[BI Connector v 6.x] How to Create a Connection to Oracle OTBI, OAC, OAS, Fusion, OBIEE Analysis in the BI Connector Application?

Modified on Fri, 31 May at 11:23 AM

This article is applicable for BI Connector v 6.x and above.


BI Connector supports connectivity to Analysis in the following applications:


  • Oracle Transactional Business Intelligence (OTBI) that's available as a part of
    • Oracle Fusion Enterprise Resource Planning (ERP) Applications
    • Oracle Fusion Supply Chain Management (SCM) Applications
    • Oracle Fusion Human Capital Management (HCM) Applications
    • Oracle Fusion Customer Experience (CX) Applications
  • Oracle Analytics Cloud (OAC)
  • Oracle Analytics Server (OAS)
  • Oracle Business Intelligence Enterprise Edition (OBIEE) 
  • Oracle OPERA Cloud
  • Taleo
  • NetSuite


The following is a general tutorial to create a connection to your Oracle Analysis in the BI Connector app, which you can leverage in Power BI to visualize the data and automate scheduled refreshes.


In BI Connector, the process of creating a connection to the Oracle Analysis comprises 2 steps - 

Create Data Source 

Add Table


Let's see how to execute these steps below. Before we start, please have the following handy:


For Oracle OTBI, OAC, Fusion Analytics users 

  • Oracle Account URL or Server URL - The URL up to "oraclecloud.com" after logging into you Oracle application. In essence, the URL will be something like
    https://xx-xx-xx-xx.oraclecloud.com (or)
    https://[RandomID].oraclecloud.com
  • Your Login credentials


For Oracle OBIEE, OAS users

  • Oracle Account URL or  Server URL - The URL after logging into the application, along with the port number. The URL could look something like this:
    http://obiee.CompanyName.com:9502/analytics
    (or) an IP address with a port number like
    http://xxx.xxx.xxx.xxx:9704/analytics
    In the above examples, the server names are http://obiee.CompanyName.com and http://xxx.xxx.xxx.xxx respectively. And the port numbers are 9502, and 9704 respectively.
    In essence, the final Server URL would be ServerName:PortNumber, which is the following for the above examples:
    http://obiee.CompanyName.com:9502
    http://xxx.xxx.xxx.xxx:9704
    Note: If there is no port number visible in the url, the Port number used behind the scenes is usually 443 if the url starts with HTTPS or 80 if the URL starts with HTTP
     
  • Your Login credentials



Step1: Create Data Source


In the BI Connector app, click on the Data Sources menu in the pane on the left side, and click on the New Data Source button.


Click on Data Sources in the left pane and click the New Data Source button


In the next screen, give the Data Source a name (that's easy for you to identify in Power BI) in the Data Source Name field. Then select the Data Source Type as Analysis(Reports). Then enter the Server URL in the Oracle Account URL field, and enter your Oracle login credentials in the User Name and Password fields. Finally, click on the Test & Save button.


Enter your Oracle BI account URL, credentials, select data source type as Analysis and click Test and Save


You can confirm the Data Source creation in the BI Connector application is successful by the notification displayed at the top right, and the Data Source being listed on the application.


Data Source successfully created confirmation




Step2: Add Table


Now that you've successfully created a Data Source for your Oracle Analysis in the BI Connector application, it's time to add the folder path(s) of the Analysis you want to analyze in Power BI.


To do this, click on the Tables menu in the left side pane, and click on the Add Table button.


Click Tables in the left pane and click the Add Table button


Next, in the Data set name field, give the Dataset a name that's easy for you to identify. Then select the Data Source you created for the Analysis in the Data Source field from the drop-down. Next, its recommended to leave the number of rows per fetch field with the default value shown, and click Next:Manage Reports button.


Note: The No. of rows per fetch is to help you avoid time out errors when dealing with large datasets. For example, if your Analysis has 2 Million rows, a no. of rows per fetch setting of 100,000 will help you fetch all the 2 Million rows in batches of 100,000 rows at a time for 20 fetches, instead of fetching all the 2 Million rows at once in a single fetch, which would most likely result in time out errors. In most cases, the default 100,000 value would work seamlessly. But if you face time out errors, you can reduce the value in this setting at anytime.


Enter the required info in the Select Data Source screen


Next, you need to enter the path of the Folders containing the Analysis (which you want to analyze in Power BI). To do this, please open your Oracle Analytics application in the browser, and navigate to the Catalog. Next navigate to the location of the Analysis(s) you want to analyze in Power BI. Then, click on the More option on any of the Analysis.

Open your Oracle BI Catalog in browser and navigate to the Analysis of your interest and click More

Note: Oracle Fusion users can navigate to the OTBI Catalog by clicking on Tools -> Reports and analysis -> Browse Catalog. Then the screen like above will open, on which you can navigate to the Analysis you want to analyze in Power BI, and click on the More option corresponding to the Analysis.


In the menu list that opens up, select Properties.


Click on Properties



In the pop-up that opens, please copy the content on the Location field. 

Copy the folder path of the Analysis of your interest


Next, in the BI Connector application, please paste the path copied in the previous step on the Report or Folder Path box, and click on Verify & Add button.
IMPORTANT: The path copied is case-sensitive, and hence must be pasted as in the Report or Folder Path. Also, remove the extra trailing and preceding spaces in the pasted path, if any.


Paste the copied folder path in the BI Connector app and click Verify and Add

Note: If you'd like to add a sepcific Analysis's path (instead of a Folder path), you just need to add the folder path, and append a "/[AnalysisName]" at the end of the pasted path. For example if an Analysis named Americas Orders is located on the /shared/Northwind folder, just type the folder path as it is, and append "/Americas Orders" at the end, so it looks as:

/shared/Northwind/Americas Orders


Now the added Folder or Analysis path will be shown in the Added Reports or Folder Paths section. You can add multiple folder or Analysis paths to the same Table. Finally, click on the Save button.

Folder or Report path successfully added


Upon saving, you can confirm the Table is successfully added in the next screen, as highlighted below. 

Confirmation of the Table getting saved successfully


Now you're all set to visualize the Oracle Analysis data in Power BI!


The next step is to connect Power BI to the Analysis (on the paths you added to the Table that you created in Step2), and start visualizing the data and automate the reports in Power BI Service/Report Server/Fabric.


Connect Power BI Desktop to Oracle Analysis in Import mode

Connect Power BI Desktop to Oracle Analysis in Direct Query mode