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 . 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:
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.
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.
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:
|SAMP_EMPL_D_VH||D50 Sales Rep|
|SAMP_EMPL_PARENT_CHILD_MAP||D51 Sales Rep Parent Child|
|SAMP_EMPL_POSTN_D||D52 Sales Rep Position|
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"."
For indepth understanding of OBIEE click on