Introduction

This article explains how to work with logical dimension objects using parent-child hierarchies and creating calculated measures.

This chapter illustrates the following topics:

Create logical dimensions with parent-child hierarchies

Create calculated measures 

1. Create Presentation Hierarchy

To create a presentation hierarchy, drag a logical dimension hierarchy from the BMM layer to a table in the presentation layer.

1. Test Measures and Hierarchies

Run analysis to test results. Drill down to check relative recalculations.

2. Parent-Child Logical Dimension

A parent-child logical dimension is a hierarchy of members that all have the same type.

This contrasts with level-based hierarchies, in which members of the same type occur only at a single level of the hierarchy.

The real-world example of a parent-child hierarchy is an organizational reporting hierarchy chart, in which the following conditions are applied.

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

1. Parent-Child Logical Table

A parent-child hierarchy is typically based on a single logical table e.g. Employees table.

Each row in the table contains two identifying keys: one to identify the member itself and the other key to identify the parent of the member. 

2. Relationship Table

For each Oracle BI Server parent-child hierarchy defined on a relational table, you must explicitly define the inter-member relationships in a separate parent-child relationship table.

• A column that identifies the member.

• A column that defines the ancestor of the member (The ancestor may be the parent of the member or a higher-level ancestor).

• A "distance" column that specifies the number of parent-child hierarchy levels from the member to the ancestor.

• A "leaf" column that indicates if the member is a leaf member (1=Yes, 0=No).  A leaf column is a dimension member without descendants.

3. Creating a Parent Child Dimension

In Oracle BI, you generally create scripts to create and populate the parent-child relationship table through an Administration tool wizard that you can choose to use to define the parent-child hierarchy.

You can then use these scripts as often as required to reflect the current state of the data in the parent-child hierarchy. If you do not choose to use the wizard, then you must have previously created the parent-child relationship table, and you can then manually associate it with the parent-child hierarchy.

In the latter case, it is also your responsibility to populate the table with the data required to describe the inner member relationships in the parent-child hierarchy.

4. Create Logical Dimension Object

Perform either of the following steps listed below:

• Right-click the business model object and select New Object -> Logical Dimension -> Dimension with Parent-child hierarchy.

• Right-click the logical dimension table and select Create Logical Dimension -> Dimension with Parent-Child hierarchy.

To set Member Key

Click Browse next to the Member Key field to view or set the member key.

To set the Parent Column

The parent column is the column that identifies an ancestor of the member in the logical table. The ancestor may be the parent of the member or a higher-level ancestor. In the example below, the ancestor is the Manager ID column.

To set Parent-Child Relationship

If the logical table that you have selected was not from a relational table source, you can click OK to finish the process of creating the dimension. However, because the logical table in the following example is from a relational table source, you must continue the dimension definition process to set up the parent-child relationship table for the hierarchy.

You must define a parent-child relationship table for parent-child hierarchies based on relational tables. When you create the parent-child relationship table, you must choose one of the following methods.

1. Use a wizard that generates scripts to create and populate the parent-child relationship table.

2. Select a previously created parent-child relationship table.

5. Parent-Child Table Script Information

Script Location is the initial screen in Generate Parent-Child relationship Table wizard that generates SQL scripts for creating and populating parent-child relationship table.

In the Script Location screen, enter the names and locations for the DDL scripts to create and populate the parent-child relationship table.

At the end of the wizard, Oracle BI Server stores the scripts in directories selected during the wizard session. The scripts, when executed, create and populate the parent-child relationship table in the physical data source.

To Enter Parent-Child Table Details

In the parent-child Relationship Table Details screen, provide details for the table that is generated by the scripts.

To Preview Scripts

In the Preview Script screen, click View Script to view either or both of the scripts

6. To confirm Parent-Child settings

When the wizard is completed, the parent-child relationship table details are populated.

7. To confirm BMM layer changes

After the wizard is completed, the parent-child logical dimension is added to the BMM layer.

8. To confirm changes to physical layer

When the wizard is completed, the parent-child relationship is added to the physical layer.

9. To modify changes to physical layer

After adding the parent-child relationship table to the physical layer, you must make some modifications in both the physical layer and the BMM layer.

For example, you must create join relationships with the other tables related to the parent-child relationship table. Here the Dim_EMP_PARENT_CHILD alias table is created and then joined to the DIM_EMPLOYEE and Fact_DI_ORDER_AGG1 tables.

There is a one-to-many join from Dim_EMPLOYEE to Dim_EMP_PARENT_CHILD to Fact_D1_ORDER_AGG.

10. To modify changes in BMM layer

Map the logical table source (LTS) in the business model to the parent-child relationship table in the physical layer.

11. Presentation hierarchy

Add the hierarchy to the corresponding table in the Presentation layer to make the hierarchy available for queries.

Add the hierarchy to an analysis and check the results.

12. Calculated Measures

A calculated member is a user-defined dimension member whose measure values are calculated at run time.

You define a calculated member within a dimension through a formula that references other members of the same dimension. If a dimension has multiple hierarchies, all members referenced in the formula must belong to one hierarchy.

Within a calculated measure, the members do not have to be at the same level in the hierarchy.

Three standard components of a calculated member are listed below:

1. Presentation hierarchy on which the calculated member is based (in this example, “Customer-Region”).

2. Name to identify the calculated member and to distinguish it from other members in the dimension (in this example, “West-Desert-Northwest”).

3. Formula used to calculate the calculated member, which consists of one or more examples of a “member clause” connected by standard arithmetic operations. In this example, you calculate total dollars for the West region and then subtract dollars for the Desert and Northwest districts. Because there are only three districts in the west region, the remainder represents total dollar for the “California district”, “Fact-Sales”. Dollars provide the formatting for the SQL results.

Selvi

Add comment