Dimensional Hierarchy - Preferred Drilldown – Number of elements at this level
A hierarchy is a set of parent child relationships between attributes within a dimension.
Dimension hierarchical levels are used to perform the set up aggregate navigation, configure level-based measure calculations and drill down from one parent to a child level.
Creation of Dimensional hierarchy.
There are two methods of creating dimensional hierarchies.
1. Automatic
2. Manual
Automatic Method:
Right click on the dimension and click on Create Dimension. This will create two levels total and detail level by default. You can change it to any way you want.
Manual Method: We will create Time dimension
1. On offline & online mode, Right click on the business model and select, New Object – Dimension.
2. Right click on the new dimension and select New Object – Logical Level, type as “Time total” for the Name and check the option, Grand Total Level and save it.
3. Right click on the “Time Total” level, and select the option, New Object – Child Level, name as Year and save it and to the same for Month.
4. Right click on the “Month” level, and select the option, New Object – Child Level, name as Time Detail and save it.
5. Drag year from logical table and drop it on Year level, drag month from logical level and drop it on month level and drag date from logical table and drop it on Time detail level.
6. Drag and drop the primary key of the time logical table to the time detail level.
7. Right click on the year logical column properties, on the levels tab, select year and click ok, do the same for month and time detail levels.
8. Select on year, month, time detail levels, select keys and add the same keys (check the option use for drilldown option).
9. Save and check on answers.
For time dimensional hierarchy, it is necessary to check the option “Time Dimension” on the general tab to support for time series calculation.
Preferred Drilldown
You will see a tab in the logical level called Preferred Drilldown
This option is used to force BI to move to any level specified in preferred drill tab. It is most commonly used to drill from one dimension to another.
Number of elements at this level
This number is used by the Oracle BI Server when picking aggregate sources. The number does not have to be exact, but ratios of numbers from one logical level to another should be accurate.
These Level Counts can be automatically known for one or more dimensional hierarchies using Estimate Levels.
Open the RPD in online mode, right click on any dimensional hierarchy or any BMM model, select estimate level, click yes for “Do you want to check them out?”. It will automatically update the level counts in the dimensional hierarchy.
Posts
