Power BI just recently released the ability to set up Incremental refresh policies through the Power BI Desktop.
This is a short blog post with my first thoughts on it.
Everybody has been excited about the possibility of doing Incremental Refresh through Power BI. Back in the day, this was only something that you could accomplish using Partitions in SSAS, which would require a server and it didn’t use M syntax at all.
Recently there have been new releases like Azure Analysis Services and new versions of Analysis Services that have Power Query integrated into it which allow for really dynamic M syntax. To this point, everything was through a SSAS but now we have the ability to basically create partitions through a Power BI Desktop model.
I not only wanted to test this out by itself, but by combining it with my Custom Connectors. Could I create a scenario where I’m getting data from the WooCommerce API (using my Custom Connector from here) and set up an incremental refresh?
Let’s find out.
Setting things up and requirements
Before we start, we need Power BI Desktop, the Custom Connector for WooCommerce and an account with Power BI Premium. If you don’t have Power BI Premium, then unfortunately you won’t be able to use the Incremental Refresh as this feature is only available for Power BI Premium tenants.
Don’t forget to go inside the Options of Power BI Desktop and enabling the “Incremental Refresh” Preview feature:
You can download the Custom Connector from here and I’m hoping that you have the latest version of Power BI Desktop already installed.
Getting the Data inside
I installed the Custom Connector and made sure that it was already enabled as a preview feature so I could see it inside the ‘Get Data’ window:
I went through the parameters window where I entered the values for my connection:
then I was greeted with a navigation window where I had 2 elements. 1 Table and 1 function.
I created the Orders table so I could have the same experience that I get from other connectors which simply try to fetch all the data and I also created this “OrdersByDate” function specifically for this Incremental Refresh Scenario.
Setting up the Parameters
The reason why I needed to create this specific function (OrdersByDate) is because Power BI requires 2 parameters in order for the incremental refresh to work:
- RangeStart – the first date of your period (Jan-1-2017)
- RangeEnd – the last date of your period (Dec-12-2018)
Both of these parameters need to be of the Date/Time data type. If you wish to get to know more about what Parameters are and how to use them, I highly encourage you to take a look at this video where I go in-depth about them.
You can set them through the Manage Parameters and you’d reference both of those parameters inside your query like this:
If your function requires a “Date” data type instead of Date/Time then you can use the function “Date.From” to transform the value from the parameter into a Date data type, but the Parameters need to be Date/Time.
Once that’s set up. Click Close & Load.
Setting up the Incremental Refresh
Now back inside Power BI Desktop, right click on your table and you’ll notice that incremental Refresh is now an option. Click on it and you’ll be taken to the Incremental Refresh window:
When I tried this, clicking on the Learn more link took me to a 404 page, but this was pretty intuitive. You toggle the incremental refresh on and then select how many years, months, quarters or whatever period you might need to store your data and how Power BI should refresh your data. Should it refresh only the last month of data? last quarter? last year?
You also have this option to detect data changes and this is where other date fields in your table come in handy. If you have a field like “date modified”, then this is the time to use it.
Again, everything looks pretty clean and straightforward. Once you finish setting up all of these “policies”, you can simply publish this report/model to a Power BI workspace with Premium.
Setting up Incremental Refresh on the Cloud
I then did the whole process that I described here to set up the personal gateway and be able to refresh the data from my custom connector.
I ended up doing a refresh and everything went good:
I even waited some time to make sure that everything was set up correctly:
but one thing that I’m uncertain is, is it really working or not ? The Datasets window or the Settings for this Dataset look exactly the same to any other Dataset. I wish this window gave me more information about the “Partitions” that this dataset had and the refresh history based on each partition.
I wasn’t able to find any hints as to whether this was working or not. The data was refreshed, but from here I couldn’t tell if this was working or not. What were the calls that it made to the API? I couldn’t tell from here, so I had to resort to using Fiddler locally on my computer and find out if the Gateway was telling my computer to make the calls that I needed or not.
Don’t forget – I set up the parameters of the file to have the following values:
and I set up the Incremental refresh to always refresh data in the last month.
Here’s a screenshot of what Fiddler said:
based on what Fiddler is telling me, it appears to be working! Incremental Refresh works! (even with Custom Connectors!!)
You can tell by looking at the parameters of the call. As you can see, it says that the call was made for the range of dates between May 1st and June 1st of 2018 which is exactly the month that I’m trying to refresh.
THIS IS HUGE!
Personal note: I still need to figure out why it made 3 calls instead of just 1, but it works!
Update 9-May-2018: The links to the Official Incremental refresh in Power BI Documentation are now available. Click here to see the documentation.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.