OBIEE 10G/11G - Level-based Hierarchy

Structure

• A hierarchy should only have one grand total level.
• All the columns in a hierarchy should come from one logical table.
• All hierarchies must have a common leaf level and a common root (all) level. If a hierarchy has multiple branches, all branches must have a common beginning point and a common end point.

Total US
            Region
                        District
                                    Customer
            State
                        County
                                    Customer
     

Level keys

• Each logical level should have one level key that is displayed when an Oracle BI Presentation Services user clicks to drill down. This may or may not be the primary key of the level. To set the level key to display, select the Use for display option in the Level Key dialog.

• The level key of the root level (the lowest level of the hierarchy) should match the key of the logical dimension table upon which the hierarchy is built.
• Level keys should be meaningful business keys (like Month_name='2010 July') rather than generated surrogate keys (like time_key='1023793'), because surrogate keys are physical artifacts that only apply to a single instance of a source table. The business name (Month_name='2010 July'), in contrast, 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,
  • and a column in a federated spreadsheet.

Note that the Physical layer still uses the surrogate keys in the joins, so there is no performance or flexibility penalty for using business keys in the business model.

Level

• Not all columns in the logical table need be associated with a level. If not explicitly associated with a level, a column is associated with the lowest level automatically.
• No column can be associated with more than one level.
• If a column pertains to more than one level, associate it with the highest level it belongs to.
• No level except the grand total level can exist without at least one column being associated with it.
• All levels except the grand total level must have level keys.
• The columns used to make up a level key should reference a unique value. If the level value is not unique, columns need to be added to the key to guarantee uniqueness.

Create it automatically ?

For Siebel Analytics versions 7.7 and above, it is possible to create the dimension hierarchy automatically:

• Right-click on the dimension and select the option Create Dimension.

Know the table associated with the hierarchy

Define Multiple Hierarchies for the Same Dimension Logical Table

Sometimes there may be a need to apply different hierarchies on the same logical table.

As a result, create a single dimension that includes both hierarchies to achieve this requirement.

The following steps to achieve this assume that there is at least one level in common:

1. Select the highest common level. Note that it is possible to use the grand total level as the common level.
2. Right-click and choose New Object > Child Level to create a new branch off of the existing hierarchy.
3. Fill out the remainder of the hierarchy in the same way as for a new hierarchy.
4. When you want to specify the bottom of this new hierarchy branch, use the New Object → Shared Level As Child menu option.

Below is an example. The time dimension contains two different hierarchies, one for the year and another one for the fiscal year. Year and Fiscal Year levels are both children of the “Total” grand total level.

If this guideline is not used and several dimensions are defined on the same logical table, the following error will be returned in the report:

[nQSError:  14031] The content filter of a source for logical table:  <Logical Table Name> references multiple dimensions.

Work with a Snowflake Schema

When using a snowflake schema, several dimension logical tables are joined together.

In this example, Product Line > Products is a branch of the snowflake.

One dimension hierarchy should be created for the branch. The minimal levels of this hierarchy should be:

1. Grand Total Level
2. Detail Level of the dimension that is not joined to the fact table. In this case, it is ProductLine.
3. Detail Level of the dimension that is joined to the fact table.

So in this example, the following hierarchy would be created:

A source does not join to any fact source

OBI Server recommends that the key of the lowest level of a dimension hierarchy must match the primary key of its corresponding dimension logical tables.

If this is not done, the following error may be returned in the report:

[39008] Logical dimension table <Dimension Table Name> has a source <Source Table Name> 
that does not join to any fact source.