Oracle Wallet based connection with Power BI

Steps:

  1. Download and install ODAC OUI from Oracle website . Don’t install the XPath installer. Use the OUI installer. Also, make sure the ODAC version (32/64 bit) matches with your Power BI Desktop version.

2. Register ODP.NET Unmanaged client to GAC by running the following commands in CMD. Make sure you start the CMD in Admin mode.

C:\app\client\user_name\product\18.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:gac /providerpath:C:\oracle64\product\18.0.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll

Path may change depending on the installation location of your ODAC

Reference link from Microsoft

3. Copy all files (tnsnames.ora, sqlnet.ora etc.) of your wallet into the following folder :

C:\app\client\user_name\product\12.2.0\client_1\Network\Admin

Again, watch out for the installation path of ODAC.

4. Open tnsnames.ora file you had in your wallet folder and then find the variable name used inside.

Following snapshot is an example of a variable called ‘db_bi’

5. Now open your Power BI Desktop and create Oracle data connection. Use the variable name from step 4 as server name when you create connection. You will also need to provide user name and password.

We are done!

But you may face some typical Oracle connectivity error from Power BI. I faced a famous one – “Object reference not set to an instance of an object’.

I had to do the following to solve this error (thanks to stackoverflow):

  1. Copy the oraons.dll file in β€˜<>product\12.2.0\client_1’ on my oracle install path
  2. and paste the file into the β€˜<>product\12.2.0\client_1\bin directory

You can try other suggestions like installing latest pbi desktop from microsoft website (not from windows store), fresh ODAC installation etc if copying oraons.dll doesn’t work.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.