Hello everyone and welcome to my blog. Today’s blog will be on Power BI and I will discuss on the issues while connecting Power BI Desktop to Oracle data source.
There is a fairly good documentation out here from Microsoft on how to connect to Oracle from Power BI. Then why this blog? Because even after following the documentation, when you try to connect and you will face errors which are not documented.
In this blog, I am going to share my personal experience of setting up the connectivity of Oracle from Power BI Desktop. I will walk you through the most common errors you will get and how to resolve them. Please follow everything that is documented here to ensure proper connectivity.
Please note that this post is all about the steps to resolve the issues with connectivity to Oracle from Power BI Desktop. For detailed documentation, first follow this link which has been shared above as well.
You must install the Power BI desktop from Power BI portal and Windows store.
As silly as it may sound, Power BI Desktop from Windows store has some issues while connecting to ORACLE database. Navigate to Power BI service and download and install the Desktop from there.
Make sure you have the correct version of Oracle Data Access Client installed.
This is one more common mistake that we usually make while installing the Oracle Data Access client (ODAC). To connect to Oracle from Power BI, you need to have ODAC installed in your client machine. You can find the related documentation here.
However be careful to install the correct version. First open your Power BI Desktop -> Help -> About and then check whether you are running the 64 bit or 32 bit version of Power BI Desktop.
As you can see in the above screenshot, I am running the 64 bit version of Power BI Desktop. Navigate to this link and download and install the version released in 2018. If you find a newer version, do install it.
When you install the ODAC, make sure to check the box to Configure ODP.NET at machine.config level
Make sure to re-open your Power BI Desktop once your installation is complete.
Resolving Common connection issues with Oracle.
Once all these setup is done, you may still face issues when connecting to Oracle. Below are the few errors you may face.
- Object reference not set to an instance of object
- Oracle: ORA-12154: TNS:could not resolve the connect identifier specified
- Listener was not given the SERVICE_NAME in CONNECT_DATA_1
It’s little bit tricky to resolve these issues and trust me I literally had to scrap through multiple pieces of information to eventually make it work. Collating all of them here.
When you install the Oracle Data Access client (ODAC) as explained in the previous step, it gets installed in the default location – c:\app\client\<user>\product. The path can be different if you have chosen a custom location during install.
Irrespective of where it is installed, navigate to <product>\12.2.xx\client_1. Here xx stands for the version number you installed.
Look for the DLL, oraons.dll in the folder and copy and paste the DLL in the the bin folder in the same directory. Make sure you don’t delete from the source. You just copy and paste
This will take care of object reference error you get while connecting.
Now coming to the last hurdle. Navigate to Get Data -> Other sources -> Database -> Oracle Database. Power BI desktop show the below screen where you need to enter the server name and then followed by database username and password.
When you click connect here, you may face the following errors which I mentioned earlier too.
- Oracle: ORA-12154: TNS:could not resolve the connect identifier specified
- Listener was not given the SERVICE_NAME in CONNECT_DATA_1
Well these errors took me for a ride. After many trial and errors I finally could connect to Oracle. The trick here is to use the following format for your server field.
<Server Name>:<port number>/<service name or SID>SID is the name of the database. If with SID it does not work, try with the Service name
If you are using a local database, or autonomous database connections, you may need to place the server name in quotation marks to avoid connection errors.
For on-premise you will need to use data gateway. Follow the documentation link shared earlier in this post for more information on the same.
In my case I provided like the below screenshot.
In the next step I go ahead and enter the database credentials and voila, I could finally connect.
I have documented the most frequent errors encountered and their resolution. Even after following all these steps, if you still face an error and you were able to resolve, please do let me know. I shall be happy to add them here for the benefit of everyone.
If you liked this post and it has helped you, you can buy me a coffee. Link on the right side pane.
You will also like the below posts
Debajit Dutta
Business Solutions MVP