Oracle.DataAccess.Client - direct query error

[Raptor]

Hi MS Power BI gurus,

first of all I want to state my versions I am using for the upcoming scenario / question / problem:

MS Power BI Desktop: 2.71.5523.941 64-bit (Juli 2019)

Application OS: Windows Server 2012 RS Standard

Database Version: Oracle 12c Enterprise Edition

Since the latest update (2.71.5523.941 64-bit (Juli 2019)) of MS Power BI desktop, I am not able to use the functionality of direct query of data which is stored within an Oracle 12c database.

When selecting a target database table I receive the following error message:

Cannot load model

We couldn't connect to your DirectQuery data source or internal model. Double-check that your server and database names are correct, and that you have permission to access them.

The managed provider 'Oracle.DataAccess.Client' could not be instantiated. Unable to find the requested .Net Framework Data Provider. It may not be installed.

The database is accessable and I also have the appropriate grands since the option of "import" works without any problems. Nevertheless I want to use "direct query" (in combination with data gateway) as I do not want to store all the data of our business model within the .pbix-file.

For connection we are having an Oracle 12c-client (12.2.0.1.0) installed on our application / development server which is also running MS Power BI desktop. The connection information to the database is stored within the tnsnames.ora-file.

I also tried to install an ODAC-driver (x64 and x86) and followed this thread: https://community.oracle.com/thread/2571139?start=0&tstart=0

Does anyone have a suggestion for me?

Best regards and thanks in advance

Carsten

[Anonymous]

Hello guys,

Unfortunately I'm facing the same issue and downgrading to ODAC 12.1.0 did not solve it.

However, when I open and refresh my test report on PBI Desktop installed on my server, it works well.

The problem is only occuring when trying to refresh a report on the RS web page.

MS Power BI Report Server: 1.6.7206.42072 (Septembre 2019)

Application OS: Windows Server 2016

Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

[ChrisRenlund]

Guess you solved this, but for anyone else facing the issue - this is what solved it for us:

• Install Oracle Client 12.1 (64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64) as an additional Oracle home on your gateway server (we were running 12.2)
• Restart you On-premises data gateway (it did not work if you didn't restart the gateway)

[ChrisRenlund]

NOTE: installing the second Oracle Home made some (not all) of our import dataset refreshes fail. Had to uninstall it to get the more important dataset refreshes working again - so now we're back where we started with our direct query that doesn't work.

[pawlowski6132]

Wish we had a solution for this. Same issue here. I had heard that running this internally on Report Server was a fix but, reading through this entire thread indicates it might now. I don't want to muck around with the ODAC becasue it could hose something else up.

[v-shex-msft]

Hi @Raptor ,

According to your description and error messages, I think this issue should more related to compatibility between power bi desktop latest version and oracle client.

I'd like to suggest you open support ticket one power bi/oracle support page.

submit a support ticket

Regards,

Xiaoxin Sheng

[Raptor · Accepted solution]

I finally got a solution:

I installed an older version of 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64 and it worked as expected:

https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

[ShaoyuHung]

對我有幫助，12.2.0.1.0 - > 12.1.0.2.4 就可正常執行

[Anonymous]

Hello @Raptor 

Do you uninstall first version 12.2?

Do you have to configure again your odbc credentials?

Thank you.

[Raptor]

@Anonymous: I did not have to uninstall the 12.2 Oracle client. I just created a second Oracle-Home on my computer. There was also no need for re-configuration of odbc-credentials since I am using tnsnames.ora for specification of the target databases.

[asad]

Did you figure it out yet? I am facing the same issue and am unable to resolve this.

[Anonymous]

I had the same problem and fixed it with your suggestion. I was using ODAC version 12.2 and I downgraded to version 12.1. Thanks!

[Anonymous]

Quick follow up question on this, as I've had the same problem for a long time...

Is your Power BI installed from the Windows Store or from the desktop download link?

I've found that the Store version doesn't work nicely with Oracle Clients - but if your older Oracle Client version works OK then I may have to revert to that client as it drives me mad having to manually download Power BI regularly rather than let the Store manage it for me! 

[Raptor]

@Anonymous :

I have installed it from the following link: https://www.microsoft.com/en-us/download/details.aspx?id=58494