Level-based hierarchy is the first type of hierarchy in OBIEE.
Dimension hierarchy levels allow :
Special type of level-based dimension are supported:
When building a hierarchy in the Administration Tool, follow these rules.
Total US
Region
District
Customer
State
County
Customer
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.
For Siebel Analytics versions 7.7 and above, it is possible to create the dimension hierarchy automatically:
I would have preferred “Create Hierarchy”. On this website, the dimension is the logical table and the hierarchy is the definition of the levels
This option appears only if the table is recognized as a logical dimension, otherwise the table is identified as a fact_table.
Sometimes there may be a need to apply different hierarchies on the same logical table.
A logical dimension table, however, can be associated with one and only one hierarchy.
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:
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.
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:
So in this example, the following hierarchy would be created:
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.