One of PowerBI’s biggest strengths is the connectivity to almost everything. (They say that through PowerApps it is going to connect to your home espresso machine soon enough), the challenge is when things don’t go as expected.
What is the business need? Data visualization from ORacle data base.
What are the constrains? No cloud is allowed, everything is on premise.
The proposed solution? Use PowerBI to create a local dashboard that I can save and share internally in the company. The data displayed will be from ORACLE database that is local in the company.
Simple enough? well as I have later discovered not that simple…
In order to connect Oracle and PowerBI desktop there is a need in a connection on the Oracle side, otherwise we get the following error:
In the excellent blog post from the PowerBI community, the instructions are:
- Make sure you have 32bit/64 bit matching windows and PowerBI
- Install the ODAC from Oracle site (32bit or 64 bit)
- Go to PowerBI desktop
- Add new data connection, choose data base and Oracle
- Add the servers name and that’s it 🙂
So I have followed, but what happens when it doesn’t work? I got the same error as if nothing was installed to begin with!
It is recommended to install and uninstall all the Oracle client, install diffrent version, make sure you have matching 32/64 bit versions and so on…
I have notice something different, between the connection properties in my Oracle SQL developer tool and the PowerBI data connection window:
So what am I writing in the server name? Nothing seemed to work and worse, I was prompted to use my machine access credentials instead of the DB access credentials. Meaning the connection that PowerBI is trying to establish is to some object on my computer instead to the data base.
The easiest path was discovered in this excellent blog about Excel Power Pivot: PowerPivot Pro
Connecting to Oracle Using TNSnames.ora
The process goes like this:
Creating a file named TNDnames.ora either manually or using the ODAC release 4 which can be found right here for the 64 bit version
Download the Zip file-> unzip-> run the file setup.exe setup as administrator
The setup will ask you to enter the exact same data you have entered in your database connections properties.
- Connection Name (Please do not put space or special characters in there)
- Port (usually 1521)
- Service Name
- User Name
There will be a file created in the following directory:
The files name is: tnsbanes.ora
In the blog post I have mentioned before, contains the full file created.
Mine looked very similar:
(ADDRESS = (PROTOCOL = TCP)(HOST = HostName )(PORT = PortNumber))
(SERVER = DEDICATED)
(SERVICE_NAME = ServiceName)))
After this file is created, either manually or by using the ODAC wizard, it is the equivalent of the connection string. I have entered in the PowerBI get data option the following:
Server = MYCONNECTIONNAME
and used the database credentials in the permissions part.
More tips around creating the connection:
- You do not need to install Visual studio just for the connectivity option. If you are prompted to install VS and the error while installing the connector is:
That means it is not the right connector for the data connection proposes.
2. As recommended in the PowerBI community, clear all the data connections associated with this connection. There might be automatically saved credentials from your previous attempts that will make things not work. In the desktop version go to file-> options and settings-> Data Source Settings; simply delete the not working connections.
Good Luck! and don’t forget to visit the powerBI community: https://community.powerbi.com