Parent child hierarchy in obiee 11g

· From the Business model, select parent child hierarchy.

· Provide the name as staff(parent-child)

· From the member_key, Click on Browse and select "sales"."Dim staff"."Dim_staff_parent_child_key".

· From the parent key, click the browse and select"sales"."Dim staff"."managerId".

· Next you will define the closure table taht will resolve the parent child hierarchy in your data.

· Click on the parent child settings.

· Click on create parent child relation ship.

· The generate parent child relationship table script location dialog will then be displayed. On this first page, type following values:

DDL script to create parent child relationship table:

Name: Create_staff_closure_table.sql

Location: C:\Files

DDL script to populate parent child relationship table:

Name: pop_staff_closure_table.sql

Location: C:\Files.

· Click on Next.

· The generate parent child relationship table_parent_child relationship table dialog will then be displayed.

· Provide a name as staff_closure_train

· Leave the remaining value as it is.

· Click on Next.

· Review the script.

· Click on Finish.

· Now go to sql prompt.

· sql>prompt and execute the following two scripts.

· sqlplus gcbc_sales/password

· @C:\Files\create_staff_closure_table.sql;

· @C:\Files\Pop_staff_closure_table.sql;

· Commit;

· Note: you should receive the message pl/sql procedure successfully completed after each command is submitted.

· Enclosure that the commit is done with out fail.

· Else the reports on hierarchy columns will not produce any data.

· Now go back to Administration Tool. Click on OK and OK again. Close the Parent child hierarchy window.

· Now navigate to staff(parent-child) hierarchy and you should be able to see total level and detail level.

· Drag and drop Dim staff from BMM to Presentation.

· Expand Dim staff and double click on staff hierarchy,

· Click on display columns tab, delete staff ID.

· Add staff name.

· Save repository.

· Close rpd.

· To test ragged and skipped level hierarchy, add :stores(hier) and sales amount.

· To test parent child add:staff(hier) and salesamount.