Incremental Refresh in Power BI for ADO Analytics Views
Ensuring that work item reports remain useful requires a performant refresh action to keep the report up-to-date with the latest work item values. Configuring the incremental refresh policy on the analytics view in Azure DevOps will ensure the dataset is refreshed quickly and the latest work item values are ready for reporting. If it is not enabled, the refresh process brings back ALL the work items and this can dramatically increase the refresh time This blog post covers the steps to configure the incremental refresh for an analytics view for current work items. There is a different process for configuring incremental refresh for an analytic view based on historical work items.
Prologue:
Analytics Views can potentially return a large number of work items if filtering is not configured correctly. To avoid this, establish a filter on the analytics view to limit the result set. The filter can be to return only open work items, or with a specific set of work item types. This filter should be tailored to the type of report you are creating. By applying filters, you can focus on relevant data and prevent unnecessary overhead during refresh.
Review this Power BI help file to understand the incremental refresh process in the Power BI service. This Power BI help file describes how to setup incremental refresh on the dataset using parameters. The steps outlined in the aforementioned help files are adapted to use with analytic views in Azure DevOps.
NOTE: Incremental refresh is supported for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded model.
The Steps to Configure Incremental Refresh
Add the Analytics Updated Date field to the Analytics View
This process uses the AnalyticsUpdatedDate field to detect changes in the work item. When a work item is added or updated in Azure DevOps, the AnalyticsUpdatedDate field is updated. The incremental refresh process uses this date to detect the changes to the work item. This captures new and changed work items.
NOTE: This process is for the analytics views configured to use the Current Only option. This is not for the historical work items. The historical work items require a different process for detecting changes and will be in a future blog post.
If your analytics view does not have the AnalyticsUpdatedDate field, review this blog post on how to add a field to the analytics view and update the Power BI data model.
Save the analytic view and refresh the data model in Power BI Desktop.
Power BI Desktop Configuration Changes
In Power BI desktop, select the Transform Data button on the menu bar. This opens the Power Query editor.
Change the data type for the AnalyticsUpdatedDate field from Time Zone to Date/Time.
Incremental Refresh Parameters
The incremental refresh process requires two parameters in the Power BI data model.
Select Manage Parameters -> New Parameter.
Create the RangeStart parameter with a date value in the past. This is used on the initial refresh of the data in Power BI desktop. The value is overwritten based on the number of years (or months) selected in the incremental refresh policy when the dataset is refreshed in the Power BI service. To optimize performance, keep the RangeStart value to only a few months back. This helps minimize the initial load of data in Power BI Desktop.
Create the RangeEnd parameter with a date value in the future. Again, this value is overwritten after the incremental refresh policy is enabled in Power BI Service.
Filter the AnalyticsUpdatedDate Column
You’ll now apply a filter based on conditions in the RangeStart and RangeEnd parameters.
In Power Query Editor, select the AnalyticsUpdatedDate column, and then choose the dropdown arrow > Date Filters > Custom Filter.
In Filter Rows, to specify the first condition, select is after or equal to, then choose Parameter, and then choose RangeStart. Select the drop down in the yellow box to see the parameters.
The second option in the filter requires the is before or equal to option. Choose Parameter, and then choose RangeEnd.
Select OK to close the dialog.
Select Close and Apply.
Define the Incremental Refresh Policy
After you’ve defined RangeStart and RangeEnd parameters, and filtered data based on those parameters, you’ll define an incremental refresh policy. This policy is applied only after the model is published to the service, and a manual or scheduled refresh operation is performed.
The incremental refresh policy is defined per analytics view. If your report has multiple analytics views, then each one will have its own refresh policy established.
Select the table with the analytics view data in the Data view, right-click a table in the Data pane and select Incremental refresh.
Specify required settings:
If the table with the analytics view data is not selected in the 1. Select Table section, then select it from the drop down.
In the 2. Set import and refresh ranges section, move the slider to On. If the slider is disabled, it means the Power Query expression for the table doesn’t include a filter based on the RangeStart and RangeEnd parameters.
In Archive data starting, specify the historical store period you want to include in the model. All rows with dates in this period will be loaded into the model in the service, unless other filters apply.
In Incrementally refresh data starting, specify the refresh period. All rows with dates in this period will be refreshed in the model each time a manual or scheduled refresh operation is performed by the Power BI service.
Keep the amount of data to load from the analytics view in perspective. You probably don’t need more than a year or two of data on the current work items. If you need more, then explore creating aggregation ODATA queries to summarize the data.
NOTE: The yellow banner will appear indicating the M query cannot be folded. This is to be expected and can be disregarded.
Publish Report
Now that your RangeStart and RangeEnd parameters, filtering, and refresh policy settings are complete, save your report, and publish to the Power BI service.
Schedule the Power BI Data Model Refresh
Open a browser and navigate to the Power BI service and select the published semantic model. Go into the semantic model’s settings and add the times during the day to refresh the model that makes sense for your business needs.
Manual Refresh in the Power BI Service
In the service, refresh the model. The first refresh will load both new and updated data in the refresh period. Depending on the amount of data, this refresh can take a long time. Subsequent refreshes, whether manual or scheduled, are typically much faster because the incremental refresh policy is applied and only data for the period specified in the refresh policy setting is refreshed.
Verify Refresh
Verify the data refresh times after the manual refresh AND the scheduled refresh. The first refresh of the dataset, whether manual or scheduled, will take the longest. This is a complete overhaul of the data in the dataset. The second refresh will be the incremental and is faster.
For instance, this the first refresh of the dataset after publishing:
Here is the second refresh:
The initial load of the dataset is for tens of thousand work items from the analytics view. The second was only a few thousand. The refresh included added and updated work items.
You have now configured the dataset for incremental refreshes!