Creating Logical Dimensions with Parent-Child Hierarchies in OBIEE

A parent-child hierarchy is a hierarchy of members that all have the same type. This contrasts with level-based hierarchies, where members of the same type occur only at a single level of the hierarchy. The most common real-life occurrence of a parent-child hierarchy is an organizational reporting hierarchy chart, where the following all apply:

• Each individual in the organization is an employee.

• Each employee, apart from the top-level managers, reports to a single manager.

• The reporting hierarchy has many levels.

In relational tables, the relationships between different members in a parent-child hierarchy are implicitly defined by the identifier key values in the associated base table. However, for each Oracle BI Server parent-child hierarchy defined on a relational table, you must also explicitly define the inter-member relationships in a separate parent-child relationship table.

To create a logical dimension with a parent-child hierarchy, perform the following steps:

• Open the Repository in Offline Mode

• Import Metadata and Define Physical Layer Objects

• Create Logical Table and Logical Columns

• Create a Logical Join

• Create a Parent-Child Logical Dimension

• Define Parent-Child Settings

• Create Presentation Layer Objects

• Test Your Work

Open the Repository in Offline Mode

1 . Return to the Administration Tool, which should still be open. If not, select Start > Programs > Oracle Business Intelligence > BI Administration.

2 . Select File --> Open --> Offline.

3 . Select BISAMPLE.rpd and click Open. Do not select any BISAMPLE repository with an extension, for example, BISAMPLE_BI0001.rpd. Recall that these are the repositories that have been loaded into Oracle BI Server memory.

4 . Enter BISAMPLE1 as the repository password and click OK to open the repository.

Import Metadata and Define Physical Layer Objects

1 . In the Physical layer, expand orcl.

2

2 . Right-click Connection Pool and select Import Metadata to open the Import Wizard.

2

3 . In the Select Metadata Types screen, accept the defaults and click Next.

2

4 . In the Select Metadata Objects screen, in the data source view, expand BISAMPLE and select the following tables for import:

SAMP_EMPL_D_VH

SAMP_EMPL_PARENT_CHILD_MAP

SAMP_EMPL_POSTN_D

2

5 . Click the Import Selected button to move the tables to the Repository View.

2

6 . Click Finish to close the Import Wizard.

7 . Confirm that the three tables are visible in the Physical layer of the repository.

2

Desired to gain proficiency on OBIEE Training? 

Explore the blog post on OBIEE  training to become a pro in OBIEE Training Online.

8 . Right-click SAMP_EMPL_PARENT_CHILD_MAP and select View Data.

2

This is an example of a parent-child relationship table with rows that define the inter-member relationships of an employee hierarchy. It includes a Member Key column, which identifies the member (employee); an Ancestor Key, which identifies the ancestor (manager) of the member; a Distance column, which specifies the number of parent-child hierarchy levels from the member to the ancestor; and a Leaf column, which indicates if the member is a leaf member.

9 . Create the following aliases for the tables:

TableAlias
SAMP_EMPL_D_VHD50 Sales Rep
SAMP_EMPL_PARENT_CHILD_MAPD51 Sales Rep Parent Child
SAMP_EMPL_POSTN_DD52 Sales Rep Position

2

10 . Use the Physical Diagram to create the following physical joins for the alias tables:

"orcl".""."BISAMPLE"."D52 Sales Rep Position"."POSTN_KEY" =

"orcl".""."BISAMPLE"."D50 Sales Rep"."POSTN_KEY"

"orcl".""."BISAMPLE"."D50 Sales Rep"."EMPLOYEE_KEY" = "orcl".""."BISAMPLE"."D51

Sales Rep Parent Child"."ANCESTOR_KEY"

"orcl".""."BISAMPLE"."D51 Sales Rep Parent Child"."

2

For indepth understanding of OBIEE click on