Need help with PowerBI Paginated reports in the cloud connected to cloud-based Oracle data sources

[jbenfield]

We have PBI Paginated reports that we need to publish in the cloud.

The data source is a cloud-based Oracle DB on a private subnet. 

We have the VNet setup (via Fabric Capacity) and PBI dashboards connect and work just fine, but not paginated reports.

The Paginated Reports work fine in a VM, but we can't expose the VM because of company policy.

When we put the Paginated reports into a cloud workspace, they can't connect to the Oracle DB. 

Is there something different about how the Paginated Reports connect and are we missing a step? 

For SSIS, I had to install ODAC into the cloud instance, but I have no clue if that's needed in this situation or how I would go about doing that for PBI-RB in the cloud. I just don't know enough about PBI and neither does my IT team.  

Any assistance would be appreciated. 

[rajendraongole1 · Accepted solution]

Hi @jbenfield - Point1: For Paginated Reports, a Data Gateway is usually required to access on-premises or private cloud data sources. Since you have an Oracle Database on a private subnet, ensure the following:

• Install the On-Premises Data Gateway: Set up a gateway in the same VNet that your Oracle Database is located in.
• Gateway Configuration: Make sure the gateway is configured with the appropriate network and security settings to connect to the Oracle Database within the VNet.
• Connect Power BI Service to the Gateway: After installing and configuring the gateway, link the gateway to your Power BI workspace in the Power BI Service. In the workspace settings, confirm that the gateway is associated with your Paginated Report.

Point 2. Ensure Oracle Client Software Installation

Power BI requires the Oracle Data Access Components (ODAC) for Oracle connections. To enable the gateway to communicate with Oracle:

Install the Oracle Client (either the full Oracle Client or the Instant Client) on the gateway machine. This is necessary for the Data Gateway to access the Oracle database.

Ensure the version of ODAC or Oracle client installed is compatible with the Oracle Database version in the cloud.

Point 3. Paginated Reports and Data Gateway Compatibility

Unlike standard Power BI reports, Paginated Reports often require the gateway to be configured specifically for paginated connections:

          Go to the Manage Gateways section in Power BI Service and add the Oracle Data Source under your gateway with the same credentials and configuration as used in the VM.

          Confirm that the Connection Type is set to Oracle and that you are using the correct credentials.

Point 4. Use Private Endpoints for Secure Access

If your Oracle Database supports Private Endpoints or Private Link, use them to securely connect the Oracle Database to your Power BI Service in Azure without exposing it publicly. This approach secures the traffic between Power BI and Oracle.

Point 5. Verify Network and Firewall Settings

• Check that any firewalls or network security groups (NSGs) in the VNet allow traffic from the Data Gateway to the Oracle Database.
• Ensure the gateway’s IP range or hostname is whitelisted in the Oracle Database’s security settings.

Point 6. Use Fabric Capacity for Paginated Reports

Since you mentioned using Fabric Capacity:

• Confirm that your workspace is enabled for Fabric Premium or PPU (Power BI Premium Per User), which is required for Paginated Reports.
• Under Fabric Capacity, confirm that the VNet configuration for data connectivity is fully set up to allow paginated report access.

[SaiTejaTalasila]

Hi @jbenfield ,

Please refer this article -

https://learn.microsoft.com/en-us/power-bi/paginated-reports/report-data/oracle-connection-type

I hope it will be helpful.

Thanks,

Sai Teja 

[rajendraongole1 · Accepted solution]

Hi @jbenfield - Point1: For Paginated Reports, a Data Gateway is usually required to access on-premises or private cloud data sources. Since you have an Oracle Database on a private subnet, ensure the following:

• Install the On-Premises Data Gateway: Set up a gateway in the same VNet that your Oracle Database is located in.
• Gateway Configuration: Make sure the gateway is configured with the appropriate network and security settings to connect to the Oracle Database within the VNet.
• Connect Power BI Service to the Gateway: After installing and configuring the gateway, link the gateway to your Power BI workspace in the Power BI Service. In the workspace settings, confirm that the gateway is associated with your Paginated Report.

Point 2. Ensure Oracle Client Software Installation

Power BI requires the Oracle Data Access Components (ODAC) for Oracle connections. To enable the gateway to communicate with Oracle:

Install the Oracle Client (either the full Oracle Client or the Instant Client) on the gateway machine. This is necessary for the Data Gateway to access the Oracle database.

Ensure the version of ODAC or Oracle client installed is compatible with the Oracle Database version in the cloud.

Point 3. Paginated Reports and Data Gateway Compatibility

Unlike standard Power BI reports, Paginated Reports often require the gateway to be configured specifically for paginated connections:

          Go to the Manage Gateways section in Power BI Service and add the Oracle Data Source under your gateway with the same credentials and configuration as used in the VM.

          Confirm that the Connection Type is set to Oracle and that you are using the correct credentials.

Point 4. Use Private Endpoints for Secure Access

If your Oracle Database supports Private Endpoints or Private Link, use them to securely connect the Oracle Database to your Power BI Service in Azure without exposing it publicly. This approach secures the traffic between Power BI and Oracle.

Point 5. Verify Network and Firewall Settings

• Check that any firewalls or network security groups (NSGs) in the VNet allow traffic from the Data Gateway to the Oracle Database.
• Ensure the gateway’s IP range or hostname is whitelisted in the Oracle Database’s security settings.

Point 6. Use Fabric Capacity for Paginated Reports

Since you mentioned using Fabric Capacity:

• Confirm that your workspace is enabled for Fabric Premium or PPU (Power BI Premium Per User), which is required for Paginated Reports.
• Under Fabric Capacity, confirm that the VNet configuration for data connectivity is fully set up to allow paginated report access.

[jbenfield]

Thank your for your kind and detailed response. 

I'm a bit surprised that we need to build an on-prem gateway, but if that's what's required, I guess we're stuck with it. It's certainly less hassle than trying to replicate all of the data into a natively supported DB. 

[rajendraongole1 · Accepted solution]

Hi @jbenfield - You're very welcome! And yes, it can indeed be surprising that an on-premises data gateway is still often necessary, especially for hybrid cloud setups. Paginated reports in Power BI, which are rooted in the SSRS (SQL Server Reporting Services) architecture, typically require an on-premises gateway for secure connectivity to on-prem or private network data sources. The on-premises data gateway acts as a bridge between your Power BI service and the private Oracle database, handling the secure data transfers.

While it may feel like an extra layer of setup, it’s often the most secure and feasible option, particularly when the source is a non-Microsoft database like Oracle on a private subnet. Plus, using the gateway keeps your data secure without requiring a full data replication, which would indeed add complexity and costs.

If your organization’s setup evolves and Power BI begins supporting direct cloud-to-private connections for paginated reports without a gateway, you might be able to simplify things later on. For now, though, the on-premises data gateway remains the go-to solution for this kind of connectivity.

Hope this information helps you