Important Considerations for Setting up Incremental Refresh on Power BI Desktop

[Kumail]

This simple and brief article is about Incremental Refresh setup in Power BI. By the end of this article, you will know more about incremental refresh, its advantages and setup process.

What is incremental refresh?

Incremental refresh is a feature in Power BI premium which enables more reliable, more efficient resource consumption and fast refreshes on very large datasets. This feature is available with Power BI premium tenant only.

Use Case

When working with potentially billions of rows of data, in order to use Power BI desktop efficiently we need to filter upon import. This is where incremental refresh comes in handy.

Important requirements for Incremental Refresh to Work:

• The data source must support “query folding” which is supported by most data sources that support SQL databases.
• The filter logic (filter is pushed to the source system when queries are submitted for refresh) is included in the source queries.
• The filter only applies to date time column.
• In case the date is used as a surrogate key in data warehouse of the form yyyymmdd. The function can be called by the filter step (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

How to set it up?

Process

1. Filter data set using power query editor. It requires setting up 2 parameters;
2. Connect to a data source with import or direct storage mode. For this demo, I connected to MS SQL database.
   • Press edit queries from “Home” menu in Power BI desktop to navigate to power query window.
   • In query editor, select “New Parameter” from home tab.
   • Name new parameter “RangeStart” (Case sensitive), select type “Date/Time”, suggested values “Any Value” and current value “12/29/2010 12:00:00 AM” (first date of your period).
   • Create another parameter “RangeEnd” (Case sensitive), select type “Date/Time”, suggested values “Any Value” and current value “12/30/2011 12:00:00 AM” (last date of your period)Manage Parameters
3. Select table that you want to incrementally refresh and select date column to apply “RangeStart” and “RangeEnd” to custom filters.

     Step 1: Filter date column and go to custom filters.Custom Filter

     Step 2: Apply parameters “StartRange” and “EndRange” in custom filters.Filter Rows

     Step 3: Verify “filtered rows” step added in applied steps pane.

 Applied StepsNow close the query editor window and apply any pending changes.

Refresh Policy

With refresh policy, organizations have more control on their large dataset particularly how often it incrementally refreshed in Power BI service.

Refresh policy for incremental refresh is defined in Power BI desktop and applied on Power BI Service.

• Step 1: In Power BI desktop, select table where you have applied custom filters and go to its context menu.Context Menu 

• Step 2: Select Incremental Refresh option that will pop up Incremental Refresh window:
  • Select Incremental refresh table (where you have applied custom filters).
  • Create rows storage policy.
  • Create rows refresh policy.Incremental Refresh

Now publish the report to Power BI service, configure gateway connection (that you already know. If not check out other blogs!!) and enter data source credentials and refresh.Publish to web

And you're done! Everything looks pretty clean and simple doesn’t it?

I hope now you know more about Incremental Refresh in Power BI and its setup 😊.

Thank You