Creating a Repository Using the Oracle Business Intelligence Administration Tool ( part 8 )

Lab 6: Creating Logical Dimensions with Parent-Child Hierarchies

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 BISAMPLE 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_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"."MEMBER_KEY" = "orcl".""."BISAMPLE"."F1 Revenue"."EMPL_KEY"

Create Logical Table and Logical Columns

1 .	In the BMM layer, right-click the Sample Sales business model and select New Object > Logical Table to open the Logical Table dialog box.
2 .	On the General tab, name the logical table D5 Sales Rep.
3 .	Click OK to add the logical table to the business model. Notice that the D5 Sales Rep icon has a # sign. This is because you have not yet defined the logical join relationship. When you define the logical join later in this tutorial, the icon will change accordingly.
4 .	Drag all six columns from D50 Sales Rep in the Physical layer to D5 Sales Rep in the BMM layer. This action creates logical columns and adds a D50 Sales Rep logical table source to D5 Sales Rep.
5 .	Rename the D50 Sales Rep logical table source to LTS1 Sales Rep.
6 .	In the Physical layer, expand D52 Sales Rep Position.
7 .	Drag POSTN_DESC and POSTN_LEVEL from D52 Sales Rep Position to LTS1 Sales Rep. Note that you are dragging the columns to the logical table source, not the logical table. Dragging to the logical table would create a second logical table source.
8 .	Drag DISTANCE from D51 Sales Rep Parent Child to LTS1 Sales Rep. Again, you drag the column to the logical table source, not the logical table.
9 .	Rename the logical columns: Old Name New Name POSTN_KEY Position Key TYPE Sales Rep Type EMPL_NAME Sales Rep Name EMPLOYEE_KEY Sales Rep Number HIRE_DT Hire Date MGR_ID Manager Number POSTN_DESC Position POSTN_LEVEL Position Level DISTANCE Closure Distance

Create a Logical Join

1 .	In the BMM layer, select D5 Sales Rep and F1 Revenue.
2 .	Right-click either highlighted table and select Business Model Diagram > Selected Tables Only to open the Business Model Diagram.
3 .	Create a logical join between D5 Sales Rep and F1 Revenue.
4 .	Close the Business Model Diagram. Notice that the icon has changed for the D5 Sales Rep table.

Create a Parent-Child Logical Dimension

1 .	Right-click the D5 Sales Rep logical table and select Create Logical Dimension > Dimension with Parent-Child Hierarchy.
2 .	In the Logical Dimension dialog box, on the General tab, name the logical dimension H5 Sales Rep.
3 .	Click Browse next to Member Key. The Browse window shows the physical table and its corresponding key.
4 .	Click View to open the Logical Key dialog box. Confirm that the Sales Rep Number column is selected
5 .	Click Cancel to close the Logical Key dialog box.
6 .	Click OK to close the Browse window.
7 .	Click Browse next to Parent Column. The Browse window shows the columns other than the member key.
8 .	Select Manager Number as the parent column for the parent-child hierarchy.
9 .	Click OK to close the Browse window, but do not close the Logical Dimension dialog box.

Define Parent-Child Settings

1 .	Click Parent-Child Settings to display the Parent-Child Relationship Table Settings dialog box. Note that at this point the Parent-Child Relationship Table is not defined. For each parent-child hierarchy defined on a relational table, you must explicitly define the inter-member relationships in a separate parent-child relationship table. In the process of creating the parent-child relationship table, you may choose one of the following options: 1. Select a previously-created parent-child relationship table. 2. Use a wizard that will generate scripts to create and populate the parent-child relationship table. In the next set of steps you select a previously created and populated parent-child relationship table. For your information only: To start the wizard you would click the Create Parent-Child Relationship Table button. The wizard creates the appropriate repository metadata objects and generates SQL scripts for creating and populating the parent-child relationship table. At the end of the wizard, Oracle BI Server stores the scripts into directories chosen during the wizard session. The scripts can then be run against the database to create and populate the parent-child relationship table. Running the wizard is not necessary in this tutorial because the parent-child relationship table is already created and populated.
2 .	Click the Select Parent-Child Relationship Table button to open the Select Physical Table dialog box.
3 .	In the Select Physical Table dialog box, select the D51 Sales Rep Parent Child alias you created.
4 .	The D51 Sales Rep Parent Child alias is now displayed in the Parent-Child Relationship Table column.
5 .	In the Parent-Child Table Relationship Column Details section, set the appropriate columns: Member Key MEMBER_KEY Parent Key ANCESTOR_KEY Relationship Distance DISTANCE Leaf Node Identifier IS_LEAF Explanation: Member Key identifies the member. Parent Key identifies an ancestor of the member, The ancestor may be the parent of the member, or a higher-level ancestor. Relationship Distance specifies the number of parent-child hierarchical levels from the member to the ancestor. Leaf Node Identifier indicates if the member is a leaf member (1=Yes, 0=No).
6 .	Click OK to close the Parent-Child Relationship Table Settings dialog box.
7 .	Click OK to close the Logical Dimension dialog box.
8 .	Right-click H5 Sales Rep and select Expand All. Note that a parent-child logical dimension has only two levels.
9 .	Delete all columns from the Detail level except for Sales Rep Name and Sales Rep Number.
10 .	Double-click the Detail level to open the Logical Level dialog box.
11 .	On the Keys tab, create a new key named Display Key that maps to the Sales Rep Name column.
12 .	Deselect Use for Display for the Sales Rep Number column and select Use for Display for the Sales Rep Name column.
13 .	Make sure that Member Key is still set to D50 Sales Rep_Key.
14 .	Click OK to close the Logical Level dialog box.
15 .	Expand F1 Revenue > Sources and double-click LTS1 Revenue to open the Logical Table Source dialog box.
16 .	On the Content tab, set the logical level to Detail for the H5 Sales Rep logical dimension.
17 .	Click OK to close the Logical Table Source dialog box.

Create Presentation Layer Objects

1 .	Drag the D5 Sales Rep logical table from the BMM layer to the Sample Sales subject area in the Presentation layer.
2 .	Rename the D5 Sales Rep presentation table to Sales Reps.
3 .	Move the Sales Reps presentation table above the Base Facts table.
4 .	Expand the Sales Reps presentation table and notice that the H5 Sales Rep parent-child logical dimension is automatically included as a presentation hierarchy.
5 .	Double-click the H5 Sales Rep presentation hierarchy to open the Presentation Hierarchy dialog box.
6 .	On the Display Columns tab, confirm that Sales Rep Name is set as the display column.
7 .	Click OK to close the Presentation Hierarchy dialog box.
8 .	Save the repository and check consistency. Fix any errors or warnings before proceeding.
9 .	Close the repository. Leave the Administration Tool open.

Test Your Work

1 .	Return to Fusion Middleware Control Enterprise Manager and load the BISAMPLE repository. If you need help, click here to review steps from earlier in this tutorial.
2 .	Return to Oracle BI, which should still be open, and sign in.
3 .	Create the following analysis to test the parent-child logical dimension. Sales Reps.H5 Sales Reps Sales Reps.Position Base Facts.Revenue
4 .	Click Results.
5 .	Expand the pivot table to view data at different levels of the hierarchy. Notice that the Revenue measure rolls up through each level.
6 .	Sign out of Oracle BI.