[OBIEE11g] - Show top-N Sales Persons in BI Publisher with Snowflake dimension

This procedure shows how to achieve the top-N Sales Persons when dimensional data is present in a snowflake layout.

1. First create a JDBC connection in the BI Publisher Administration section by going to the url: http://localhost:9704/xmlpserver/and login as “weblogic” user.
2. Under the Administration section select “JDBC Connection” under the “Data Sources” sub-section.
   
3. In the JDBC web page select “Add Data Source” button.
4. In the “Add Data Source” screen enter the following:
5. For the “Data Source Name” enter the name for the connection e.g: “XE”
6. For the “Driver Type” select “Oracle 11g”
7. For the “Database Driver Class” field leave the default “oracle.jdbc.OracleDriver”
8. For the “Connection String” field enter the location and port of the Oracle Database where your BISAMPLE schema exists.
9. For the “Username” field enter “BISAMPLE” and for the “Password” field enter “BISAMPLE”.
10. Verify your entries with the below screenshot.
    
11. To verify that the entries are correct, click the “Test Connection” button and make sure that “Connection established successfully” appears at the top of the screen.
12. Click the “Apply” button to save your changes.
13. Your entry will be present in the list as show below:
    
14. In the “Administration” screen menu select New -> “Data Model”
15. select “XE” for the “Default Data Source” field, this the JDBC connection we have created above.
    
16. Click the Save icon on the top right corner and save it under the “Shared Folders” -> “11g Shared” -> “BI Reps” folder, enter “SalesRepModel” for the “Name” field.
17. Click the “Save” button.
    
18. Click the “Data Sets” option under the “Data Model” panel on the left side.
19. Under the “Diagram” tab select “SQL Query” as show in below screen shot:
    
20. Click the “Query Builder” button on the “Create Data Set – SQL” screen.
    
21. In the “Query Builder” Window select the three tables on the left pane i.e. “SAMP_EMPL_D_VH” , “SAMP_EMPL_PARENT_CHILD_MAP” and “SAMP_REVENUE_F” tables.
22. Create the foreign key links between the tables.
23. Click on the Right White Square next to the “EMPLOYEE_KEY” in the first table then click on the Right White Square next to the “MEMBER_KEY”.
24. Click on the Right White Square next to the “ANCESTOR_KEY” in the second table and then on the Right White Square next to the “EMPL_KEY” in the third table.
25. Now select the columns from the tables that we would require to display in our reports and which are required to generate our SQL query which constitutes the Data Source.
26. Check the “EMPL_NAME” checkbox in the first table, the “MEMBER_KEY” checkbox in the second table and the “REVENUE” checkbox in the third table.
27. Click on the “Conditions” link.
    
28. In the “Conditions” Screen, de-select the “MEMBER_KEY” checkbox under the “Show” column.
29. For the “REVENUE” row select the “SUM” under the “Function” column.
30. Click the “Save” button
    
31. In the “Create Data Set – SQL” Screen enter “topNSQL” for the “Name” field.
32. Update the “SQL Query” section with the “DISTINCT” Function so the query is as show in below screen shot.
33. Click on the “OK” button to close the window.
34. Save the Model by clicking on the “Save” icon and Enter “SalesRepModel” for the “Name” field.
35. Now click the xml icon  to run the query.
    
36. In the “SalesRepModel” screen select “All” for the “Number of rows to return” then click the “Run” button.
37. The select the options “Save As Sample Data” option.
    
38. In the “Diagram” screen select the “New” -> “Report” option as shown in below screenshot.
    
39. In the “Create Report – ” window select the “SalesRepModel” then click the “Next” button.
    
40. On the next screen select “Use Report Editor” and click on the “Finish” button.
    
41. Enter “SalesRepReport” in the “Save As” window and click the “Save” button.
    
42. In the Report Editor as show below select the “Data Table” icon.
    
43. Drag the “EMPL_NAME” and the “REVENUE” columns from under the “DATA_DS” -> “G_1″ folder on the left pane to the “Data Table” as show below.
44. Select the Data rows under the “REVENUE” column and select the “Descending Order” icon as shown in below screenshot.
    
45. Click the “Save” icon and enter “SalesRepLayout” for the “Layout Name:” field and click the “Save” button.
    
46. Select the data rows under the “REVENUE” column and select the ($1,234.57) format under the “Data Formatting” from tool bar.
    
47. Select the “Interactive Preview” icon to view your report. 

Summary:
As you can observe that achieving the type of report is mainly dependent on how your query is constructed for your data source.