Create Logical Levels in a Logical Dimension Table

When you create logical levels in a logical dimension table, you also create the hierarchy by identifying the type of level and defining child levels.

If you're defining the level as a Grand Total level, the default value is 1.

The number doesn't have to be exact, but ratios of numbers from one logical level to another should be accurate. You can retrieve the row count for the level key and use that number as the number of elements. For multidimensional sources, you can use the number of members at that level.

The Oracle Analytics query engine uses this number when selecting which aggregate source to use. For example, when aggregate navigation is used, multiple fact sources exist at different grains. The Oracle Analytics query engine 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. The Oracle Analytics query engine then 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.

  1. On your home page, click Navigator Navigator icon and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. In the Logical layer Logical layer icon, double-click a logical table, and in the logical table's tabs click Hierarchies.
  4. Click the Hierarchy Type field and either select Level-Based, or if the dimension is a time dimension, select Time.
  5. Click New Level.
  6. Add and configure the Grand Total level and Detail level.
  7. Optional: Rename the Grand Total level and Detail level. For example, Products Total or Products Detail.
  8. To add and define child logical levels, select the hierarchy and click New Level.
  9. In the Elements at this level field, specify the number of elements that exist at this logical level.
  10. If measure values at a particular level fully constitute aggregated measures at its parent level, select Supports rollup to higher level.
  11. For all levels except Total, select the Primary Key.
  12. For all levels except Total, select the Display Key
  13. Click Save.