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

Modified on Wed, 10 Jan at 8:57 PM

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

BI Connector supports connectivity to Data Models 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 Data Models 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 Data Model 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 pane on the left side, and click on the New Data Source button.

Go to 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 Data Model.

Enter a Data Source Name that's easy for you to remember

Next, in the Oracle Analytics Application field, select the appropriate option based on the info given below.

Select Fusion if you are connecting to OTBI, or Oracle Fusion (ERP / SCM / HCM / CX) Analytics application.

Select Analytics Cloud (OAC) if you are connecting to Oracle Analytics Cloud.

Select OBIEE/OAS if you are connecting to any other Oracle application.

Select the Oracle Analytics Application

Next enter the Server URL in the Oracle Account URL field, and enter your Oracle login credentials in the User Name and Password fields.

Enter your Oracle BI Server URL and Credentials

Next, you need to enter the path of the Data Model Folders (on which the Data Models you want to analyze in Power BI are located). To do this, please open your Oracle Analytics application in the browser, and navigate to the Catalog. Next navigate to the location of the Data Model(s) you want to analyze in Power BI. Then, click on the More option on any of the Data Models, and select Properties. Please refer to the screenshot for clarity.

In Oracle BI, go to the Data Model you want and click on More and Properties

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 Data Model you want to analyze in Power BI, and click on More next to Data Model and then select Properties.

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

Copy the folder path of the data model

Next, in the BI Connector application, please paste the path copied in the previous step on the Folder Path box, and click on Verify & Add button, as highlighted in the image below.

IMPORTANT: The path copied is case-sensitive, and hence must be exactly pasted in the Folder Path. Also, remove the extra trailing and preceding spaces in the pasted path, if any.

Paste the folder path and click Verify and Add

Now the path will be shown in the Added Folder Paths section. You can add multiple folder paths to the same Data Source.

Folder Path successfully added

Now click on Test & Save at the top right.

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.

New Data Source created successfully

Step2: Add Table

Now that you've successfully created a Data Source for your Data Model in the BI Connector application, it's time to create a Table for the Data Model, which you can connect to from Power BI for visualizing.

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

Go to Tables in 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. The select the Data Source you created for the Data Model 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:Select Data Set 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 Data Model 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 details in the Select Data Source screen

Now the folder you added to the Data Source in the previous step will be shown. Please click on the arrow to the left of the folder to expand it and see the list of Data Models under that folder.

Open the folder by clicking the arrow on the left

Navigate to the Data Model you want to analyse in Power BI, and select it, and click Next: Configure Parameters.

Open the appropriate folders and navigate to the folder of your choice

Note: If you don't find the Data Model you want in any of the folders (despite adding its Folder Path to the corresponding Data Source), it should be located in the Others folder.

In the next screen, if the selected Data Model doesn't have any parameters, you can directly click on the Save button.

Also a quick info - At this time, BI Connector supports connectivity to Oracle Data Models with a single Data Set. Hence, Data Models with more than one Data Set are not supported at this time.

If you Data Model does not have any Parameters, you can directly click on the Save button.

If your Data Model has Parameters, you'll be prompted to enter the value for the parameters. Please refer to the screenshot below for detailed instructions on entering parameter values before saving the table. After entering the Parameters, click on the Save button.

Configure Parameters for the Dataset

Now you'll be able to see the table in the BI Connector application, which you can connect to from Power BI and start visualizing your Data Model data.

Table created successfully

The next step is to connect Power BI to the table (from your Oracle Data Model) 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 Data Model