I am having issues accessing cube data from a user other than where the AW was built.
Following is what I have done in User-1:
1. Create two AW's in User-1 : AW-1 and AW-2.
2. Create all objects in these two AW's
3. AW-2 is dynamically attached on attaching AW-1 by using PERMIT startup programs. Please refer
2249005
4. Refreshed both AW's to load data
5. ALLCOMPILE - no errors.
6. Created some SQL views on User-1 which basically query the underlying cube views. I also have some queries using the olap_table option.
7. All my querries work fine in User-1. I can access data from AW-1 and AW-2.
However, we give access to the above SQL views through an abstract user: User-abs. In 10g, all we did was grant select privs to all the User-1.AW$ tables and also on all the sql views. This would let User-abs get data from the cubes in User-1.
I am not able to get User-abs to do the same in 11g. We encounter an error mentioning that the workspace object <cubename>_STORED does not exist. The cubename it is referring to exists in AW-2.
I granted select privs on all AW$ tables, dimension views, cube views and the abstracted sql views but the error persists.
I tried searching this forum but could not find any helpful leads. Any help will be greatly appreciated.
Thanks!
Database - 11.2.0.1
AWM - 11.2.0.2.0A