Connect Oracle To Power BI: 3 Easy Methods

|

ORACLE TO POWER BI

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.

Table of Contents

Hevo Data: A Convenient Method To Explore Your Data

Hevo is a No-code Data Pipeline. It is a fully managed platform. You can use Hevo to consolidate data from Oracle and other sources such as Facebook Ads, MySQL, MongoDB, etc, to be visualized in Power BI. Hevo also offers pre-built integrations from 100+ data sources at a reasonable price.

Get Started with Hevo for Free

Let’s see some unbeatable features of Hevo:

  1. Simple: Hevo has a simple and intuitive user interface.
  2. Fault-Tolerant: Hevo offers a fault-tolerant architecture. It can automatically detect anomalies and notifies you instantly. If there is any affected record, then it is set aside for correction.
  3. Real-Time: Hevo has a real-time streaming structure, which ensures that your data is always ready for analysis.
  4. Schema Mapping: Hevo will automatically detect schema from your incoming data and maps it to your destination schema. 

All these features make Hevo a must-have option for everyone.

Sign up here for a 14-Day Free Trial!

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’. 

Oracle To Power BI: Get Data

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

Oracle To Power BI: Select Python Script

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)
Oracle To Power BI: Python Script

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

Oracle To Power BI: Load Data

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’.

Oracle To Power BI: 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’. 

Oracle To Power BI: Select Web

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

Oracle To Power BI: Paste the URL

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.

Oracle To Power BI: Load Data

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.

Oracle To Power BI: Select Oracle Database

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

Oracle To Power BI: Server name and data connectivity mode

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

Oracle To Power BI: Load data

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.

Visit our Website to Explore 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.

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.  

Oshi Varma
Freelance Technical Content Writer, Hevo Data

Driven by a problem-solving ethos and guided by analytical thinking, Oshi is a freelance writer who delves into the intricacies of data integration and analysis. He offers meticulously researched content essential for solving problems of businesses in the data industry.

No-code Data Pipeline for Oracle