About Level-Based Hierarchies

Each business model can have one or more dimensions, each dimension can have one or more logical levels, and each logical level has one or more attributes (columns) associated with it.

Also see Dimensional Hierarchies, Level Keys, and Content Levels.

When you create logical levels, first create a Grand Total level and then create child levels, working down to the lowest level.

The following are the parts of a dimension:

Grand Total level

The Grand Total level represents the sum of all totals for a dimension. Each dimension can have just one Grand Total level. The Grand Total level doesn't contain dimensional attributes and doesn't have a level key. You can associate measures with a Grand Total level. The aggregation level for those measures is the grand total for the dimension. The Grand Total level can exist without any columns.

Level

Levels must have at least one column. You don't need to explicitly associate all of the columns from a table with logical levels. Any column that you don't associate with a logical level is automatically associated with the lowest level in the dimension that corresponds to that dimension table. You must associate all logical columns in the same dimension table with the same dimension.

A dimension can have an unlimited number of levels.

Hierarchy

Each dimension contains one or more hierarchies. All hierarchies must have a common leaf level. For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with a common leaf level of Day. In this example, Day has two named parent levels, Fiscal Year and Calendar Year that are both children of the All root level.

Unlike hierarchies in the presentation layer, in the logical layer logical hierarchies aren't defined as independent metadata objects. Logical hierarchies exist implicitly through the relationships between levels.

You can define intermediate levels in your hierarchies to avoid having very large numbers of members at one level. For example, if you're creating a Product dimension for an automotive company that tracks data on 500 different car models, you might create some finer-grained hierarchical levels such as SUVs, Subcompacts, and Midsize Sedans. You could improve query performance and make reports and diagrams easier to read and navigate. See Create Logical Levels in a Logical Dimension Table.

Level keys

Each logical level, except the Grand Total level, must have one or more attributes that compose a level key. The level key defines the unique elements in each logical level. You must associate the dimension table logical key with the lowest level of a dimension.

A logical level can have multiple level keys. When a logical level has multiple level keys, specify a key as the primary key for the level. All dimension sources that have aggregate content at a specified level need to contain the column that's the primary key of that level. Each logical level should have one level key that's displayed when a user selects the object to drill down. You can use any level key to provide user access to the level.

You must create a unique level key. To create a unique level key with month, include the year attribute as part of the key.

Ensure that your level key is unique by including higher-level attributes to prevent queries from returning unexpected results. For example, when the Oracle Analytics query engine needs to combine result sets from multiple physical queries, the results might exclude expected rows that aren't unique according to the level key definition.

Create meaningful level keys using common business keys such as Month_name='2022 July', rather than generated surrogate keys such as time_key='1023793'. The generated surrogate keys are physical artifacts that only apply to a single instance of a source table. A business key can map to any physical instance for that logical column, for example, month_name might map to a detailed table, an aggregate table from an aggregate star, or a column in a federated data source. The physical layer can use surrogate keys in the joins but Oracle recommends using business keys.

Time dimensions and chronological keys

You can identify a dimension as a time dimension. Use the guidelines in this section when setting up and using time dimensions. Also see Time Dimension Design.