Optimize slow OData API appended queries

[Anonymous]

Hello friends,

I work with an accounting system that has an API which returns up to 20K rows in one call.

Since there is no incremental update option built in Power BI - I need to refresh all the data each time.

I have appx. 200K rows total so I have created 10 OData Queries (with different $filter= ) and then I created one FACT Query that appends them all.

(Instead of append I have honestly tried to use a function that will loop through 10 calls, but it took even more time)

I also have several queries that connect to OneDrive for Business Excels and then use these with my Fact in the model.

Currently it takes about 7-10 minutes to refresh the whole model and my users suffer very much.

How can I improve the refresh time?

What different approaches can I use?

Thank you

Michael

[v-caliao-msft]

@Anonymous,

I have appx. 200K rows total so I have created 10 OData Queries (with different $filter= ) and then I created one FACT Query that appends them all.
Why would you do that? Based on my understanding, it takes more time by creating 10 queries because you need to connect to source multiple times and send your query to datasource server. Besides, it also take time to append all data. In your scenario, please try to create a data query instead of 10 queries and check if there are any improvements.

Besides, performance issue not only related to data load, it also related to your report structure. Please avoid (or hide) columns with duplicate information in your report, for the details, please refer to the link below.
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-auto-insights-optimize/

Regards,

Charlie Liao

[Anonymous]

@v-caliao-msft

Thanks Charlie,

As I have mentioned, the API service I am working with is restricting the results of one call to 20K rows, so I need to make several filtered calls.

So what do you mean by: "try to create a data query instead of 10 queries "?

I don't quite understand what "data query" do you have in mind?

Plaese help,

Thank you

Michael