You guys must have read scores of documentation about the “Number of Elements at this level” in OBIEE. You must have read that each logical level of each dimension hierarchy contains this number. This setup number is used by the Oracle BI server to pick the optimal aggregate physical source for a query.
This number only affects which aggregate source is to be used and is only for performance optimization and not the correctness of data. Let me try to explain how it works.
I have a Time dimension hierarchy with the number of elements as mentioned in the image. The default for a grand total level is 1 and the best practice says that it show increase downwards. Just to show how this work’s I am just doing the other way around.
I have assigned 365 to the Year level in the hierarchy. And as shown in the image below, I have two aggregate fact tables which aggregate the revenue by month and year.
When I create the following analysis, the query generated by BI server will be surprising.
In the above query, the BI server does not use the YEAR aggregate but rather uses the detail fact SAMP_REVENUE_F. Now to just spice up things here, I will be changing the “Number of Elements at this level” below the month level. So, now week has the value 52 (from 4) and days to 366 (some random value greater than that of month).
After changing the values and running the report again, we see the following query. Below, we see that now the MONTH aggregate table is being sued because the “Number of elements” at month level is less than that of the detail.
Here comes the magic. Once the changes mentioned above are done and the analysis is re-run, the query fired uses the YEAR aggregate table.
For more information about this topic, you can read the following:
My Tech Accounts 