Connect to Oracle Database from Power BI Desktop

Nov 5, 2019 written by Juan León

Within the various connectors available in Power BI Desktop, in this article you will access Oracle Database. If you want to know a little more about Microsoft Power BI please check our article Some Stuff about Microsoft Power BI.

Previous requirements

To be able to connect to the Oracle database, it is necessary:

• Install the software "Oracle Client" for Windows according to the version of the database engine 11gr2, 12cr1, 12cr2, 18c or 19c. It is recommended to use the "Oracle Client" of the version of the database engine installed from the server where it will be accessed (if you have the database engine installed locally, it is not necessary to install the "Oracle Client").
• Define in the configuration file for the database from the local computer (tnsnames.ora). If you have already been working on that database, the connection is probably already configured.
• Have an username and password to access the database.
• Have the necessary permissions on the tables or views that you will access.
• Have the latest version of Power BI Desktop installed.

How to connect to Oracle

Open the Power BI Desktop application, from the icon on your desktop.

The Power BI Desktop screen appears.

From the Power BI Desktop screen, search the "Get Data" icon from Home Menu, left click with the mouse on the icon as shown in the following image.

The pop-up window for "Get Data" appears, select from the list on the left side the option that says "Databases", immediately change the list on the right side and choose "Oracle Database" as shown in the image:

Click on the "Connect" button at the bottom right of the screen that is highlighted with a yellow background, then the window to place the information of the database you wish to access appears.

Where it says "Server" put the name of the server along with the name of the database. In my case, I will use LOCALHOST and ORCL, it is written as shown below, separated by a "/".

Click on the "OK" button, another pop-up window appears. Select from the left side the text that says "Database", you can see on the screen that a user and password is requested, click on the "Connect" button

If the username and password are correct, the "Browser" screen appears, listing all the schemes to which you have access.

Select the scheme where your information is located, mark the desired tables or views one by one, when finished click on the "Load" button.

The window that shows how the data is being loaded into Power BI Desktop appears.

Once the information is loaded it is displayed in the "Fields" bar on the right side of Power BI Desktop and so you can start working with the data. And ready!!!

Manage the connection to Oracle

After loading the data successfully if you need to change the user, modify the connection or simply delete the connection data, select from the top menu "File", the "Options and Configuration" item then "Configure data sources". The data configuration screen appears with an entry from the connection to the Oracle database. Here you can edit or delete the connection.