Incremental Refresh

• 2021-02-15
• Daniel Otykier

Datasets hosted in the Power BI service can have Incremental Refresh set up on one or more tables. To configure or modify Incremental Refresh on a Power BI dataset, you can either use the XMLA endpoint of the Power BI service directly, or you can use Tabular Editor connected to the XMLA endpoint, as described below:

Important

Setting up Incremental Refresh with Tabular Editor 3 is limited to dataset hosted in the Power BI Datasets service. For Analysis Services custom partitioning is required.

Setting up Incremental Refresh from scratch with Tabular Editor

1. Connect to the Power BI XMLA R/W endpoint of your workspace, and open the dataset on which you want to configure Incremental Refresh.
2. Incremental refresh requires the RangeStart and RangeEnd parameters to be created (more information), so let's start by adding two new Shared Expressions in Tabular Editor:
3. Name them RangeStart and RangeEnd respectively, set their Kind property to "M" and set their expression to the following (the actual date/time value you specify doesn't matter, as it will be set by the PBI service when starting the data refresh):

#datetime(2021, 6, 9, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

4. Next, select the table on which you want to enable incremental refresh 5. Set the EnableRefreshPolicy property on the table to "true": 6. Configure the remaining properties according to the incremental refresh policy you need. Remember to specify an M expression for the SourceExpression property (this is the expression that will be added to partitions created by the incremental refresh policy, which should use the RangeStart and RangeEnd parameters to filter the data in the source). The = operator should only be applied to either RangeStart or RangeEnd, but not both, as data may be duplicated. 7. Save your model (Ctrl+S). 8. Right-click on the table and choose "Apply Refresh Policy".

That's it! At this point, you should see that the Power BI service has automatically generated the partitions on your table, based on the policy you specified.

The next step is to refresh the data in the partitions. You can use the Power BI service for that, or you can refresh the partitions in batches using XMLA/TMSL through SQL Server Management Studio, or even using Tabular Editor's scripting.

Full refresh with incremental refresh policy applied

If you have applied a refresh policy to your table and wish to perform a full refresh, you must ensure that you set applyRefreshPolicy to false in your script. This will ensure that you perform a full refresh of all the partitions in your table. The TMSL Command would in our example look like this:

{
"refresh": {
  "type": "full",
  "applyRefreshPolicy": false
  "objects": [
    {
      "database": "AdventureWorks",
      "table": "Internet Sales"
    }
  ]
}
}

Modifying existing refresh policies

You can also use Tabular Editor to modify existing refresh policies that has been set up using Power BI Desktop. Simply follow step 6-8 above in this case.

Applying refresh policies with EffectiveDate

If you want to generate partitions while overriding the current date (for purposes of generating different rolling window ranges), you can use a small script in Tabular Editor to apply the refresh policy with the EffectiveDate parameter.

With the incremental refresh table selected, run the following script in Tabular Editor's "Advanced Scripting" pane, in place of step 8 above:

var effectiveDate = new DateTime(2020, 1, 1);  // Todo: replace with your effective date
Selected.Table.ApplyRefreshPolicy(effectiveDate);

Removing Incremental Refresh using Tabular Editor

You may need to remove the incremental refresh policy from a table.

1. Select the table in the TOM view and get the M code from the SourceExpression property and save it somewhere.
2. Change the EnableRefreshPolicy value from TRUE to FALSE.
3. Right-click on the table and create a new M partition.
4. Paste in the M code from step 1 above as the partition's expression.
5. Edit the M code to remove the step that contains the Table.SelectRows() function for the RangeStart/RangeEnd parameters.
6. Delete all of the historical partitions. They have a SourceType of "Policy Range".
7. Refresh the table (Tabular Editor 3) or in the service refresh the dataset to repopulate the table.
8. Optionally delete the RangeStart/RangeEnd shared expressions if there are no other tables in the model with an Incremental Refresh policy set.