Dimension Hierarchy 11.1.1.7.0

OBIEE 11G - Dimension Hierarchy (11.1.1.7.0)

When you’re building a hierarchy in the Administration Tool, follow these rules.

• 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.

Dimension hierarchical levels are used to perform the set up aggregate navigation, configure level-based measure calculations and drill down from one parent to a child level.

Creation of Time Dimensional hierarchy.

There are two methods of creating dimensional hierarchies.

1. Automatic

2. Manual

Automatic Method:

Right click on the dimension and click on Create Dimension. This will create two levels total and detail level by default. You can change it to any way you want.

 Once you select automatic method this will create two levels total and detail level by default as like below.

Manual Method:

We will create Time dimension

•  On offline & online mode, Right click on the business model and select, New Object – Dimension

Please select structure (Time).

Note: Year you can select ragged and skipped as well depends of on your requirement.

• Right click on the new dimension and select New Object – Logical Level, type as “Total” for the Name and check the option, Grand Total Level and save it.

•  Right click on the “Time Total” level, and select the option, New Object – Child Level, name as Year and save it and to the same for Half/Qtr/Month/Week/Day  .

•  Right click on the “Week” level, and select the option, New Object – Child Level, name as Time Detail and save it.

•  Drag year from logical table and drop it on Year level, drag Half from logical level and drop it on Qtr level and drop it on Week level on from logical table and drop it on Time detail level.

•  In the time hierarchy you have to define chronological key into lowest level means (details level nothing but day level here).

• Right click on the year logical column properties, on the Key tab, Type year column name then Primary key column will be appear then select the primary key column  then left side pencil symbol will be enable like below.

•  Select on Year/Half/Qtr/Month/Week/Day detail levels, select keys and add the same keys (check the option use for display option) for drill down purpose.

• You have to drag the hierarchy in presentation layer.

• You can check the consistency check then save it.

While you were saving the rpd you will get the below Waring.

 level has no defined key to uniquely identify members

Solution:

Presentation layer --> double click each level --> select display columns --> here you have to click ( + symbol) then select appropriate columns like bellow.

•  Save and check on answers.

Create report and check like below.

Good Luck !!!!

Thanks,

Satya Ranki Reddy