Search Results for

    Show / Hide Table of Contents

    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:

    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: Add shared expressions
    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]
    

    Set kind property 4. Next, select the table on which you want to enable incremental refresh 5. Set the EnableRefreshPolicy property on the table to "true": Enable Refresh Policy 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 partititions 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. Configure Properties 7. Save your model (Ctrl+S). 8. Right-click on the table and choose "Apply Refresh Policy". 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.

    Generated Partitions

    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.

    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);
    

    Use scripts to apply refresh policy

    • Improve this Doc
    In This Article
    Back to top Generated by DocFX