• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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

Summary
Review Date
Reviewed Item
Creating Logical Dimensions with Parent-Child Hierarchies in OBIEE
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Creating Logical Dimensions with Parent-Child Hierarchies in OBIEE"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.