topic Re: Oracle Power BI report not working on-premise in Report Server

Oracle Power BI report not working on-premise

don075 — Tue, 22 Oct 2024 00:22:46 GMT

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.

Re: Oracle Power BI report not working on-premise

lbendlin — Tue, 22 Oct 2024 14:18:35 GMT

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

Re: Oracle Power BI report not working on-premise

don075 — Tue, 22 Oct 2024 15:43:31 GMT

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.

Re: Oracle Power BI report not working on-premise

lbendlin — Tue, 22 Oct 2024 16:30:08 GMT

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.

Re: Oracle Power BI report not working on-premise

don075 — Tue, 22 Oct 2024 17:09:58 GMT

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

Any idea about the oracle report error?

Re: Oracle Power BI report not working on-premise

lbendlin — Tue, 22 Oct 2024 17:22:35 GMT

Report works fine with the power bi desktop version.

What error?

Re: Oracle Power BI report not working on-premise

don075 — Tue, 22 Oct 2024 17:28:03 GMT

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.

Re: Oracle Power BI report not working on-premise

lbendlin — Tue, 22 Oct 2024 17:33:07 GMT

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 Services (SSRS) | Microsoft Learn

Re: Oracle Power BI report not working on-premise

don075 — Tue, 22 Oct 2024 20:21:42 GMT

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

Re: Oracle Power BI report not working on-premise

lbendlin — Tue, 22 Oct 2024 20:24:04 GMT

did you check your Oracle versions before doing this?

Re: Oracle Power BI report not working on-premise

don075 — Tue, 22 Oct 2024 23:45:52 GMT

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.

Re: Oracle Power BI report not working on-premise

don075 — Wed, 23 Oct 2024 20:31:05 GMT

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