Each logical level of each dimensional hierarchy contains a setup named “Number of Elements at this Level”. This setup number is used by the Oracle BI Server when defining strategy for picking optimal aggregate physical source for a query. The numbers in the setup will help determine which physical table is most appropriate for a better performance query return (another critical element in that decision being also the dimensional granularity of each table).
This number does not have to be exact. The ratio from one level to the next is more important than the absolute number.
These numbers only affect which aggregate source is used (optimization, not correctness of queries).
These values should be changed to ratio values based on Actual data in the implementation. To prevent problems with aggregate navigation, make sure these numbers illustrate reality, mainly in the way the compare to each other.
Fact sources will be selected on a combination of:
- the fields selected as well as
- the levels in the dimensions to which they link.
For example, when aggregate navigation is used, multiple fact sources exist at different grains. The Oracle BI Server multiplies the number of elements at each level for each qualified source as a way to estimate the total number of rows for that source.
Then, the Oracle BI Server compares the result for each source and selects the source with the lowest number of total elements to answer the query. The source with the lowest number of total elements is assumed to be the fastest.
By adjusting these values, you can alter the fact source selected by Oracle Business Intelligence in some cases.
For the Grand Total level, leave this field blank. The system will set to a value of 1 by default.
Steps to populate them automatically
These Level Counts can be automatically populated for one or more dimension hierarchies using Estimate Levels. To populated logical level counts automatically:
- In the Administration Tool, open a repository in online mode.
- Right-click one or more business models and dimension objects, and choose Estimate Levels.
- In the Check Out Objects dialog box, click Yes to check out the objects that appear in the list.
Documentation / Reference
- [http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10540/dimensions.htm#i1005741|Creating Logical Levels in a Dimension]]