Get data in Power BI from Oracle Database

Get data in Power BI from Oracle Database

Pablo Gómez

SolidQ Data Platform Specialist. Training, learning and working at SolidQ since 2016.

Latest posts by Pablo Gómez (see all)

Microsoft’s Business Analytics Service Power BI, enables us to connect to hundreds of data sources and produce beautiful reports that can be consumed on the web and across mobile devices, in order to deliver insights throughout our entire organization. In this post, I will walk you through the process to get data in Power BI from Oracle Database.

When you open Power BI Desktop, you will see the following window:

PowerBI welcome page

By clicking on Get Data (located at the top left of the window), the following window will appear. As we want to obtain the data from an Oracle Database, the only thing we have to do is to mark that Oracle Database option and click on the Connect button.

Get Data page

After click on Connect button, the following window will appear.

Connect to oracle database

In the Server box, we will write the exactly the following text:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)))
These instructions are written in the tnsnames.ora file, which is located in: C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
Note: if you do not have exactly that code, change it to get it in the same way!

Connect to Oracle database (2)

Once we have copied and pasted the text before, we have to ensure that the Import option is selected, because we want to import directly the data from an Oracle database. Once we have all this right, we click on OK button.

Oracle Database XE

After that, the following window will appear. Here we have to write our credentials. These credentials correspond to the user we created in the Oracle Database Express Edition 11g program:

Oracle Database XE login

These are the data we introduced in:
– Database Username: EXAMPLE_USER
– Application Express Username: TEST
– Password: test
– Confirm Password: test

Oracle db in PowerBI

So here, in our case, we have to introduce the following credentials:
– Username: EXAMPLE_USER
– Password: test

Navigate Oracle database

Once you click on the Connect button, you will arrive at the following window. Here you can see the objects you have in your Oracle database:

Oracle databases

If you click on your schema (in our case, EXAMPLE_USER), we will see the same databases we have in Oracle.
Databases in PowerBI:

In Oracle App Express

Oracle Databases:

Load tables in PowerBI

If we click on some of them, we will able to load them in Power BI Desktop, as follows:

Loading

After a few minutes of loading, the tables selected will appear.

Tables loaded in PowerBI

Now, in the right part of the screen, we can see the loaded tables with all their data.