Incremental refresh Rest Api best practice

[Anonymous]

I managed to set up Incremental Refresh in Power Bi where the data source is a Rest API. I'm looking for some advice on how to make this useful.

A little background. This is done by creating af table, where one column is dates. The next column invokes a functions, which makes a call to the API (including login etc). This works for a few days, but it's not going to work for thousands of day, since it is very slow.

So I'm looking for a way to set this up, so I can make a bulk load (this is supported by the API) and then run incremental scheduled update hereafter.

Any recomendation are appreciated.

[Anonymous]

Thanks for your input. I've have been working the issue quite a bit. The main issue was, the slow speed, when doing the bulk import for 4 years, since the api is call for every single day. The API support a from and to date, which is able to minimize the ammount of data. I did make a big mistake, when call the api. The result was, that I imported  all data (4 years) for every single date, which made it very slow. Performance has imporved, but I need to test if the problem has been solved.

[v-jingzhang]

Hi @Anonymous 

If you set from date and to date in the query, it seems most of the query time will be happening at the backend of the API. In case of any timeout error, you could try setting a longer timeout value in the query. 

Web.Contents - PowerQuery M | Microsoft Docs

Jing

[v-jingzhang]

Hi @Anonymous 

You can try setting up incremental refresh on a table from a REST API data source, but it may not be able to improve the refresh speed as you expected. I extract related paragraph from Incremental refresh for datasets in Power BI - Power BI as below.

=======================================

Incremental refresh works best for structured, relational data sources, like SQL Database and Azure Synapse, but can also work for other data sources. In any case, your data source must support the following:

Date column - The table must contain a date column of date/time or integer data type. The RangeStart and RangeEnd parameters (which must be date/time data type) filter table data based on the date column. For date columns of integer surrogate keys in the form of yyyymmdd, you can create a function that converts the date/time value in the parameters to match the integer surrogate key of the data source table. To learn more, see Configure incremental refresh - Convert DateTime to integer.

Query folding - Incremental refresh is designed for data sources that support query folding, which is Power Query's ability to generate a single query expression to retrieve and transform source data. Most data sources that support SQL queries support query folding. Data sources like flat files, blobs, and some web feeds often do not.

When incremental refresh is configured, a Power Query expression that includes a date/time filter based on the RangeStart and RangeEnd parameters is executed against the data source. The filter is in effect a transformation included in the query that defines a WHERE clause based on the parameters. In cases where the filter is not supported by the data source, it cannot be included in the query expression. The query mashup engine compensates and applies the filter locally, which requires retrieving all rows for the table from the data source. This can cause incremental refresh to be slow, and the process can run out of resources either in the Power BI service or in an On-premises Data Gateway - effectively defeating the purpose of incremental refresh.

Before configuring your incremental refresh solution, be sure to thoroughly read and understand Query folding guidance in Power BI Desktop and Power Query query folding. These articles can help you determine if your data source and queries support query folding.

=======================================

Per my understanding, REST API data sources probably don't support query folding, so setting up incremental refresh for it can help reduce the amount of data but perhaps cannot improve refresh speed. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.