Power BI Incremental Refresh for SQL Sources


By:   |   Updated: 2020-07-08   |   Comments (3)   |   Related: More > Power BI


Problem

Incremental refresh is now available for both Pro and Premium capacity in Power BI. There are three main benefits for implementing incremental refresh on a dataset including faster refreshes, more reliable refreshes and, a reduction in resource consumption. Moreover, since incremental refresh is usually much more useful in cases where we have potentially large datasets with millions upon millions of rows and there is a potential for the datasets to grow significantly overtime, then it is essential that the partition filters are pushed to the source system when queries are submitted for refresh operations.

However, these benefits cannot be attained if the data source used in the development of reports in Power BI do not support what is known as Query Folding. Most data sources that support SQL queries like Azure SQL database, SQL Server, Oracle, and Teradata usually support Query Folding. But these need to be implemented appropriately to ensure incremental refresh is working fine.

Solution

In this tip, I will be demonstrating how to implement an incremental refresh on a SQL Server data source the right way. This would be done in a series of steps.

I will be using the SalesOrderHeader table from AdventureWorks2014 database for this tip. In your case you might have more than one table as source data, you just need to figure out which of the tables you need to implement an incremental refresh on. This is usually the fact tables which usually grow with time more than the dimension tables.

Step 1: Connecting the data source with Power BI Desktop

The SQL Server database needs to be connected to Power BI desktop as shown in the diagrams below.

How to get data from SQL Server source to Power BI

Enter the server name, the database name and optionally write out the SQL queries you would want to use. I will explain later how writing out the SQL Queries (not Views) may affect the implementation of incremental refresh.

Server connection to SQL source
SQL script in source connectivity

Step 2: Configure the Power Query datetime parameters

It should be noted that to configure incremental refresh, datasets are filtered using the reserved and case sensitive datetime parameter names RangeStart and RangeEnd. The diagrams below show how to define these.

Creating parameters in Query Editor
Configuration of parameters settings

The "Type" should usually be entered "Date/Time", the "Suggested Values" should usually be "Any Value" and the "Current Value" should be any datetime value since Power BI service would auto detect this later. However, I just used a MAX of "Modifieddate" from the dataset in RangeEnd and a MIN of the "Modifieddate" in RangeStart.

Query Editor window showing created Parameters

Step 3: Configure the parameters to apply filters to the dataset

In this step we are going to locate the column in the dataset we are trying to apply the filter of incremental refresh on and map it to the parameters created by using custom filters as shown in the diagrams below.

Navigating to Custom Filter ANavigating to Custom Filter B

Click on "Date/Time Filters" and navigate to "Custom Filters". In this case we used the Basic configuration as shown below.

Custom Filter configuration window

Then input the values exactly as shown below. Click OK and Close & Apply the Query Editor window.

Parameters mapped and configured in Custom Filter window

Step 4: Activate the incremental refresh on the dataset

Having finished the initial configuration, it is now time to activate the incremental refresh settings on the dataset as seen in the diagrams below.

Navigating to the Incremental Refresh settings window

Next you can see that we are getting a warning in the next diagram that our dataset cannot be folded or does not support Query Folding. But remember we are using a data source that supports SQL Queries in this tip you might say. We can verify this further by going back to the Power Query Editor and check if the data source supports Query Folding by right clicking on the source step or any step in the Applied Steps in the dataset and checking if the dataset "View Native Query" highlighted or greyed out. If greyed out it means the dataset cannot support Query Folding, thus even if we manage to setup the incremental refresh, it would be very slow and might not work as planned.

Incremental refresh window showing warning
Query Settings showing no Query Folding AQuery Settings showing no Query Folding B

The way to resolve this is to go back to Power Query Editor, click on the properties icon of the source data and totally remove the SQL Statement query. Then only import all tables and select the tables required. As seen in diagrams below.

Query Settings to navigate back to data source settings
Diagram showing SQL Query to be removed from source
Warning to allow a new navigation step in Applied Steps

After this, redo Step 3 & Step 4 again. You should now be able to see the incremental refresh settings without the warnings. And you can verify if there is Query Folding taking place by following steps mentioned in step 4 (the View Native Query is now highlighted and not greyed out).

Query Settings showing Query Folding now supported AQuery Settings showing Query Folding now supported B

You can now view the Native Query behind this incremental refresh, should look something like the diagram below. Click Close & Apply.

select [_].[RevisionNumber],
    [_].[OrderDate],
    [_].[DueDate],
    [_].[ShipDate],
    [_].[Status],
    [_].[OnlineOrderFlag],
    [_].[SalesOrderNumber],
    [_].[PurchaseOrderNumber],
    [_].[AccountNumber],
    [_].[CustomerID],
    [_].[SalesPersonID],
    [_].[TerritoryID],
    [_].[BillToAddressID],
    [_].[ShipToAddressID],
    [_].[ShipMethodID],
    [_].[CreditCardID],
    [_].[CreditCardApprovalCode],
    [_].[CurrencyRateID],
    [_].[SubTotal],
    [_].[TaxAmt],
    [_].[Freight],
    [_].[TotalDue],
    [_].[Comment],
    [_].[rowguid],
    [_].[ModifiedDate]
from 
(
    select [RevisionNumber],
        [OrderDate],
        [DueDate],
        [ShipDate],
        [Status],
        [OnlineOrderFlag],
        [SalesOrderNumber],
        [PurchaseOrderNumber],
        [AccountNumber],
        [CustomerID],
        [SalesPersonID],
        [TerritoryID],
        [BillToAddressID],
        [ShipToAddressID],
        [ShipMethodID],
        [CreditCardID],
        [CreditCardApprovalCode],
        [CurrencyRateID],
        [SubTotal],
        [TaxAmt],
        [Freight],
        [TotalDue],
        [Comment],
        [rowguid],
        [ModifiedDate]
    from [Sales].[SalesOrderHeader] as [$Table]
) as [_]
where [_].[ModifiedDate] >= convert(datetime2, '2011-05-31 00:00:00') and [_].[ModifiedDate] < convert(datetime2, '2014-06-30 00:00:00')

Next, try to activate the incremental refresh settings again as shown below. You would be able to now see that there is no more warning message.

Incremental Refresh window now without warning and configured

This guarantees that our incremental refresh would work well as required since the filter partitioning is able to be pushed to the source data and thus guarantee that all the benefits of incremental refresh would be attained.

Step 5: Publish to Power BI Service schedule refresh frequency

The next step is to publish the created reports to Power BI Service and then where applicable configure a refresh schedule without which the refresh would not take place. Initially, this is usually not activated as seen in the diagram below.

Power BI Service in the Dataset window with warning

The warning sign indicates that there are some issues with the scheduled refresh settings.

Power BI Service in the Dataset window with warning for activating Scheduled Refresh.

However, since the configuration of gateways and scheduled refresh are not the focus of this tip, I would advise that where applicable this should be looked at to ensure the refresh works properly.

Next Steps
  • Learn more about how to configure refresh schedule here.
  • You can get more information on Query Folding here.
  • Learn more about how to use or configure parameters in Power BI here.
  • Learn more about Power Query date filters here.
  • Try this tip out with your data.


Last Updated: 2020-07-08


get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Microsoft Certified Data Analytics and BI Professional mostly in Microsoft BI stack of tools.

View all my tips





Comments For This Article




Wednesday, July 22, 2020 - 10:08:15 AM - Kenneth Omorodion Back To Top (86176)

Thanks for your points raised Lutz, i think the use of "usually" in the article does not negate the fact that you should be using datetime type. Also this post is focused on how Incremental Refresh is done for SQL sources, this article did not go beyond verifying if there is Query Folding occuring to ascertain incremental refresh. There are a number of other articles and youtube videos that demonstrated that, this is the direction this blog covered on this. Thanks!


Tuesday, July 21, 2020 - 9:37:32 PM - Lutz Back To Top (86173)

Quote: The "Type" should usually be entered "Date/Time"

Not just usually. It is mandatory. Incremental refresh will not work otherwise.

You may also want to mention partitions and ways to check that the incremental refresh actually works.


Wednesday, July 08, 2020 - 8:57:41 AM - bernard black Back To Top (86105)

Nice article.  Informative and to the point.



download





Recommended Reading

Using Power BI with JSON Data Sources and Files

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI

Power BI Conditional Formatting for Matrix and Table Visuals

Read API Data with Power BI using Power Query

Power BI Workspace Permissions and Roles














get free sql tips
agree to terms