connect power bi to oracle analytics

Oracle Analytics With Power BI Extensibility: The 5-Minute Magical Setup

Oracle Analytics is technically the revamped version of Oracle Business Intelligence Enterprise Edition (OBIEE). Oracle offers Oracle Analytics in 2 versions – Cloud and On-prem.

The Cloud version is branded as Oracle Analytics Cloud (or OAC) and the On-prem version is referred to as Oracle Analytics Server (or OAS).

Oracle has made tremendous efforts to eliminate the disadvantages of OBIEE in the Oracle Analytics products. Both Oracle Analytics products are self-service and data visualization friendly. Further, they are also fitted with AI and ML capabilities, apart from simplified options for the data management part.

However, many early adopters of Oracle Analytics are connecting Power BI to Oracle Analytics due to user preferences.

In this blog post, we’ll see how to connect Power BI to Oracle Analytics.

Step 1: Download & Install BI Connector

BI Connector is a Power BI Certified connector for connecting to Oracle Analytics data sources. 

Technically it’s a 64-bit ODBC driver (for Windows machines), and it works on all possible scenarios you can imagine, including the following:

  1. Use both Subject Areas and Reports as data sources in Power BI
  2. Retain the table relationships with a super-simple hack
  3. Access only the data you have access to on the Oracle Analytics end
  4. Connect in both Import and Direct Query modes
  5. Create reports in Power BI Desktop and publish them to Power BI Service (Gateway/Report Server) using the BI Connector Server Edition
  6. Schedule data refresh as required

You can download the connector from here, and extract the content to a convenient path. Then go to the path where you extracted the file, and run the .exe file for installation. The entire process takes hardly a minute!

Step 2: Activate the License

Once you install the connector, you’ll just need to open the 64-bit ODBC console, click on Add, select BI Connector driver and click Finish.

Now you’ll be prompted to enter your information. The default trial license key will be shown in the license key field. You can simply click on the Activate button after filling in the information.

If you’re installing on an environment with restricted or no internet connectivity, you can perform an offline license activation using the steps here.

Once you activate the license successfully, you’re all set to create a DSN for your Oracle Analytics.

Step 3: Create a DSN for Your Oracle Analytics

The DSN configuration is a simple step. Just give a name to the DSN. Then enter the url you would type in a web browser to access your Oracle Analytics account on the Server name field.

Enter the Port Number. Check the Authentication box and leave the type as Default. If your Oracle Analytics Environment is using a specific external authentication like Windows Native Authentication, OAM, Oracle EBS, etc, you can select the appropriate authentication type from the drop-down.

In the credentials section, just enter the same username and password you use to access your Oracle Analytics account. This makes sure you’re able to access the data which you’re authorized to access on your Oracle Analytics account.

DSN Setup for Oracle Analytics Subject Area(s)

If you want to connect to Subject Areas, choose the Data Source Type as Subject Areas. You can also click on the Manage Subject Area List link and add the specific Subject Areas you want to analyze in Power BI.

DSN Setup for Oracle Analytics Report(s)

In case you want to connect to your Oracle Analytics Reports, select the Data Source Type as Reports. Then click on the Manage Reports List link. This will open the Reports configuration window. Here, you must mandatorily add the specific folders or reports path of the reports you want to analyze in Power BI.

The above step of adding the path is mandatory only for a Reports DSN, and not for a Subject Area DSN. You can also simply check the Include My Folder Reports box at the bottom of the Reports Configuration window.

Now that you’ve configured the DSNs, you’re all set to visualize your Oracle Analytics data in Power BI!

Step 4: Visualize Oracle Analytics in Power BI

Based on the Data Source Type (Subject Area or Report), and the Power BI’s Connectivity mode (Import or Direct Query), there are 4 scenarios you can come across in your Power BI Desktop.

First, let’s see the common steps for all 4 scenarios – Open a Power BI Desktop instance. Under Get Data, select BI Connector. In the pop-up window that opens, enter the DSN you want to connect to, along with your Oracle Analytics credentials.

In case of connecting to Reports DSN (a DSN configured with Reports as the Data Source Type), select the report you want to analyze in Power BI. Your Oracle Analytics Report is now available as a flat table to analyze on Power BI, regardless of Import or Direct Query modes.

In case of connecting to a Subject Area DSN (a DSN configured with Subject Area as the Data Source Type), select the Subject Area you want to analyze in Power BI. Then select the specific Fact and Dimension tables you need for your visualization. Based on the connectivity type, use the steps (to retain the joins between your Subject Area tables) given in the below articles to visualize your Oracle Analytics data.

Connect Power BI to Oracle Analytics Subject Area via Import Mode

Connect Power BI to Oracle Analytics Subject Area via Direct Query Mode

You can also install and configure the BI Connector Server edition on the Gateway (or Report Server) machine, and publish Oracle Analytics reports to the Power BI service and/or configure Scheduled Refresh in minutes!