Connect Oracle To Power BI: 3 Easy Methods

Are you eager to transfer data from Oracle to Power BI? Have you invested several hours on the internet to find the easiest way to do it? If yes, then this blog will answer all your queries. Oracle database is an object-relational database management system that was introduced by Oracle Corporation. Oracle is capable of running different kinds of workloads, such as transaction loads and data warehouse loads. It is very popular in data engineering as it provides a suite of products that manages and monitors each ETL step. Recently, Oracle cloud-based services have also become popular because of their flexibility and reliability.

At times, you need to analyze and visualize your Oracle data to get a better understanding of it. This can be done by using Microsoft Power BI. It is a business analytics tool by Microsoft Corporation. It will help you get an enterprise-level self-service analysis and visualization using the smart tools offered by it. 

This post aims to provide you with a solution to connect the Oracle database to Microsoft Power BI using different methods. By the end of this post, you will be clear with all the available approaches and their merits and demerits.

Method 1: Using Custom Code

Power BI provides multiple data sources such as databases, online services, azure, etc. The data from these sources can be used for visualization using custom scripts.
Let’s see what you will cover here:

1. Prerequisites
2. Steps To Connect Oracle To Power BI Using Custom Code
3. Drawbacks

Prerequisites

• Install python or R on your system.
• Basic knowledge about python or R.

Steps To Connect Oracle To Power BI Using Custom Code

The following steps are used to import data from Oracle using python script:

Step 1: Pip install these libraries in the command prompt:

pip install cx_Oracle
pip install pandas
pip install matplotlib

cx_Oracle library is used to import data from the Oracle database using python.
Pandas is a library used for data manipulation and analysis. The imported data from python should be in a two-dimensional framework. 
Matplotlib is a python library used for plotting. You can directly plot data frames from pandas using this library. 

Also, check for your detected python home directory in Power BI. Select ‘Files’ then ‘Options and Setting’. Click ‘Python Scripting’ and check the ‘Python Home Directory’ that specifies the local python installation path in your system and ‘Python IDE’ that your system will use while executing the python query.

Step 2: In the Home ribbon, select ‘Get Data’. 

Step 3: Select ‘Other’ and then ‘Python script’. You can now click on ‘Connect’.  

Step 4: Write the following code in the python script dialog box and select ‘Ok’.

import cx_Oracle
l=[]
con = cx_Oracle.connect('username/password@localhost/SID’')
cur = con.cursor()
cur.execute('select * from tablename');
for result in cur:
    l.append(result)
df = pd.DataFrame(l,columns=('columnname1’,’columname2',..),dtype=float)
print(df)

Step 5: If your command is correct, this is how your navigation window will appear.

Now, you can select the desired table and load it for visualization. You can also transform the data before loading it. 

Drawbacks

1. You have to install libraries such as pandas, matplotlib, and cx_Oracle. Without these libraries, your code will not run. 
2. Establishing a connection and then consuming the Oracle database by python is a complex and time-consuming process.   
3. You need to have python or R installed on your computer before running your scripts.

Method 2: Using Rest API

Oracle offers Oracle Rest Data Service (ORDS), which removes the gap between HTTPS and Oracle database. It is a java application.
Let’s see what you will cover here:

1. Prerequisites
2. Steps To Connect Oracle To Power BI Using REST API
3. Drawbacks

Prerequisites

• Install the Java Runtime Environment (JRE).
• Install Oracle Rest Data Service (ORDS) on your computer.
• Install SQL Developer on your computer.

Steps To Connect Oracle To Power BI Using REST API

You can transfer your data from Oracle to Power BI using the following steps after successful installation of ORDS on your SQL developer:

Step 1: Right-click on the desired database in SQL Developer. Select ‘REST Services’ and then ‘Enable REST Services’.

Step 2: Write the name of the schema in ‘Specify Objects’ dialog and proceed.

Step 3: Select the table which you want to visualize and analyze and then click ‘none authorized’ and click ‘Ok’ and ‘Finish’.

Step 4: Now, go to your browser and open the URL. The URL format is given below:

http://<HOST>:<PORT>/ords/<SCHEMAALIAS>/OBJECT/<OBJECTALIAS>/

Example: http://localhost:8888/ords/hr/_/sql/

Step 5: You need to copy this URL to use it in Power BI.

Step 6: In Power BI, select ‘Get Data’. Select ‘Other’ and then ‘Web’. Proceed by clicking ‘Connect’. 

Step 7: Paste the REST API URL in the dialog box and select ‘Ok’.

Step 8: The data will be loaded to Power BI. You can see the loaded data on the right side of the application. Now, you can visualize your data as you need.

Drawbacks

1. Working with ORDS requires a lot of skills and effort.
2. Basic knowledge about REST services and java is a must.
3. This process requires a lot of patience and time than any other method. 

Method 3: Using Power BI Connector

You can directly load data from the Oracle database using the connector provided by the Power BI.
Let’s see what you will cover here:

1. Prerequisites
2. Steps To Connect Oracle To Power BI Using Connector
3. Drawbacks

Prerequisites

• Install Oracle Client on your computer.
• Basic SQL knowledge.

Steps To Connect Oracle To Power BI Using Connector

Use the following steps to connect Oracle to Power BI using the connector:

Step 1: Select ‘Get Data’ and then ‘Database’. Click on the ‘Oracle Database’ as shown below.

Step 2: Write the name of the server and then click ‘DirectQuery’. Proceed by clicking ‘Ok’. 

Step 3: In the navigation panel, select your desired table and then click ‘Load’. You can also transform your data before loading.

Now your data will be present on the right side of the screen.

Drawbacks

1. This process requires the installation of Oracle Client beforehand.
2. If Oracle Client is not installed, you have to go through the ODBC Data Source configuration, which requires immense time and energy.
3. If you are not aware of the server name of your database, then you will have to go through the configuration files of your Oracle database.

Conclusion

You have learned about various methods to connect Oracle to Power BI. All these methods have their merits and demerits. If you want to load your data using your script, then method 1 is a good option. If your data is already on a REST API, then method 2 is a quick option. But if you want to consolidate data from various platforms before visualizing it in Power BI and avoid the hassle involved in it, then you should try Hevo.

Hevo is a No-code Data Pipeline. We can use Hevo to combine data from Oracle along with other sources to be visualized in the Power BI. This way you invest minimal time in collecting data and more time on analyzing and visualizing your data for better results.

FAQ on Oracle to Power BI

Give Hevo a try and Sign Up for a 14-day free trial today.

Let us know about your experience to connecting Oracle to Power BI in the comment section below.