In this article, we’ll learn how to configure the Incremental Refresh of your Oracle BI Publisher Data Models in Power BI Desktop.
If you intend to create an Incremental Refresh setup directly on Power BI Service, please follow the steps here.
Prerequisites
A Power BI account that’s tied to a Power BI Premium Capacity (or) Premium Per User (or) Power BI Pro license
BI Connector v6.2.8 or above.
The BI Connector Server Edition is installed on your Gateway Server
Note - If you are using a clustered Gateway Setup, then BI Connector must be installed and configured on all the Gateways of the cluster, as Power BI could route the refresh request to any Gateway in the clusterThe user configuring the BI Connector on the Gateway Server must have Admin access to the Server
A Data Model in your Oracle Fusion or Analytics application with at least one dataset (that you want to refresh incrementally in Power BI) having one Date/Time column
A Data Source (for the Data Model that you want to incrementally refresh) to be created in the BI Connector application on the Gateway Server using just step 1 alone in this article.
This feature will help perform Incremental refresh on your Oracle datasets using BI Connector tool to your Power BI desktop and Service.
Step 1: Configure a Table With Incremental Refresh in BI Connector on Gateway Server
Open the BI Connector application on the Gateway Server
Select the Tables tab on the left pane, and click on the Add Table button.
Enter a table name that’s easy for you to identify, and then select the Data Source (that you created as a prerequisite for the Incremental refresh) from the drop-down. Then click on Next : Select Dataset option.
Navigate to the folder paths, and select the data model that you created as a prerequisite, and select the Dataset with the Date/time column , click on Next : Configure Parameters
Under the Configuration Tab, Enable the Incremental Refresh option. Once enabled you will see the below options
Date Time Column: Select the preferred Column from the dropdown Menu.
Format: Select the Date Format.
Fill in the parameter values as per your required data conditions and Click on Save.
Step 2: Configure Incremental Refresh on Power BI Desktop
Load Data into Power BI:
Open Power BI Desktop and connect to the BI Connector data source using these steps.
Import the dataset containing the Date/Time column that will be used for incremental refresh.
Set Up Parameters for Incremental Refresh:
Go to the Transform Data menu.
Go to Manage Parameters and Create two parameters:
RangeStart: Enter the desired start date/time for data refresh.
RangeEnd: Set the end date/time for data refresh.
Filter the Dataset:
Apply a filter on the Date/Time column such that the dates between the RangeStart and RangeEnd parameters are filtered.
This ensures only the data within the defined range is refreshed. Make sure to provide a minimal data range for configuration purposes.
Query Modification
Select the Data Set (Table) and Click on Advanced Editor.
Go to BI Connector Tool and navigate to the Table you want to incrementally refresh, you will see a "Copy Power Query" option under the Actions section. Copy the query.
Remove the auto-generated query by Power BI and paste the new query. Click on Done.
You will see an option to Edit the Credentials and after doing that, Click on Close and Apply option.
Enable Incremental Refresh:
Right-click the dataset and select Incremental Refresh.
In the Incremental Refresh settings:
Specify how much data to archive (e.g., retain 5 years of history).
Define the refresh period (e.g., refresh last 1 year).
Save and Publish the Report:
Save the Power BI file and publish it to the Power BI Service.
Step 3: Create a New Connection in Power BI Service
Log in to Power BI Service
Click on the Gear icon on the top right and click Manage Connections and Gateways
Click on the + New at the top left to create a new connection
Select the exact Gateway or the cluster (on which you configured the BI Connector) from the Gateway Cluster name drop-down field, and give a name to the connection on the Connection name field
Select the Connection type as ODBC
Enter the Data Source name (that you created on the BI Connector app on the Gateway Server as a prerequisite) in the Connection string field, with dsn= prefixed to it.
For example, if you named the Data Source as Oracle Fusion, the following will go into the connection string field:
dsn=Oracle FusionSelect the Authentication Method as Basic, and enter the Oracle credentials in the Username and Password fields.
Click Create.
The connection would be added at this point, provided the credentials and connection string entered are valid.
Step 4: Configure Connection and ScheduledRefresh of Semantic Model in Power BI Service
Navigate to the workspace where you published the report.
Hover over the semantic model (that you published from Power BI Desktop previously)and click on the three dots and select the Settings option.
Under the Gateway and cloud connections section, map the connection (that you created in the previous step) from the drop-down under the appropriate Gateway, and click Apply
Click on the Refresh option, select the appropriate timezone, enable the Refresh toggle, set the time slots to refresh, and set up the refresh failure notification based on your need, and click Apply
After setting up the schedule, check the refresh history to ensure the process runs smoothly.
Best Practices and Troubleshooting
Ensure Accurate Date/Time Column: The Date/Time column must have no missing or invalid values.
Test with a Small Dataset: Validate your incremental refresh setup with a smaller dataset before applying it to a large dataset.
Monitor Performance: Keep track of refresh times and adjust refresh intervals or data ranges as needed.
Following these steps shall help you set up incremental refresh efficiently, combining the power of BI Connector and Power BI's capabilities. If you encounter issues, contact Support or refer to our troubleshooting guide.