1. Building Semantic Models in Oracle Analytics Server
  2. Create and Build Your Model
  3. Work with Logical Hierarchies
  4. Create and Manage Level-Based Hierarchies

Create and Manage Level-Based Hierarchies

This topic provides information to help you understand and create a level-based hierarchy and its dimensions.

Topics:

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.

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 following guidelines when setting up and using time dimensions:

About Hierarchy Structures

A logical hierarchy can have a balanced, ragged, or skip-level structure.

Balanced hierarchy

A balanced hierarchy's structure contains members that descend to the same level and where each member's parent is immediately above it.

Unbalanced or ragged hierarchy

An unbalanced or ragged hierarchy is a hierarchy where the leaves (members with no children) might not have the same depth. For example, a site can choose to have data for the current month at the day level, previous month's data at the month level, and the previous five years' data at the quarter level.

User applications can use the IS_LEAF function to determine whether to allow moving down from any particular member.

A missing member is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all missing nodes together.

Unbalanced hierarchies aren't necessarily the same as parent-child hierarchies. Parent-child hierarchies are unbalanced by nature. Unbalanced level-based hierarchies are possible.

Skip-level hierarchy

A skip-level hierarchy is a hierarchy where there are members that don't have a value for a particular ancestor level. For example, in a Country-State-City-District hierarchy, the city Washington D.C. doesn't belong to a State. In this case, you can drill down from the Country level (USA) to the City level (Washington D.C.) and below.

In a query, skipped levels aren't displayed, and don't affect computations. When sorted hierarchically, members appear under their nearest ancestors.

A missing member at a particular level is implemented in the data source with a null value for the member value. All computations treat the null value as a unique child within its parent. Level-based measures and aggregate-by calculations group all skip-level nodes together.

Example of hierarchy containing ragged and skip-level

The image shows a hierarchy with both ragged and skip-level characteristics. For example, A-Brand 4, B-LOB 3, and Type 5 are unbalanced branches, while skips are present between A-Brand 2 and Type 3, B-LOB 2 and Product 6, and others.

Description of rgd_hier.png follows
Description of the illustration rgd_hier.png

About Using Dimension Hierarchy Levels in Level-Based Hierarchies

Learn how to use dimension hierarchical levels.

Dimension hierarchical levels can be used to perform the following actions:

  • Set up aggregate navigation.

  • Configure level‐based measure calculations. See Level-Based Measure Calculations.

  • Determine what attributes are displayed when users drill down in their data requests.

Automatically Create Dimensions with Level-Based Hierarchies

You can set up a dimension automatically from a logical dimension table if a dimension for that table doesn't exist.

To create a dimension with a level-based hierarchy automatically, a semantic model examines the logical table sources and the column mappings in those sources and uses the joins between physical tables in the logical table sources to determine logical levels and level keys. As a best practice, create a dimension table with a level-based hierarchy after all the logical table sources have been defined for a dimension table.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical Layer and locate and double-click a logical dimension table that isn't associated with any dimension.
  4. In the logical table, click the Hierarchy tab.
  5. In the Hierarchy Type field, select Level-Based or Parent-Child.
  6. Click Save.

Manually Create Dimensions in Level-Based Hierarchies

You can associate each dimension with attributes (columns) from one or more logical dimension tables and level-based measures from logical fact tables.

It's a best practice to ensure that the physical hierarchy type set in the physical layer matches the dimension properties you select in the logical layer. Also, be sure that you set the Ragged and Skipped Levels dimension properties correctly so that the queries work properly.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. In the Logical layer, right-click a business model and select Create Logical Table.
  4. In Name, type a name for the logical table. Click the Type field and select Dimension.
  5. Click OK.
  6. In the new logical table's tabs click the Hierarchy tab.
  7. Click the Hierarchy Type field and either select Level-Based, or if the dimension is a time dimension, select Time.

    The Default root level field is automatically populated after you associate logical columns with a dimension level.

  8. If the hierarchy type is Level-Based, click either Ragged or Skipped Levels.
  9. Click Save.

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.

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 the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. In the Logical layer, 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.

Associate a Logical Column and Its Table with a Dimension Level

After you create all logical levels within a dimension, you associate one or more columns from the logical dimension table to each logical level except the Grand Total level.

The first time you add a column to a dimension it associates the logical table to the dimension. The drag and drop action associates the logical column with that level of the dimension. To associate the logical level with that logical column, drag a column from one logical level to another.

You must associate the logical column or columns that comprise the logical key of a dimension table with the lowest level of the dimension.

After you associate a logical column with a dimension level, the tables where these columns exist are displayed in the Tables tab of the Dimensions dialog box.

For examples, see: About Level-Based Measure Calculations and Grand Total Dimensional Hierarchy Example.

For time dimensions, ensure that all time-related logical columns in the source table are defined in the time dimension. For example, if a time-related logical table contains the columns Month Name and Month Code, you must ensure that both columns are defined at the appropriate level within the dimension.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical Layer and locate and double-click the logical table that you want to associate with a dimension level.
  4. Click the Columns tab.
  5. Click to select the logical column, and then click Detail View.
  6. Click the Level field and select a logical level, making sure not to select the Grand Total Level.
  7. Click Save.

About Level-Based Measure Calculations

A level-based measure is a column whose values are always calculated to a specific level of aggregation.

You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue. For example, a company might want to measure its revenue based on the country, region, and city.

When a query containing a presentation hierarchy includes a level-based measure column, and the query grain is higher than the level of aggregation specific to the column, the query results return null. If the request only contains ordinary columns and no hierarchical columns, the level-based measure isn't replaced with null.

You can create an AllProductRevenue measure as a level-based measure at the Grand Total level. Level-based measures allow a single query to return data at multiple levels of aggregation. Level-based measures are also useful in creating share measures, calculated by taking some measure and dividing it by a level-based measure to calculate a percentage. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

For example, to set up these calculations, you need to build a dimensional hierarchy in your semantic model that contains the Grand Total, Country, Region, and City levels. This hierarchy contains the metadata that defines a one-to-many relationship between Country and Region and a one-to-many relationship between Region and City. For each country, there are many regions, but each region is in only one country. Similarly, for each region, there are many cities, but each city is in only one region.

After building a dimensional hierarchy, you need to create three logical columns one each for CountryRevenue, RegionRevenue, and CityRevenue. The columns use the Revenue logical column as its source. The Revenue column has a default aggregation rule of SUM and has sources in the underlying databases.

Assign the CountryRevenue, RegionRevenue, and CityRevenue columns to the Country, Region, and City levels, respectively. Each query that requests one of these columns returns the revenue aggregated to its associated level.

Grand Total Dimensional Hierarchy Example

Use this example to learn how to use a grand total dimensional hierarchy with revenue.

If your product dimensional hierarchy contains TotalProducts (Grand Total level), Brands, and Products levels, and a Revenue column defined with a default aggregation rule of Sum, you can then create an AllProductRevenue logical column. The AllProductRevenue column uses Revenue as its source. Associate the AllProductRevenue column to the Grand Total level. Each query that includes the AllProductRevenue column returns the total revenue for all products. The value is returned regardless of any constraints on Brands or Products.

If you have constraints on columns in other tables, the grand total is limited to the scope of the query. For example, if the scope of the query asks for data from 2000 and 2021, the grand total product revenue is for all products sold in 2000 and 2021.

If you have three products, A, B, and C with total revenues of 100, 200, and 300 respectively, then the grand total product revenue is 600, the sum of each product's revenue. If you have set up a semantic model as described in this example, the following query produces the results listed:

SELECT product, productrevenue, allproductrevenue
FROM sales_subject_area
WHERE product IN 'A' or 'B'

The results are as follows:

PRODUCT;;PRODUCTREVENUE;;ALLPRODUCTREVENUE
A;;;;;;;;100;;;;;;;;;;;;;600
B;;;;;;;;200;;;;;;;;;;;;;600

The AllProductRevenue column always returns a value of 600, regardless of the products on which the query constrains.

Identify the Primary Key for a Dimension Level

Use a logical dimension table's Hierarchy tab and Primary Key field to identify the column to use as the dimension level's primary key.

You can't use a derived logical column that's the result of a LOOKUP function as part of a primary logical level key. This limitation exists because the LOOKUP operation is applied after aggregates are computed, but level key columns must be available before the aggregates are computed because they define the granularity at which the aggregates are calculated.

You can use a derived logical column that's the result of a LOOKUP function as a secondary logical level key.

If the level is in a time dimension, you can select chronological keys and sort the keys by name.

To help manage primary keys, you can go to the logical table's Columns tab, locate the column used as a primary key, and add information to its Description field.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. In the Logical layer, right-click on a logical dimension table, then click Edit.
  4. Click Logical Layer and locate and double-click the logical table with the dimension level that you want to add a primary key to.
  5. Click the Hierarchy tab.
  6. Click to select a level below the Grand Total level.
  7. Click the Primary Key field and select a level key from the list and save changes. If only one key exists, it is the primary key by default.
  8. Click Save.

Select and Sort Chronological Keys in a Time Dimension

At least one level of a time dimension must have a chronological key. You can select one or more chronological keys for any level and then sort keys in the level, but Oracle Analytics uses only the first chronological key.

Pay attention to the column order in a chronological key with many columns. You set the column order using a SQL ORDER BY clause on the columns to reflect the real-world chronological order in the Chronological Key field. Since the range for quarters is 1 to 4 with 4 quarters in a year, using an ORDER BY clause with the Quarter before the Year (Quarter, Year) is incorrect. The incorrect order shows all first quarters across all years, before displaying any second quarters, and so on. To correct the results, use (Year, Quarter) in the ORDER BY clause.

For information about creating a time dimension, see Manually Create Dimensions in Level-Based Hierarchies.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. In the Logical layer, double-click a logical table, and in the logical table's tabs click Hierarchies.
  4. Click a logical level below the Grand Total level.
  5. Click the Chronological Key field and select a chronological key.
  6. Click Save.

Add a Dimension Level to the Preferred Drill Path

You can use the Preferred Drill Path field to identify the drill path to use when users drill down in their data requests.

You should use a preferred drill path only to specify a drill path that's outside the normal drill path defined by the dimensional level hierarchy. A drill path is most commonly used to drill from one dimension to another. You can delete a logical level from a drill path or reorder a logical level in the drill path.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical Layer and locate and double-click the logical table that you want to add a drill path to.
  4. In the logical table, click the Hierarchy tab.
  5. Click a logical level, go to the Preferred Drill Path field, and click Add Table.
  6. In Select Logical Level, search for and select a logical level, and then click Select.
    You can select logical levels from the current dimension or from other dimensions.
  7. Click Save.