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.