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 . Deselect Show Qualified Names and 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 intermember 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:
|Leaf Node Identifier||IS_LEAF|
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.
At TekSlate, we offer resources that help you in learning various IT courses. We avail both written material and demo video tutorials. To gain in-depth knowledge and be on par with practical experience, then explore OBIEE 11g Training Online.
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.
For indepth understanding of OBIEE click on