Miguel Escobar Published May 8, 2018

First Impression on Power BI Incremental Refresh

Power BIPower Query

fireworks

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:

image

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:

image

I went through the parameters window where I entered the values for my connection:

image

then I was greeted with a navigation window where I had 2 elements. 1 Table and 1 function.

image

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)

imageBoth 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:

image

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

image

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:

image

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:

image

I even waited some time to make sure that everything was set up correctly:

image

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:

image

and I set up the Incremental refresh to always refresh data in the last month.

Here’s a screenshot of what Fiddler said:

image

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! Smile

————————————

Update 9-May-2018: The links to the Official Incremental refresh in Power BI Documentation are now available. Click here to see the documentation.

Power BIPower Query
Subscribe
Notify of
guest
1 Comentar
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Dhvanil

Hey, from what I understood, this approach means, that every api call made for the refresh will go via desktop.
So, does that also mean that data is stored locally in the desktop as well?

What happens, if I don’t use a gateway, but directly publish the report to the service? Will it still work? Because, I tried following the exact same steps and tried to publish it to the service and not gateway, and it is throwing 500 internal server error.