Incremental Refresh in Power BI via OData Feeds

Owned by Rishabh Rath
Last updated: Aug 29, 2024
4 min read
https://www.loom.com/share/28b01cd0efb14dc69ea349680fa5cfff

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.

1.png

Step 2

Navigate to Parameters and click on Add New.

2.png

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 ‘is after or equal to’ and select the RangeStart parameter and choose ‘is 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.