What is a Refresh Policy?

2023-01-09
Kurt Buhler

Applies to:

❌ ~~Desktop Edition~~
✔ Business Edition
✔ Enterprise Edition

Incremental Refresh Visual Abstract

Datasets hosted in the Power BI service can have Incremental Refresh configured for one or more data tables. The purpose of Incremental Refresh is to achieve faster, more efficient refreshes by only retrieving recent/changing data, incrementally refreshing the table. To do this, the table is automatically divided into partitions, such that only recent or changing data is refreshed ("hot" partitions) or even retrieved in real-time ("Direct Query" partitions in "Hybrid Tables") while older, static data is archived ("cold" partitions).

Incremental refresh can be easily configured and modified from within Tabular Editor.

Note

Configuring incremental refresh can be beneficial for your data model:

Reduce refresh time & resource consumption
Experience shorter and more dependable scheduled refreshes

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.

How does it work?

To create the partitions, Power BI uses the RangeStart and RangeEnd datetime parameters in Power Query. These parameters are used in a filter step of the table partition M Expression, filtering a table datetime column. Columns that are of date, string or integer types can still be filtered while maintaining query folding using functions that convert RangeStart, RangeEnd or the date column to the appropriate data type. For more information about this, see here

An example is given below. Incremental Refresh is applied to a table 'Orders' upon the [Order Date] column:

// The filter step should ideally be able to fold back to the data source
// No steps before this should break query folding
#"Incremental Refresh Filter Step" = 
    Table.SelectRows(
        Navigation,
        each 
            [OrderDate] >= #"RangeStart" and 
            [OrderDate] < #"RangeEnd"
    )

let
    // The data source should ideally support Query Folding
    Source = Sql.Database(#"ServerParameter", #"DatabaseParameter"),

    Navigation = 
        Source{ 
            [ Schema="DW_fact", Item="Internet Sales" ] 
        } [Data],

    // The filter step should ideally be able to fold back to the data source
    // No steps before this should break query folding
    #"Incremental Refresh Filter Step" = 
        Table.SelectRows(
            Navigation,
            each 
                [OrderDate] >= #"RangeStart" and 
                [OrderDate] < #"RangeEnd"
        )
in
    #"Incremental Refresh Filter Step"

// It does not matter what the initial value is for the RangeStart parameter
// The parameter must be of data type "datetime"
#datetime(2022, 12, 01, 0, 0, 0) 
    meta 
        [
            IsParameterQuery = true, 
            IsParameterQueryRequired = true, 
            Type = type datetime
        ]

// It does not matter what the initial value is for the RangeEnd parameter
// The parameter must be of data type "datetime"
#datetime(2022, 12, 31, 0, 0, 0) 
    meta 
        [
            IsParameterQuery = true, 
            IsParameterQueryRequired = true, 
            Type = type datetime
        ]

Warning

Incremental refresh is designed for data sources that support Power Query query folding. Ideally, query folding shouldn't be broken before the filter step is applied. There's no explicit requirement for the final query to fold, except when implementing Hybrid Tables.

What is a Refresh Policy?

A Refresh Policy determines how the data is partitioned, and which of these Policy Range Partitions will be updated upon refresh. It consists of a set of table TOM properties which can be setup or changed.

Warning

Power BI Desktop limitations: Configuring incremental refresh when connected to a local Power BI Desktop model is not supported. To configure incremental refresh for a local Power BI Desktop model, use the Power BI Desktop user interface.

Refresh Policy properties

Four different kinds of properties make up a basic Refresh Policy:

Incremental window properties: The period window wherein data is kept up-to-date.
Rolling window properties: The period window wherein data is archived.
Source expressions: Define table schema and Power Query transformations of the table.
Mode: Whether Import or Hybrid tables are used.

Incremental Refresh Policy Windows

Comparing to Power BI Desktop

In Power BI Desktop, these properties are named differently. Below is an overview of how the properties match the Power BI Desktop user interface.

Incremental Refresh Policy Windows Properties

Advanced Properties

Depending on the configured properties, Incremental Refresh may function differently. Below is an overview of the different Incremental Refresh configurations:

In the standard configuration of Incremental Refresh, all partitions are imported in-memory. Partitions in the rolling window are archived, while those in the incremental window are refreshed.

In this configuration, the policy range will not include the current period in the rolling window.

In the standard configuration of Incremental Refresh, the current period is always in the incremental window. This might not be the desired behavior, as the data will change with each refresh. If the users do not expect to see partial data for a partial day, you can configure 'Only Refresh Complete Periods'.

This is configured with the IncrementalPeriodsOffset property. In the above example, a value of -1 for an IncrementalGranularity of Day will exclude the current date from the incremental window and thus the data scope; only complete days will be refreshed.

Incremental Refresh Policy Windows

In this configuration, not all records are refreshed in the incremental window. Instead, records are only refreshed if they change. Detect data changes can further optimize refresh performance when using incremental refresh. To identify data changes you use a Polling Expression. A Polling Expression is a separate property that expects a valid M Expression to identify a maximum date from a list of dates.

Typically, you use the Polling Expression on a DateTime column of a table to identify the latest date. If any records match that date, they'll be refreshed. A common example is using a column like [LastUpdateDt] to label records that were updated or added with the current DateTime value. Any records that have values equal to the latest [LastUpdateDt] are refreshed.

Note

Records in archived partitions are not refreshed.

An example of a valid Polling Expression property is below. You can use this as a template when configuring Detect Data Changes in your model from Tabular Editor:

// Retrieves the maximum value of the column [LastUpdateDt]
let
    #"maxDateOfLastUpdate" =
        List.max(
            Orders[LastUpdateDt]
        ),

    accountForNu11 =
        if #"maxDateOfLastUpdate" = null
        then #datetime(1901, 01, 01, 00, 00, 00)
        else #"maxDateOfLastUpdate"
in
    accountForNu11

Incremental Refresh Policy Windows

Overview of all properties

Below is an overview of the TOM Properties in a data model used to configure Incremental Refresh:

Property Name	Power BI Desktop Equivalent	Description	Expected Value
EnableRefreshPolicy	Incrementally refresh this table	Whether a refresh policy is enabled for the table. In Tabular Editor, other Refresh Policy properties will only be visible if this value is set to `True`.	`True` or `False`.
IncrementalGranularity	Incremental Refresh Period	The granularity of the incremental window. Example: "Refresh data in the last 30 days* before refresh date."*	`Day`, `Month`, `Quarter` or `Year`. Must be smaller than or equal to the IncrementalGranularity.
IncrementalPeriods	Number of Incremental Refresh Periods	The number of periods for the incremental window. Example: "Refresh data in the last 30 days before refresh date."	An integer of the number of IncrementalGranularity periods. Must define a total period smaller than the RollingWindowPeriods
*IncrementalPeriodsOffset*	Only refresh complete days	The offset to be applied to IncrementalPeriods. Example for: IncrementalPeriodsOffset=`-1`; IncrementalPeriods = `30`; IncrementalGranularity = `Day`: "Only refresh data in the last 30 days, from the day before refresh date.	An integer of the number of IncrementalGranularity periods to shift the Incremental window.
*Mode*	Get the latest data in real time with DirectQuery	Specifies whether Incremental Refresh is configured with only import partitions or also a DirectQuery partition, to result in a "Hybrid Table".	`Import` or `Hybrid`.
*PolicyType*	N/A	Specifies the type of refresh policy.	Can only contain a single value: `Basic`.
*PollingExpression* (Optional)	Detect Data Changes	The M Expression used to detect changes in a specific column such as LastUpdateDate In Tabular Editor, *the Polling Expression* can be viewed and modified from the Expression Editor window** by selecting it from the dropdown menu in the top-left.	A valid M Expression that returns a scalar value of the latest date in a column. All records in incremental window hot partitions containing that value for the column will be refreshed. Records in archived partitions are not refreshed.
*RollingWindowGranularity*	Archive Data Period	The granularity of the rolling window. Example: "Archive data starting 3 years* before refresh date."*	`Day`, `Month`, `Quarter` or `Year`. Must be larger than or equal to the IncrementalGranularity.
*RollingWindowPeriods*	Number of Archive Data Periods	The number of periods for the rolling window. Example: "Archive data starting 3 years before refresh date."	An integer of the number of RollingWindowGranularity periods. Must define a total period larger than the IncrementalPeriods
*SourceExpression*	Power Query Source Expression	The M Expression for the table data source. This is where the original table M Expression is, and where any existing Power Query transformations would be modified. In Tabular Editor, the Source Expression can be viewed and modified from the Expression Editor by selecting it from the dropdown menu in the top-left.	A valid M Expression containing a filter step appropriately using `RangeStart` and `RangeEnd`.