cancel
Showing results for 
Search instead for 
Did you mean: 

Important Considerations for Setting up Incremental Refresh on Power BI Desktop

 

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)Parameters.pngManage 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.CustomFilter.pngCustom Filter

 

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

 

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

 FilteredRows.pngApplied 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.ContextMenu.pngContext 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.IncrementalRefreshPolicy.pngIncremental 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.PublishToWeb.pngPublish 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

Comments

Hi Kumail ,
Thanks for the post.
Currently incremental refresh works for the data was updated in , say 10 days. It does not take into consideration the Primary Key that might have been inserted in the past.
Eg: lets say a "Sales Order" was created 3 months , but only today its status is moved to "Shipped". Since I am only looking for past 10 days data for incremental refresh, a new row gets inserted into the Dataset causing my Sales Order to be duplicated. How can we avoid this?

Thanks again for your help