Creating Logical Dimensions with Parent-Child Hierarchies in OBIEE

Blog Author


Published Date

10th October, 2020




  • Share this blog :

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:

Table Alias
SAMP_EMPL_D_VH D50 Sales Rep
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"."


About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .

Related Blogs

Write For Us