- 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
3. Copy all files (tnsnames.ora, sqlnet.ora etc.) of your wallet into the following folder :
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):
- Copy the oraons.dll file in ‘<>product\12.2.0\client_1’ on my oracle install path
- 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.