Feeds:
Posts
Comments

When a BI Answers Report data need to be filtered based upon changing data, this is one method to achieve that.
Lets assume you need to report on products that are added or deleted on a daily basis. We can put that data into an Oracle table and use it as a filter to refine our output.

First lets create an SQL file Entries.sql with some sample data as shown below:

Drop Table SAMP_ENTRIES;
CREATE TABLE “SAMP_ENTRIES”
( “ID” NUMBER,
“STATUS” VARCHAR2(30)
) ;
Insert into SAMP_ENTRIES (ID,STATUS) values (1,’Bluetooth Adaptor’);
Insert into SAMP_ENTRIES (ID,STATUS) values (2,’Game Station’);
Insert into SAMP_ENTRIES (ID,STATUS) values (3,’MaxiFun 2000′);
Insert into SAMP_ENTRIES (ID,STATUS) values (4,’PocketFun ES’);
Insert into SAMP_ENTRIES (ID,STATUS) values (5,’Touch-Screen T5′);
Insert into SAMP_ENTRIES (ID,STATUS) values (6,’LCD HD Television’);

  1. connect to the database as BISAMPLE/BISAMPLE user and password and execute the sql script as shown below:

  2. Open the RPD file in the “BI Administration Tool”.
  3. Right click the “Connection Pool” in the “Physical” Layer and select “Import Metadata”
  4. Click “Next” button to reach to the screen below.
  5. Select the “SAMP_ENTRIES” table and click on the “Import Selected” button then click on the “Finish” button.

  6. Double Click on the “SAMP_ENTRIES” table in the “Physical” pane and under the “Keys” tab enter “ID” for the “Key Name” and select the “ID” under Columns.
  7. Click “OK” button.
  8. Drag the “SAMP_ENTRIES” table from the “Physical” layer to the “Business Model and Mapping” layer under the “Sample Sales” Business Model.
  9. Double Click on the “SAMP_ENTRIES” logical table and select the “Lookup table” checkbox and click the “OK” button.
  10. Drag the “SAMP_ENTRIES” logical table from the “Business Model and Mapping” pane to the “Presentation” pane and rename it to “Filter Vals”.
  11. Select File -> Save and click yes when asked for the global consistency check.

  12. Now reload your RPD file by going to the URL: http://localhost:7001/em
  13. Navigate to the “Farm_bifoundation_domain” -> “Business Intelligence” -> coreapplication.
  14. Select the “Deployment” tab then the “Repository” tab.
  15. Select the “Lock and Edit Configuration”.
  16. Select the “Browse” button and choose the RPD file that you just edited and saved.
  17. Then restart your server under the “Overview” tab.

  18. Now to create your report with the filter from the newly created SAMP_ENTRIES table.
  19. Goto the URL: http://locahost:9704/analytics
  20. Select the “New” -> “Analysis” -> “Sample Sales”
  21. Under the “Filter Vals” folder drag the “Status” column to the “Selected Columns” area.
  22. Save the report as “FilterVals”

  23. Now select the “New” -> “Analysis” -> “Sample Sales”
  24. Drag the “Product” and “Revenue” columns to the “Selected Columns” area.

  25. Select the “Product” options and filter and enter the below values as show in the screenshot.
  26. Click the “Browse” button and select the “FilterVals” report we saved above.
  27. Click the “OK” button.

  28. Now click the Results tab to show the results from the just the entries in the “SAMP_ENTRIES” table.

Summary:
As seen above we have use a filter first by importing the table “SAMP_ENTRIES” to our RPD file, then creating a “FilterVals” table to use as a filter to our final report.
We can automate the process of editin the “SAMP_ENTRIES” table by creating and executing our Entries.sql file when ever our data changes in real time.

Advertisements

Lets say we have working RPD with a “Data source name:” ORCL with a Username and Password : BISAMPLE/BISAMPLE.
Now we need to deploy this RPD on a Linux box where we have an Oracle XE Database installed with the ORACLE_SID: XE.

  1. First open up our created working Windows RPD as shown in the below screen.
  2. Observe that in the “Physical” layer pane “ORCL” is shown as the Database.

  3. Right Click and select “Rename” on the “ORCL” and rename it to the Equivalent connection sting in your tnsnames.ora e.g “XE” as show in screenshot below.
  4. Save your changes and click “Yes” button for the “Do you wish to check global consistency?” dialog.

  5. We go to the “Administration Tool” menu and select “Manage” -> “Variables…”
  6. Change the “BI_EE_HOME” variable’s “Default Initializer:” from the Windows path to equivalent Unix path as show in the below screenshot:

  7. On the linux box where you have the OBIEE 11g “bi_server1” running have the following:
  8. Copy the Oracle’s Database tnsnames.ora to the OBIEE 11g installed home.
    $ cp /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora /home/srikanth/OracleFMW/oracle_common/network/admin/tnsnames.ora
    $ cd /home/srikanth/OracleFMW/oracle_common/network/admin/
  9. Make sure the XE or what ever ORACLE_SID is there is mentioned in the tnsnames.ora
    $ cat tnsnames.ora
    # tnsnames.ora Network Configuration File:
    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srilinlap)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )
  10. Update your bash_profile so that you have the location to the newly copied tnsnames.ora location
    $ cat ~/.bash_profile
    ….
    PATH=$PATH:$HOME/.local/bin:$HOME/bin
    TNS_ADMIN=/home/srikanth/OracleFMW/oracle_common/network/admin
    export PATH TNS_ADMIN
  11. One all the above are done copy over the modified RPD file to the Linux machine and deployed it using the EM from the location http://localhost:7001/em.

Summary:
Basically we are modifying the “ORCL” string to whatever is mentioned in our tnsnames string, here for example is “XE”.
Then we are copying over the tnsnames from ORACLE_HOME to OBIEE_HOME.
Then also setting the TNS_ADMIN variable to point to the new tnsnames.ora location

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.

Here we will be seeing on how to create and achieve two scenarios

  1. A Parent-Child Hierarchical Roll-Up of Revenues from sub-ordinate employees to Root/Top Employee.
  2. In the other we will be seeing how to create and achieve Parent-Child Hierarchy of Employees showing their individual Revenues.

First we have to create our RPD to model out Dimensions and Facts to support the Hierarchical of Sales Representatives.
Secondly we create our report using the “Oracle BI Answers” to show these 2 scenarios.

  1. First to create our star schema, open the “Oracle BI Administration Tool” from “Start” menu.
  2. In the Menu select “File” -> “New Repository…”

  3. When the below window comes up enter values for the Name, “Repository Password:” and “Retype Password:” fields as shown in below screenshot.
  4. Click “Next” button to continue

  5. When the below screen appears select the “ORCL” entry and enter values for the “User Name:” and “Password:” fields as shown below screen shot.
  6. Click the “Next” button to continue.

  7. When the below screen appears leave the defaults as shown in below screenshot and click “Next” button to continue.

  8. When the below screen appears select the four tables as show below and click the “import selected” button.
  9. Click “Finish” to continue.

  10. In the Main “Administration Tool” window right click and select “New Object” -> Alias and enter “D41 Sales Rep” and then click “OK” button to create an alias as shown below:

  11. Similarly go ahead and create the next 3 aliases as show below i.e.: “D42 Sales Rep Parent Child”, “D43 Sales Rep Position” and “F4 Revenue” as show in below screenshot.
  12. Select all the newly created table, right click and select “Physical Diagram” -> “Object(s) and All Joins” .

  13. On the Menu select “New Join” then click on the “F41 Revenue” table then click on the “D42 Sales Rep Parent Child” table.
  14. As show in the below screenshot create the Expression by select first the “MEMBER_KEY” then the “EMPL_KEY” and clink on the “OK” button.
  15. This will create an error link the two tables.

  16. Similarly create a “New Join” between “D42 Sales Rep Parent Child” and “D41 Sales Rep” tables as show in below screen shot by selecting the “EMPLOYEE_KEY” and “ANCESTOR_KEY”.

  17. Similarly create a “New Join” between “D41 Sales Rep” and “D43 Sales Rep Position” tables as show in below screen shot by selecting the “POSTN_KEY” and “POSTN_KEY”.

  18. The Final joins created is as shown in below screenshot.

  19. Right click in the “Business Model and Mapping” pane and select “New Business Model…” and enter “SalesRoot” as show in below screenshot.
  20. Click the “OK” button to close the window.

  21. From the Physical pane to the “Business Model and Mapping” pane drag the “D41 Sales Rep” and “F41 Revenue” tables to under the “SalesRoot” model as show in below screenshot.
  22. Next select both the tables, then right click and select “Business Model Diagram” -> “Whole Diagram”.

  23. Create the following join between the “F41 Revenue” and “D41 Sales Rep” is created and the “Cardinality” is as shown in below screenshot.

  24. Make sure the below join is created as show in screenshot.

  25. Rename your tables and columns as show in below screen shot.

  26. Create a hierarchy by right clicking on “D41 Sales Rep” table and select “Create Logical Dimension” -> “Dimension with Parent-Child Hierarchy…” as shown in below screen shot.

  27. Make sure to select the “Member Key:” value as “D41 Sales Rep_Key” and “Parent Column:” value as “Mgr id” as show in below screen shot.
  28. Click on the “Parent-Child Settings…” button.

  29. In the “Parent-Child Relationship Table Settings” window click on the “Select Parent-Child Relationship Table” icon.
  30. In the “Select Parent-Child Relationship Table” window browse to the “ORCL” -> “SAMP_EMPL_PARENT_CHILD_MAP” table in the left pane and select it, then click on the “Select” button.

  31. In the window shown in below screen shot select the four values for the respective fields as shown in below screenshot.
  32. Click the “OK” button.

  33. Right Click on the “Sales Rep Name” column and select the “New Logical Level Key…”.

  34. Make sure that the values are as shown below and the “Use for display” checkbox is selected as shown in below screenshot.

  35. Expand the “H41 Sales RepDim” Hierarchy and double click the “Detail” folder to open the below window as shown in below screenshot.
  36. Check the checkbox for the “Sales Rep Name” and uncheck the checkbox for the “Sales Rep Number” and make sure the “Parent” value is assigned to “Mgr id”.
  37. Click “OK” button to close the window.

  38. Double click on the “LTS1 Revenue” fact logical source table.
  39. Goto the “Content” tab in the “Logical Table Source – LTS1 Revenue” window as show in below screenshot.
  40. For the “Logical Level” select the “Detail” value.
  41. Click “OK” button to close the window.

  42. Double Click on the “LTS1 Sales Rep” Dimension Logical Source Table.
  43. Click on the “Add” Icon.

  44. Select the two tables on the right pane and click on the “Select” button as shown in the below screen:

  45. Make sure that the below two joins are created as shown in the below screenshot.
  46. Click on the “OK” button to close the window.

  47. Drag the “SalesRoot” from the “Business Model and Mapping” pane to the “Presentation” pane as shown in the below screenshot.
  48. This finishes the creation of the “SalesRoot” Business Model.
  49. Select the “File” -> “Check Global Consistency” option on the main menu and fix any errors shown then save your repository.

  50. Now we are onto creating the “SalesIndiv” Business Model.
  51. In the “Physical” layer pane create the 4 aliases as we did previously but this time name them “D51 Sales Rep” , “D52 Sales Rep Parent Child” , “D53 Sales Rep Position” and “F51 Revenue” accordingly.

  52. Select all the newly created 4 alias tables and right click and choose “Physical Diagram” -> “Object(s) and All Joins”

  53. Create the following Join with “EMPLOYEE_KEY” and “EMPL_KEY” as shown in below screenshot.
  54. Click “OK” to close the window.

  55. Create the following join by selecting the “POSTN_KEY” and “POSTN_KEY” as shown in below screenshot.
  56. Click “OK” button to close the window.

  57. Finally you should end up with the following joins as shown in below screenshot.
  58. Close the “Physical Diagram” window by clicking the “X” button.

  59. Now create a new model “SalesIndiv” in the “Business Model and Mapping” Pane.
  60. Drag the 2 tables “D51 Sales Rep” and “F51 Revenue” to the “SalesIndiv” Business model.
  61. Select both the tables and Right click, select “Business Model Diagram” -> “Whole Diagram” .

  62. Make sure that the link is shown as in below screenshot.

  63. Double click on the “LTS1 Sales Rep” and Click on the “+” icon.
  64. In the Browse window select both the tables and click the “Select” button as shown in below screenshot.

  65. Make sure that the two joins have been created as shown in below screenshot.

  66. Select the “D51 Sales Rep” and right click and select the “Create Logical Dimension” -> “Dimension with Parent-Child Hierarchy”

  67. Make sure that “D51 Sales Rep_Key” and “Mgr id” are selected for the “Member Key:” and “Parent column:” values respectively as shown in below screenshot.

  68. Click on the “Parent-Child Settings…” button.
  69. Click on the “Select Parent-Child Relationship Table” icon.
  70. In the new window that comes up select the “D52 Sales Rep Parent Child” table and click the “Select” button.

  71. Click “OK” on the “Logical Dimension – D51 Sales RepDim” window to close it.
  72. Select the appropriate “MEMBER_KEY”, “ANCESTOR_KEY” , “DISTANCE” and “IS_LEAF” values as shown in below screenshot.

  73. Right click on the “Sales Rep Name” and select the “New Logical Level Key…” as shown in below screenshot.

  74. Make sure that the values correspond to that shown in the below screenshot:
  75. Click “OK” button to close the window.

  76. Click on the “Detail” Level folder and confirm below values are shown as in below screenshot.
  77. Click “OK” button to close the window.

  78. Drag the “SalesIndiv” Business Model from the “Business Model and Mapping” pane to the “Presentation” pane and validate everything is ok as shown in below screenshot.
  79. Select the “File” -> “Check Global Consistency” option on the main menu and fix any errors shown then save your repository.

  80. We are now going to implement our hierarchical and individual roll-up reports.
  81. Open up the web browser and enter the URL: http://localhost:9704/analytics
  82. On the OBIEE Menu select New->Analysis->Select Subject Area->SalesRoot.
  83. Select the two columns as shown in below screenshot:

  84. For the 2nd column, select Options and “Edit Formula”.
  85. In the “Column Formula” field enter the below formula as seen in the screenshot:

  86. Now goto the “Results” tab and we can observe that all the totals are added up at the top most Sales Rep: Michele Lombardo.

  87. No we create for the individual roll-up totals.
  88. On the OBIEE Menu select New->Analysis->Select Subject Area->SalesIndiv.
  89. Select the 2 columns as shown in the below screenshot:

  90. Select the 2nd column options “Edit formula” and enter the below formula as shown in below screenshot:

  91. Now click the “Results” tab to see the output.
  92. Here we can see that the Individual Roll-Up of totals where the Sales Rep: Michele Lombardo total Revenue made is $110,000.00 dollars where as compared to the
  93. previous Total Hierarchical Roll-Up was : 50,000,000.00 which was the totals of his and his sub-ordinates totals.

  94. There is one more thing that is observed above in the report is that the Sales Rep: Sophie Bergman and her sub-ordinates are not listed here since “Sophie Bergman” has no rows which contributes to the Revenue, as can seen in the below screenshot:

  95. In order to list these “No Rows” Sales Reps i.e. Sophie Bergman and her sub-ordinates we would have to go with an alternate procedure that includes the “UNION” reports.
  96. Open your web browser to the URL: http://localhost:9704/analytics and login as admin user “weblogic”
  97. Select the New->Analysis->Select Subject Area->SalesIndiv.
  98. Select the 2 columns “Sales Rep Name” and “Revenue”.
  99. Select the 2nd column options “Edit formula” and enter SUM(“F51 Revenue”.”Revenue”) formula and click the “OK” button.
  100. Next select the “Combine results based on union, intersection, and difference operations” icon i.e green “+” icon.
  101. Under the “Select Subject Area” section select “SalesIndiv” as shown in the below screenshot.

  102. Drag the “Sales Rep Name” to the “Add Column(Sales Rep Name)” section.
  103. Drag the “Sales Rep Number” to the “Add Column(SUM(Revenue))” section.
  104. On the 2nd “Sales Rep Number” column select options “Edit formula” and enter ‘ ‘ i.e two single quotes with a space in between and click “OK” button.

  105. Your final Union Report should look as shown in the below screenshot.

  106. Click on the “Result” tab to display the report as shown in the below screen:
  107. You can notice that Sales Rep: Sophie Bergman and her sub-ordinates are now seen but we have lost the Hierarchical structure that exists above.

Note:
In order to get the “SalesIndiv” subject area to work with the hierarchical layout and the Individual Roll-Up criteria, one way is to get those members with out any rows or table entries to be updated with entries for atleast one row with a value of “0” for revenue field.
With this we would not require the UNION report workaround.

Summary:
As can be seen we have ran through a lot of steps to create our Business Models for both the hierarchical and Individual roll-up scenarios.
After demonstrating the lack of support to the 0-rows members to show up in the hierarchical order, we have created one work around to view those members.
Maybe there is a way for us to come up a Hierarchy for the Individual roll-up and I will be happy if some one can post a comment and point me in that direction.

There will be cases where you will have to relocate or point your OBIEE 11g Metadata repository to another system and update the appropriate OBIEE’s settings so your BI and scheduler services are back in operation.

Here lets take a scenario where your current Metadata is stored in an Oracle 11g database on a Windows system with ip-address :10.0.0.3, we now have to relocate or point our existing OBIEE installation which exists on a linux system with ip-address 10.0.0.2 to the new Metadata repository location to a Windows system with ip-address: 10.0.0.4.

  1. First we would need to login into our Linux system where the OBIEE is installed and update the Oracle DB Client installation which contains the tnsnames.ora file and update it to point our OBIEE to 10.0.0.4 as show below:
    [srikanth]$ cd $TNS_ADMIN
    [srikanth]$ pwd
    /home/srikanth/app/srikanth/product/11.2.0/client_1/network/admin
    [srikanth]$ vi tnsnames.ora
  2. Make sure the below exists in tnsnames.ora which points to the new Metadata repository on 10.0.0.4 system:
    XE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ) ) 
  3. Now start the Weblogic Server and change JDBC connection strings as below:
  4. First start your nodemanager using startNodeManager.sh script
    [srikanth]$ pwd
    /home/srikanth/OracleFMW/wlserver_10.3/server/bin
    [srikanth]$ ./startNodeManager.sh &
  5. Then goto the startWeblogic.sh script location to start the weblogic server
    [srikanth]$ pwd
    /home/srikanth/OracleFMW/user_projects/domains/bifoundation_domain/bin
    [srikanth]$ ./startWebLogic.sh
  6. Open your web browser and enter the URL: http://localhost:7001/console and login is as “weblogic” user.

  7. Goto the “Services” -> “Data Sources” in the left “Domain Structure” pane.
  8. click on the “bip_datasource” link under the “Data Souces” pane.

  9. Select the “Connection Pool” tab and then click on the “Lock & Edit” button under the “Change Center” pane.
  10. Change the ip-address from the old metadata repository location to the new metadata repository location that is : 10.0.0.4 and click the “Save” button.
  11. Click on the “Activate Changes” button once it is shown.

  12. Next goto the “Services” -> “Data Sources” -> “EPMSystemRegistry” link and select the “Connection Pool” tab.
  13. Click on the “Lock & Edit” button under the “Change Center” pane.
  14. Change the ip-address from the old metadata repository location to the new metadata repository location that is : 10.0.0.4 and click the “Save” button.
  15. Click on the “Activate Changes” button once it is shown.

  16. Next goto the “Services” -> “Data Sources” -> “mds-owsm” link and select the “Connection Pool” tab.
  17. Click on the “Lock & Edit” button under the “Change Center” pane.
  18. Change the ip-address from the old metadata repository location to the new metadata repository location that is : 10.0.0.4 and click the “Save” button.
  19. Click on the “Activate Changes” button once it is shown.

  20. We would now need to update the “coreapplication” settings through the EM.
  21. Open a web browser and enter the URL: http://localhost:7001/em and login with user “weblogic”.
  22. Goto the “Farm_bifoundation_domain” -> “Business Intelligence” -> “coreapplication” folder in the left pane.
  23. Select the “Scheduler” tab and update the ip-address to the new location i.e: 10.0.0.4.
  24. Click the “Apply” button.

  25. Then click the “Activate Changes” button.
  26. Then click the “Close” button on the “Confirmation” window.

  27. Now we would need to stop and restart all the “Admin Server” and “Managed Servers” in order the changes to take effect.
  28. To stop all the services use below:
    [srikanth]$ pwd
    /home/srikanth/OracleFMW/user_projects/domains/bifoundation_domain/bin
    [srikanth]$ ./stopManagedWebLogic.sh bi_server1
    [srikanth]$ pwd
    /home/srikanth/OracleFMW/user_projects/domains/bifoundation_domain/bin
    [srikanth]$ ./stopWebLogic.sh
  29. To start the services again user the below:
    [srikanth]$ pwd
    /home/srikanth/OracleFMW/user_projects/domains/bifoundation_domain/bin
    [srikanth]$ ./startWebLogic.sh
    [srikanth]$ pwd
    /home/srikanth/OracleFMW/user_projects/domains/bifoundation_domain/bin
    [srikanth]$ ./startManagedWebLogic.sh bi_server1
    [srikanth]$ pwd
    /home/srikanth/OracleFMW/instances/instance1/bin
    [srikanth]$ ./opmnctl startall
    opmnctl startall: starting opmn and all managed processes…
  30. We can check that all the services up and running by using the below command:
    [srikanth]$ ./opmnctl status

Processes in Instance: instance1

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.

This demonstration shows how we can create ticker boxes to show scrolling content for example some market results or other eye appealing information.

  1. Open the URL: http://localhost:9704/analytics and login as administrator user “weblogic”
  2. Goto the “New” -> Analysis -> “Sample Sales” and select the below 5 columns as shown:
  3. i.e. “Customer Name”, “Product” , “Units” , “Revenue” and another “Revenue” column.

  4. Select the 5th column options and select “Edit formula”.
  5. In the “Column Formula” section select “F(…)” and in the “Insert Function” window select “Aggregate” -> “Rank” so the formula shows:
  6. RANK(“Base Facts”.”Revenue”) and click the “OK” button.

  7. Select the 4th “Revenue” column options and select “Sort” -> “Sort Descending”
  8. Next select the “Results” tab to show the report.
  9. On the “Subject Areas” menu select “New View” -> “Other Views >” -> Ticker as shown in below screenshot.

  10. Click on “Edit View” on the “Ticker” menu.
  11. Once the Ticker screen opens, click on the “Clear Fields” button to clear all fields.
  12. Select the “Contains HTML Markup” checkbox.
  13. Select “Up” for the “Direction” field.
  14. Enter “700” for the “Width” field and leave the “Height” field empty.
  15. Enter the below in text in the “Row Format” field:

    <p>&nbsp;&nbsp;<font

    color=”#000099″><b>@1</b></font> is ranked <b>#@5</b> with revenue of <font color=”green”><b>@4</b></font> for selling <b>@3</b> units of <font color=”#009900″><b>@2</b></font> </p>

  16. Cilck the “Advanced…” button.
  17. Enter “2” for the “Scroll Amount” field.
  18. Enter 1 for the “Scroll Delay” field.
  19. Enter below text for the “Additional Marquee Attributes” field:

    onMouseEnter=this.stop();

    onMouseLeave=this.start(); onmouseover=this.stop(); onmouseout=this.start(); class=TickerMarquee

  20. Click “OK” button to close the “Advanced Ticker Options” window.
  21. Then click on the “Done” button to go back to the “Results” tab with “Compound Layout”.

  22. Click on the “Save Analysis” icon to save your report.
  23. Click on the “Show how results will look on a Dashboard” icon to open the window as below with the ticker box scrolling text upwards.

Summary:
Here we saw that by applying html formatting we can achieve almost what ever layout to our data columns.