Managning refresh of data

[toafk]

I've a query, requesting data from a Rest API. The query got a time out from the server. Since the API supports getting data within a date range, I've spilt the data in three queries and appended them. 

This works great. In order to speed up data refresh, I would like only to freshed data from the current month. It seem Power Bi only support refreshing all or none of the data? How can a partial refresh be implemented?

Thx  Torben

[BA_Pete]

Hi @toafk ,

It sounds like you want something like incremental refresh (IR).

You would need to get a static position for your seed data, but you could then use the API to collect only the most recent data and pass to the Service.

More info on IR:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

One thing to note - you must ensure that your report/dataset that will be running with IR is in its final production state and can stand the test of time. Once you start the IR process, you will not be able to download or retain the incrementally-built data if you later want to change something.

Pete

[toafk]

Hi Pete, 

can you elaborate a little on the "static positon for you seed data". I'm not natural english Speaking, so I need to understand, what you mean. Would it be like bulk loading data, and the use IR from this point?
In that case, how do I create the static position?
I actually have data 5 years back. IR makes a call to the rest api for data for every single date. This is going to take hours to complete the intial load on since it's going to call the API approx 2000 times.
thx

Torben

[BA_Pete]

Hi @toafk ,

Yes, you've understood correctly.

IR will only add onto what is already in the initial dataset so, if you want all five years of your data, you'll need to essentially bulk pre-load it into your dataset, then start IR running to keep adding on the new months as they happen.

Your dataset query would essentially be: "Give me all data from five years ago until [currentDate]". Then you IR parameters would override this to only refresh data within a shorter time period that you specify.

Pete

[toafk]

Thanks again Pete,

what I done in a different case.

1) Created af Parameter StartDate

2) Build a list dates from StartDate till Today

3) Build the query, which calls the Rest Api one time for each date.

4. Setup Incremental Refresh, so in only refreshes data for the last 21 days (data can be change within 3 weeks), when data is refreshed the second time. 

Still step 3) takes very long. Is this the best way to do it?

[BA_Pete]

Hi @toafk ,

I think this is the best way to do it as I assume this will give you some error/failure handling i.e. if one day request fails, it can be re-run, but if you try and make a larger single API call, it will be much harder/take longer to re-run that inividual call to fix.

Overall, I think you need to weigh up the time taken to implement this against the time saved in the future once it's set up, and you have a cloud-based dataset that holds all of your data with little-to-no maintenance.

Pete