Simplified Oracle connectivity in Power BI (Preview)

Connecting Power BI to Oracle has historically meant extra provider installations and data gateway deployment — even for cloud-hosted databases. Two new Preview capabilities change that. Power BI Desktop now includes a bundled Oracle Managed ODP.NET provider, and the Power BI service supports direct cloud connections without the need for a data gateway to Oracle data sources such as Oracle Autonomous Database.

How it works in Power BI Desktop

Power BI Desktop ships with the Oracle Managed ODP.NET provider built in. To turn it on, go to File > Options and settings > Options > Preview features and enable both switches:

• Enable using Bundled Oracle Managed ODP Provider for Import Mode
• Enable using Bundled Oracle Managed ODP Provider for DirectQuery Mode

Figure: Enabling Oracle preview features.

 

Restart Power BI Desktop, then connect to your Oracle database through the existing Oracle database connector. Both Import and DirectQuery work without any additional client installation.

How it works in the Power BI service

From the Power BI service, you can now create a cloud connection to Oracle Autonomous Database or other Oracle cloud endpoints without a gateway. To create the connection:

1. Go to Settings > Manage connections and gateways.
   Select New connection, then select Cloud.
2. Set the connection type to Oracle.
   Enter the Oracle cloud server name. For Oracle Autonomous Database, use the endpoint URL that ends with .oraclecloud.com.
3. Provide the database name and authentication credentials, then select Create.
4. Bind your semantic models in Import or DirectQuery mode to the new cloud connection.

Figure: Configuring a cloud connection to Oracle.

 

If you prefer the unmanaged provider or haven't enabled the preview switches, Oracle Client for Microsoft Tools (OCMT) remains available as an alternative. Both Previews support Oracle Database 12c (12.1.0.2) and later, plus all versions of Oracle Autonomous Database.

Personal data gateways

If your semantic models are still using a personal gateway to connect to on-premises Oracle data sources, upgrade to an enterprise on-premises data gateway or virtual network (VNET) gateway, or shift your cloud Oracle workloads to direct cloud connections. The updated Oracle connector does not support personal gateways.

 

Existing semantic models can continue to use a personal gateway until you update the data connection binding. After you update the binding, you can’t go back to the personal gateway. Plan your Oracle connection upgrades accordingly.

The enterprise data gateway provides the supported path for on-premises Oracle databases and for any scenario that needs single sign-on. Install 64-bit OCMT on the gateway machine, since the gateway is a 64-bit app.

Enable the bundled provider on the on-premises data gateway

By default, the on-premises data gateway uses the unmanaged Oracle provider for Import mode. To switch the gateway to the bundled Oracle Managed ODP.NET provider, update a configuration value on the gateway machine:

1. On the computer running the on-premises data gateway, go to C:\Program Files\On-premises data gateway.
2. Create a backup of the configuration file: Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config.
3. Open the original Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file in notepad and locate the MashupFlight_EnableOracleBundledOdacProviderV2 entry.
4. Set the MashupFlight_EnableOracleBundledOdacProviderV2 value to True, as in this example:

   <setting name="MashupFlight_EnableOracleBundledOdacProviderV2" serializeAs="String">

      <value>True</value>

   </setting>

5. Restart the gateway.

The VNET data gateway does not require additional configuration steps. Oracle connections on a VNET gateway automatically use the bundled Oracle Managed ODP.NET provider.

Single sign-on (SSO)

Power BI supports Microsoft Entra ID tokens for Oracle Database 19c Release 20 or later, Oracle 23ai, and Oracle Autonomous Database, with Power BI Desktop June 2024 or later. Entra ID single sign-on is currently supported for DirectQuery and Import through an enterprise on-premises data gateway. Direct cloud connections don't support single sign-on yet.

Get started

• Update to the latest Power BI Desktop version to get the bundled provider and enable both preview switches under File > Options and settings > Options > Preview features, then restart Power BI Desktop.
• For direct cloud connections, create the cloud connection in Manage connections and gateways in the Power BI service.

Conclusion

The bundled Oracle provider and direct cloud connections cut Oracle setup down to a few toggles and a connection dialog. Try both Previews for new semantic models and keep OCMT or the enterprise gateway for on-premises and single sign-on scenarios.