Dimensional Hierarchies, Level Keys and Content Levels

Level keys are used to define the levels in a dimensional hierarchy. In turn, these levels are used to set content levels or the level of aggregation of a logical table source. Oracle Analytics uses content levels to navigate to the most optimized logical table source for a given query.

Dimensional hierarchies are also required to create level-based measures and to set up drilling for analyses.

Dimensional Hierarchies

Always create dimensional hierarchies, even when there’s only one level. We recommend that you do this for many reasons:

Oracle Analytics uses dimensional hierarchies to select the most optimized logical table source by way of content levels.
Dimensional hierarchies are required to drill up and down between levels. Sometimes drilling is intuitive. For example, if you’re analyzing a brand, you’ll probably want to drill down to its corresponding Universal Product Codes (UPC). Other types of drill-downs might not be obvious but still useful. For example, you might want to drill down from a contact type to a contact name.
Dimensional hierarchies are useful when Oracle Analytics joins two result sets. For example, if you combine two fact tables in the same report.
Dimensional hierarchies are required to create level-based measures.
Time dimensions are required in some time series calculations. For example, where the calculation is based on a specific level, such as the year.
When you define dimensional hierarchies and content levels for a logical table source, it improves the capabilities of the consistency checker to identify issues with the semantic model.

Types of Dimensional Hierarchies

A balanced level-based hierarchy is the most common type of hierarchy used in Oracle Analytics. In all level-based hierarchies, the detail levels roll up into higher levels. In a balanced level-based hierarchy, all members of the hierarchy have ancestors at all levels as shown here.

Description of the illustration ceal_hierarchies_1.jpg
A time dimension is a special level-based hierarchy that is used specifically for time-based hierarchies. A time dimension is required if you want to use time series calculations such as AGO and TODATE. To define a time dimension, you select Time in the properties of the dimensional hierarchy.

Description of the illustration ceal_hierarchies_2.jpg
A skipped-level hierarchy is a special level-based hierarchy where not all members of the hierarchy have ancestors at all levels. To define a skipped-level hierarchy, you select Skipped Levels in the properties of the hierarchy.

This example shows a skipped-level hierarchy where Washington DC doesn’t belong to a state, so the state/province level is skipped.

Description of the illustration ceal_hierarchies_3.jpg
A ragged or unbalanced hierarchy is another special level-based hierarchy where not all the data is present at all levels of the hierarchy. To define a ragged hierarchy, you select Ragged in the properties of the hierarchy.

This example shows a ragged and skipped-level hierarchy where the Distributor and Store levels are missing from the Web branch of the hierarchy.

Description of the illustration ceal_hierarchies_4.jpg
Here, you select both Ragged and Skipped Levels in the properties of the hierarchy.

Description of the illustration ceal_hierarchies_5.jpg
A parent-child hierarchy is a type of hierarchy often associated with an organization. For example, employees rolling up to a manager. In a parent-child hierarchy, each child member rolls up to a single parent member. At the lowest level, each member has no child members. At the highest level, there is a single parent with no further parent levels. In between, each member is both a parent and a child.

Parent-child hierarchies are based on a specialized parent-child relationship table made up of four columns:
- Member
- Ancestor or ancestors of a member
- Number of levels between the member and the ancestor
- If the member is a leaf member, that is, at the lowest level
To define a parent-child hierarchy, you select Dimension with Parent-Child Hierarchy when you create a new hierarchy, and then select Parent-Child Settings to set up the parent-child hierarchy as shown here.

Description of the illustration ceal_hierarchies_6.jpg

Description of the illustration ceal_hierarchies_8.jpg

Rules When You Create a Dimensional Hierarchy

A dimensional hierarchy can contain only one grand total.
If a dimensional hierarchy has multiple branches, all branches typically have a common beginning point and a common end point.
To define the grand total-level, you must select Grand total level in the properties for the level.

This example shows Grand total level selected and Number of elements at this level set to 1.

Description of the illustration ceal_hierarchies_9.jpg
To define a non grand total levels, you must select Supports rollup to higher level of aggregation in the properties for the level. After you define a grand total level, Oracle Analytics automatically sets Supports rollup to higher level of aggregation for all other levels in the hierarchy.

For all non grand total levels, Number of elements at this level is always higher than 1.

Description of the illustration ceal_hierarchies_10.jpg
This example shows a dimensional hierarchy with multiple branches starting and ending at a common point. The shared starting point is the grand total level and the shared end point is the detail level.

Description of the illustration ceal_hierarchies_11.jpg
When you define a dimensional hierarchy, always specify the number of elements per level. Oracle Analytics uses the number of elements to identify aggregate tables and mini dimensions. This number doesn’t need to be exact, a rough estimate is sufficient. In navigation, a cross product is calculated across all the content levels in a logical table source. This is used as a tie-breaker when navigating between otherwise equal sources.
The number of elements per level is hierarchical with the lowest number of elements at the top (1 for a grand total). Higher levels in a dimensional hierarchy should have fewer elements than lower levels. Note that the consistency checker warns you if a parent level has a greater number of elements than a child level.

This example shows a logical level Month with 120 elements defined.

Description of the illustration ceal_hierarchies_12.jpg
After you create a dimensional hierarchy for a logical table, all columns in that table are part of the hierarchy. By default, a column not explicitly associated with a higher level is considered to be part of the lowest or detail level.

Level Keys

You use level keys to identify a given level.

The primary key of each level must be unique.
If a single column used as a primary key of a level is not unique, you must combine it with additional columns to form a unique, composite level key. For example, consider the case where month number or month name is used for a level key. The month number for October is 10 but both October and 10 are not unique, as every year has a month number 10 and a month named October. To form a unique level key, you must combine month number or month name with year to form a composite level key. In this example, the composite level key is month number 10 and year 2021 or month name October and year 2021.
The primary key of the lowest or detail level of a dimensional hierarchy must match the primary key of the logical dimension table that the hierarchy is based on.

This example shows a logical dimension table and dimensional hierarchy with common primary keys.

Description of the illustration ceal_hierarchies_13.jpg
Grand total levels don’t have a level key associated with them.
If a column is a primary level key or part of a primary level key, you must assign that column to that level. If a column is a level key in a parent level and part of a composite level key in a child level, you assign that column to the parent level. Using the earlier example, month number and month name should be assigned to the month level and year to the year level.
Display keys are a level key with Use for Display selected. A display key is the column that is shown in an analysis when you drill down on an object.
If a dimensional hierarchy is for a time dimension, at least one of the levels must have a chronological key which specifies the sort order of the periods from the oldest to the newest. Often the primary key of the detail level is also the chronological key.

This example shows a time dimension level key with the same primary key and chronological key.

Description of the illustration ceal_hierarchies_14.jpg

Content Levels

You use content levels to define the level of aggregation of a logical table source in both facts and dimensions.

Content levels allow Oracle Analytics to select the most optimized logical table source for a query.
Content levels help the consistency checker find issues with your semantic model configuration and this can prevent runtime errors.
If you specify content levels for a fact logical table source, you must specify content levels for all the dimensions that join to that logical fact table. If you join a dimension table with no content level set to a fact table but content levels exist for other dimensions, Oracle Analytics doesn’t perform a join between that dimension table and the fact table. In this case, Oracle Analytics assumes that the dimension table doesn’t join to the fact table. Queries involving dimensions with no content levels specified but which do join to a fact source return the error Unable to navigate requested expression.

This example shows a fact logical table source showing level of aggregation or content levels for a fact table. Note that this fact logical table source joins to only 4 of 12 attribute tables.

Description of the illustration ceal_hierarchies_14.jpg