Overview

This guide is designed for Oracle Fusion users who:

• Have published Power BI Desktop reports (connected to Oracle Fusion data) to Power BI Service.

• Want to automate the refresh of these reports and datasets in Power BI Service.

Compatibility

Supported Query Modes

• Import Mode: Supported with scheduled refresh in Power BI Service.

• Direct Query Mode: Supported for OTBI Analysis reports, but not for Data Models.

  • If using Direct Query mode, complete all steps except "Automate Dataset and Report Refresh."

Prerequisites

Required Setup

1. Windows Server hosting Power BI Gateway and BI Connector
   
   

   • Install and configure Power BI On-premises Gateway.

   • Install BI Connector Server edition and activate it using a license key.

2. Admin Access to Gateway Server
   
   

   • Admin access is required to set up System DSNs in ODBC.

Recommended Configurations

• Gateway Server: Minimum of 8 CPU cores.

• On-Premises Gateway (preferred): Ensures 24/7 availability and allows use by multiple users.

• Gateway Clusters: If using a cluster with multiple Gateways, ensure all prerequisites are met on each Gateway.

Note: You can also use a VM hosted in the cloud as the Gateway Server.

Downloads

Note: If you don’t have a BI Connector license key, request a free trial here.

Step-by-Step Process

1. Create BI Connector Data Source on Gateway Server

1. Log in to the Gateway Server as an Admin user.

2. Open BI Connector (“Run as Administrator”).

3. Navigate to the Data Sources pane and click New Data Source.

4. Configure the Data Source:

   • Name: Match the name used in Power BI Desktop.
     Ex: Oracle Fusion ERP Prod

   • Data Source Type and Oracle URL Fields: Select the same values as you did while creating the report in Power BI Desktop.

   • Credentials: Enter Oracle username and password.

   • Folder Path: Retrieve from Oracle by navigating in a browser to the desired data model/analysis, selecting Properties, and copying the Location field. Paste the content and click Verify & Add 

5. Click Test & Save to confirm the setup.

2. Create a Connection in Power BI Service

1. Open Power BI Service and log in.

2. Click the Gear icon at the top right and select Manage Connections and Gateways.

3. Add a new connection:

   • Gateway Cluster: Select the configured Gateway.

   • Connection Name: Provide an identifiable name.

   • Connection Type: BI Connector.

   • ODBC DSN: Copy and paste the Data Source name from BI Connector app.

   • Authentication: Basic (enter Oracle credentials).

   • Privacy Level: Choose based on preferences.

4. Click Create.

5. Navigate to Workspaces and locate the published dataset.

6. Click on the three dots to the side of the published dataset. and select Settings > Gateway and Cloud connections and assign the created connection.

Note: For Direct Query mode, your automation setup ends here. For Import mode, proceed to the next section.

3. Automate Dataset and Report Refresh

1. Navigate to Workspaces and locate the published dataset.

2. Click on the three dots to the side of the published dataset. and select Settings.

3. Under Refresh:

   • Set Timezone.

   • Configure the schedule based on requirements.

   • Add users to notify in case of failures.

4. Click Apply.

Verification: Run an on-demand refresh from the dataset’s workspace. If successful, the connection is valid.

Best Practices

• Clustered Gateways: Avoid using clusters with multiple Gateways for simplicity.

• 24/7 Availability: Use On-premises Gateway on a server for reliable automation.

• Testing Connections: Always verify the setup with an on-demand refresh.

  ---

For additional support, refer to:

• Microsoft Documentation on Gateway Setup.