Creating Logical Dimensions with Parent-Child Hierarchies in OBIEE

Creating Logical Dimensions 

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

Desired to gain proficiency on OBIEE Training? Explore the blog post on OBIEE  training to become a pro in "OBIEE Training Online"

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 . Right-click Connection Pool and select Import Metadata to open the Import Wizard.

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

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

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

6 . Click Finish to close the Import Wizard. 7 . Confirm that the three tables are visible in the Physical layer of the repository.

8 . Right-click SAMP_EMPL_PARENT_CHILD_MAP and select View Data.

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:

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