[OBIEE11g] - Creation of Sales Reps Hierarchy with their Individual Revenue Totals

Here it will be shown on how to display the Hierarchy of Sales Reps along with their Individual Revenue earned.
The OBIEE default behavior is to show the Rolled Up Revenue to the Sales Rep’s Manager or Root level.
Here we need to overcome that behavior and just show the Sum(Revenue) to each Sales Rep level.

Import the 4 tables as shown in below screenshot:
Create the corresponding Aliases i.e “D51 Sales Rep”, “D52 Sales Rep Parent Child”, “D53 Sales Rep Position” and “F51 Revenue” as shown in below screenshot:
Select and right click the newly created Aliases and select “Physical Diagram” -> “Object(s) and All Joins” as show in below screenshot:

Select the “New Join” icon and create the below joins using the expressions below:

“ORCL”.”".”".”D51 Sales Rep”.”EMPLOYEE_KEY” = “ORCL”.”".”".”F51 Revenue”.”EMPL_KEY”

“ORCL”.”".”".”D53 Sales Rep Position”.”POSTN_KEY” = “ORCL”.”".”".”D51 Sales Rep”.”POSTN_KEY”

You final “Physical Diagram” should look like in the below screenshot:

Right click in the “Business Model and Mapping” pane and select the “New Business Model…” and enter “SalesIndiv” and click “OK”.
Drag the “D51 Sales Rep” and “F51 Revenue” Aliases from “Physical” pane to the “Business Model and Mapping” pane under the “SalesIndiv” as shown in below screenshot.

Select the both Logical tables as shown below and select “Business Model Diagram” -> “Whole Diagram”.
Make sure the below link is created with Cardinality “0,1″ on “D51 Sales Rep” side and “N” on the “F51 Revenue” side.

Right click on the “D51 Sales Rep” and select “Create Logical Dimension” -> “Dimension with Parent-Child Hierarchy…”

Select the “D51 Sales Rep_Key” for “Member Key:” and “Mgr id” for the “Parent Column” as shown in below screenshot.
Click the “Parent-Child Settings…” button.
Select the “Select Parent-Child Relationship Table” icon.
In the “Select Parent-Child Relationship Table” window select “ORCL” -> “D52 Sales Rep Parent Child” and click the “Select” button.
In the “Parent-Child Relationship Table Settings” window select the appropriate values for the 4 “Parent-Child Relationship Table Column Details” fields as shown in below screenshot:
Click the “OK” button to close the window.
Change the “D51 Sales RepDim” to “H51 Sales RepDim”.
Click the “OK” button to close the “Logical Dimensions – D51 Sales RepDim” window.

Expand the “H51 Sales RepDim”.
Delete the other columns and keep only the “Sales Rep Name” and “Sales Rep Number” under the “Detail” Level.
Right Click on the “Sales Rep Name” and select “New Logical Level Key…” and confirm the settings as in below screenshot:

Double Click on the “Detail” Logical Level and make sure the values as shown in the below screenshot:

Drag the “Sales Indiv” Business Model pane to the “Presentation” pane as shown in the below screenshot:
Select the “File” -> “Check Global Consistency” option on the main menu and fix any errors shown then save your repository as “SalesInd.rpd”

Open the Enterprise Manager Console and load your newly created RPD.
Open the URL: http://localhost:7001/em and login with administrator “weblogic”.
Goto the “Farm_bifoundation_domain” -> “Business Intelligence” -> “coreapplication” in the left pane.
Select the “Deployment” tab -> “Repository” tab.
Click the “Lock and Edit Configuration” section link.
Click the “Browse” button and locate your RPD “SalesInd.rpd”
Enter the RPD password in both the “Repository Password” and “Confirm Password” fields.and hit “Apply”.
Click on the “Activate Changes” link.
Click on the “Restart to apply recent changes” link.
Then in the “Overview” tab click the “Restart” button and “Yes” to the confirmation.

Now to test out our RPD in the Answers.
Goto the web URL: http://localhost:9704/analytics and login as “weblogic”
Select the “New” -> “Analysis” -> “SalesIndiv” from the Answers menu.
Select the columns as shown below in the screenshot:

Click on the “Results” tab.
We can observe that not all the Sales rep “SUM(Revenue)” are shown in the table below.

The problem is that Sales Reps with “EMPL_KEY” 26, 23 and 22 do not have any rows associated with them in the SAMP_REVENUE_F(F51 Revenue) table, which results these and their sub-ordinates to be omitted in the hierarchy table above.

In order to fix that we will create some dummy rows for the 3 Sales Reps, as shown below observe that “EMPL_KEY” is given 26, 23 and 22.

Now we restart our BI services and we can see that all the Sales Rep members are shown now with their Revenue’s totaled.

Summary:
Here in order to get the required Sales Rep Hierarchy output we have made changes to the Data Source Database. That can be avoided by initially implementing this logic to the ETL side.