Oracle Power BI report not working on-premise

[don075]

I have created a power bi report that connects to the Oracle database. Report works fine with the power bi desktop version. I have power bi on premise server that will be hosting the report. Also the Oracle database server is also on premise. I published the report and entered the oracle user id and password under data sources and tested the connection successfully. However, when I try to run the report, I get the following error.

An unexpected error occurred. Please try again later.

We couldn't connect to the Analysis services server. Make sure you've entered the connection string correctly.

I am not sure how to fix this. I can connect to the Oracle db from the power bi server without issues with sql plus or odbc. The hosted report doesn't work, even though the connection info under data source is working. Any help is greatly appreciated.

[lbendlin]

Did you use TNSNAMES.ORA in the desktop?  If yes then you need to replicate that setup in all gateway cluster members and then create a connection against that TNS name in the gateway service management page

[don075]

Thanks for the reply. Yes, I used tnsnames.ora file. What I read so far was that if you have on-premise power bi report service and on-prem Oracle DB, you don't need a gateway installed. You need the gateway when connecting to data sources over cloud. Is this incorrect?

Our on-prem is only accessible when the user logs into our network. I have several reports that connects to sql server database and hosted on-pre. I never created a gateway for this and all the reports are working without any issues.

[lbendlin]

What I read so far was that if you have on-premise power bi report service and on-prem Oracle DB, you don't need a gateway installed

It's the other way round.  A gateway is only required for on-premise data sources, and only for service refreshes.

If you always refresh your reports manually in Power BI Desktop then you don't need a gateway.

[don075]

Thanks. Yes, the user manually clicks the refresh button. So I don't need the gateway. 

Any idea about the oracle report error?

[lbendlin]

Report works fine with the power bi desktop version.

What error?

[don075]

When I publish the report to power bi on premise and try to run it, I get the following.

An unexpected error occurred. Please try again later.

We couldn't connect to the Analysis services server. Make sure you've entered the connection string correctly.

You did mention about installing gateway. Since users are manually refreshing it, I don't need it. I am not sure how to fix the above error.

[lbendlin · Accepted solution]

When I publish the report to power bi on premise 

what do you mean by that, Power BI Report Server?

Set up the Oracle connection type with SSRS and a Power BI report server - SQL Server Reporting Serv...

[don075]

Thanks for the response. Much appreciated.
Executed the following from the link you provided (Power BI reports use Unmanaged ODP.NET)
C:\oracle64\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

C:\oracle64\product\18.0.0\client_1\odp.net\bin\4\OraProvCfg.exe /action:config /force /product:odp /frameworkversion:v4.0.30319 /providerpath:C:\oracle64\product\18.0.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll

Now I am getting the following error. Even refreshing report with PowerBI desktop version also gives the same error. Earlier, PowerBI desktop version worked, but the published report didn't work. Now I cannot even connect to the Oracle database by selecting "Oracle Database" in porwer bi desktop.

Container exited unexpectedly with code 0xC0000005. PID: 17164

[lbendlin]

did you check your Oracle versions before doing this?

[don075]

I have an older version of Oracle client. Removed all oracle instances and now trying to "64-bit ODAC OUI" from the link that you provided. My Oracle db version is 19C.

Will update the thread.

[don075]

Thanks for the replies. Much appreciated. Issue resolved.

Steps I followed:

1. Remove all Oracle instances

2. Re-start server

3. Install 64-bit ODAC OUI

4. Configure tnsnames.ora