Taking Advantage of Oracle Analytics Data Visualizations in Classic Dashboards

Many companies are still using Oracle Analytics Classic Dashboards and Answers (Classic) but would like to take advantage of some of the new visualizations that are available in Oracle Analytics Data Visualization (DV).  This can be easily accomplished by creating a visualization in DV an adding it to a dashboard page in OAC.  The visualization can also be filtered based on Classic dashboard prompts.

Below is an example of a Treemap visualization built in DV and is displayed on an OAC Classic Dashboard.

                   

Below are the steps needed to build this simple dashboard page.

1. Create the visualization in DV with no filters. Filters will cause issues if you are using Classic dashboard prompts. Save the visualization.

2. Create a new dashboard in Classic and add a Classic dashboard prompt and the visualization that you created in DV.  Be sure you select the canvas that you want to display.

    Once this is done, the dashboard is ready to go.   

 

3.  To test the filters, I opted to Select COMMERCIAL on Construction Type.  The DV data is now reflecting data for Task Suffix 0100 and Construction Type COMMERCIAL. 

An Important Note:  If you add canvases to the DV workbook, add them to the end.  Reordering the Canvas can impact what is displayed on the dashboard page.  In the screenshot below, I added a canvas to the DV workbook and moved it before the TreemapVis canvas.

 

When I go back to the dashboard that I created earlier, my List of Divisions canvas is displaying.

 

To fix this, edit the dashboard and change the view:

 

The dashboard is now displaying correctly: 

Good Luck and Happy Analytics! 

Connecting Power BI to OAC Subject Areas and Analyses

Last fall, Oracle worked with Microsoft to open OAC up to Power BI users and allow them connect to OAC data.  Currently, this can be done 3 ways:

1. Pulling data from tables in an OAC Subject Area.
2. Pasting in the logical SQL from OAC.
3. Pulling data from an OAC Classic analysis (this capability came out in 2023)

This blog will cover what I experienced when installing and using the Power BI connection to OAC data.  

Installation:

Oracle has good documentation on connecting to Oracle Analytics from Power BI, https://docs.oracle.com/en/cloud/paas/analytics-cloud/acsds/connect-oracle-analytics-cloud-microsoft-powerbi.html  This provides a detailed step by step on getting it set up.  Things to note:

•  OAC client tools, Model Administration, need to be installed on the users’ computers.  The Model Administration Tool allows users to edit the OAC Semantic model (RPD file).  You do not need access to modify the RPD they just the tool installed on their computer.

• You will need to have the latest version of Power BI Desktop and Model Administration Tool installed. 

• The document states you must download a .mez file and the documentation gives instructions to create folders in the c:\users\yourusername\documents. If you have a shared documents directory, like OneDrive,  you may need to put the file in that directory instead of the C-Drive. 

•  Make sure you follow the instructions and you will need to exit and restart Power BI once you make the setting changes.  

Things to be aware when using Power BI:

• You can only connect to OAC presentation subject areas (not the logical or physical layer) from Power BI Desktop.  

• Power BI Pro and Premium are not supported.  

• To access OAC, the user must have BIContentAuthor role privileges or higher in Oracle Analytics Cloud. Is this an issue?

• When you log in using the App Key, you may get an error message. I only got it a couple of times at first, but I just needed to try again and then it worked.  I had the same issue with getting the list of subject areas to appear, sometimes I get an error message, but I refresh a few times and it will eventually come up. 

• Pulling data from tables in an OAC Subject Area
• In order to report OAC subject areas in Power BI, we will need to bring the unique IDs or WIDs to the presentation layer for the fact tables to join them to the dimension table in Power BI.  In OAC, we join them in the physical layer.  Since Power BI pulls data from the presentation layer the joins will not come along. If you want to bring over an ID or WID to the presentation layer and not have it appear to the users in OAC, you can edit the properties and fill out the Hide object if box with "1=1".   

        The ID will be visible in Power BI but not in OAC. 

• The data is filtered once it is bought over to Power BI.  If you have a large table it will attempt to bring over the entire table and will error out on too many rows.    
  

•  Pulling data Logical SQL
• In order to report OAC subject areas in Power BI, we will need to bring the unique IDs or WIDs to the presentation layer for the fact tables to join them to the dimension table.
• To access the logical SQL in OAC, go to the Advanced tab of the analysis and copy all the logical SQL Issued.

 

 

• The logical  creates aliases for the column name.  The screenshot below show s_1 and s_2.  This can be changed once you load the data in PowerBI, but I find it easer to just remove the alias prior to pasting the logical SQL into PowerBI.

• Pulling data OAC Classic Analysis
• When you select an analysis, the logical SQL is produced and the column names that are pulled over are the alias names that are generated in the logical SQL, like the s_1 and s_2 above.  

Overall all, the documentation is provided is good and I have found that the connection works well as a alternative solution if your organization have users who are exporting data out of OAC into PowerBI.