How to configure Incremental Refresh of Your Oracle Fusion and BI Datasets on Power BI Desktop using BI Connector?

Pre-Requisites 

This feature will help perform Incremental refresh on your Oracle datasets using BI Connector tool to your Power BI desktop. 

Step 1: Configure Incremental Refresh in BI Connector

1. Enable Incremental Refresh in BI Connector:

   • Open the BI Connector Tool and create a Datasource. 
   • Navigate to Tables option and click on create new table. Enter the table name and select the data source. Then click on Next : Select Data Set option.
   • Your Data Set must have Date/Time column for the Incremental Refresh option to work. Once selected the desired data set, click on Next : Configuration.
   • Under the Configuration Tab, Enable the Incremental Refreshoption. 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 data conditions and Click on Save.  

Step 2: Configure Incremental Refresh on Power BI Desktop

1. 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.

2. 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. 

3. Filter the Dataset:

   • Apply a filter on the Date/Time column using the RangeStart and RangeEnd parameters.
   • This ensures only the data within the defined range is refreshed.

4. Query Modification

   • Select the Data Set (Table) and Click on Advanced Editor. 
   • Go to BI Connector Tool and navigate to Tables, you will see a "Copy Power Query" option under Actions section of your table. 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.

5. 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).

6. Save and Publish the Report:

   • Save the Power BI file and publish it to the Power BI Service.

Step 3: Configure Incremental Refresh on Power BI Service

1. Access the Dataset in Power BI Service:

   • Navigate to the workspace where you published the report.
   • Locate the dataset and click on the settings icon.

2. Set Up Scheduled Refresh:

   • Enable the scheduled refresh option.
   • Configure the refresh frequency (e.g., daily, weekly).
   • Use your BI Connector credentials for authentication.

3. Verify the Refresh Status:

   • 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.