I have a fact table with 11 million rows that joins with 8 dimesion tables to form a perfect star schema.
The client said that he can reduce the row size to 6 million if we remove 3 dimensions from the joins. So he created a materialized view with 6 million rows joining with 5 dimesnions which he is going to use the most in the reports.
The client requires the previous model as well because there are 2 reports out of the 7 reports that will be based on the 11 million rows fact and the 8 dimension tables.
if I convert this scenario to the aggregate and detail level Fact. I will have 2 models (5 Dimesnion model lets say it as aggregate and 8 dimesnion model as detail). Now i will create a BMM layer subject area and promote the detailed level model to the BMM layer. For Aggregate I will start mapping each of the objects in BMM layer except for thr 3 dimension tables that are nor present in AGG model with the Agg model (5 dimesnions) of the physical layer.
While Aggregate navigation and creating dimension hierarchies we set the content level navigation for each logical table source to the level at which the lowest granularity exists. However in my scenario. The granularity for both the model will be same. So how will the query from OBIEE recognize which model to use when a report is built as both the models are built at the same content level with the exception of 3 dimsion tables.
Please help it is urgent. How should I implement this scenario?
For your "fact" table in the BMM, you should have two LTS - one for the detail level data, one for the aggregate level data.
For each of your dimension tables, you should set up a hierarchy (sorry, using standard accepted terms of objects here...not Oracle's non-standard ones....) Each dimension should have at least a "total" and "detail" level, more if there are a proper hierarchy.
Then when you set up your LTS, for the detail one - each of your eight dimensions will have content level set to the detail levels. For your aggregate LTS, on the three dimensions that aren't present, set the content level to the "total" level. I'd also set the PRIORITY GROUP so that the aggregate gets chosen first instead of the detail.