Incremental Refresh in Power BI via OData Feeds
Incremental refresh for OData feeds from CommCare on Power BI allows for efficient updating of datasets by only refreshing new or changed data, minimizing processing time and maximizing data freshness. This feature ensures that Power BI reports remain up-to-date with the latest information from CommCare, optimizing decision-making processes for users. This is incredibly useful for partners using OData feeds with large volumes of data that may take a long time for a full refresh each time the data is synced.
Here are the steps for setting up incremental refresh on Power BI with OData feeds. Follow steps to generate and load OData feeds on Power BI here.
Step 1
Once the credentials are entered and the preview data is visible, click on Transform Data.
Step 2
Navigate to Parameters and click on Add New.
Step 3
Add two new parameters with the following settings:
Parameter 1
Name: RangeStart
Type: Date/Time
Current Value: Start date of data feed (earliest date of data received)
Parameter 2
Name: RangeEnd
Type: Date/Time
Current Value: Last date of data feed (most recent date of data received)
Step 4
Navigate to the date/time column in the Query (or convert one column of data to date/time) and click on Custom Filter.
Step 5
Choose After or equal to and select the RangeStart parameter and choose Equal to or before and select RangeEnd parameter.
Step 6
Save and apply changes to data and navigate to the query table settings by clicking on the three dots. Select Incremental Refresh from the list.
Step 7
Select the data starting before refresh date setting and the Incrementally refresh data starting before refresh date setting as per need.
See here for more information on how to setup these fields.
Step 8
Save the file and publish it to the Power BI service.
Step 9
Navigate to the settings of the Semantic Model saved on the Service and click on Refresh. Setup the incremental refresh frequency here.
Step 10
Hover over the Semantic model and click the Refresh icon. You may be prompted to re-enter credentials to access the OData feed from CommCare. Re-enter your CommCare credentials as in Step 3.
Step 11
The initial refresh on the service can take some time depending on the size of the data being synced. Navigate to the Refresh history link in the Settings of the Semantic model to check the timestamps for starting, ending and total duration of the refresh.
Step 12
The incremental refresh will now run based on the refresh setup done in Step 11. Once a scheduled refresh is completed, navigate to the Refresh history link again to see the difference in time between the initial refresh and the subsequent refresh(es). A marked difference in terms of time will be noticed as the service is only refreshing data incrementally based on the incremental refresh settings. Notice below how the initial refresh took over 41 minutes while the incremental refresh a couple of days later took only 8 minutes for a large data set.
Partners are highly encouraged to read through the Microsoft documentation here on incremental refresh setup and best practices.