Slow import from web API

[tktmastr]

Hi,

I am trying to pull data from a web API (from our company's CRM database that is onsite). It is extremely slow trying to connect and refresh. I made a new Power BI file and one table with 16 columns and approx 1 million rows.

1. Is it normal to take 30min to import and refresh? This is just one table and I have many others.

2. If not, is there anything I can do to troubleshoot? I have 32GB of ram on my computer... does an API query cause data load on our CRM database server too?

Thanks

[v-shex-msft · Accepted solution]

HI @tktmastr ,

#1, I think these time is spent on spend send requests and receive the responding results, normally REST API has a limitation on result amount.  (e.g. 100 records per requests) In addition, there are too many columns in your data table, you can remove unused columns from loading to improve loading performance.

#2, Maybe you can create a temp database(load file or import to database) to stored and cache records from your API, power bi will spend less time loading records from database.

In addition, you can also take a look at the following document about power bi performance practice:
Power BI performance best practices 

Regards,

Xiaoxin Sheng

[tktmastr]

Great, thanks. Someone else also recommended making a separate database to use as a connector in the middle of the API & Power BI - they said to look at Postman. Do you think that would be a viable option or is there something better?

Thanks again for the help

[v-shex-msft]

HI @tktmastr ,

In my opinion, I'd like to suggest to design a web service to pull data and save to local file. (e.g. excel) Then you only need to get data from the local file and schedule to refresh your report to get the last data.

BTW, power bi not able to invoke javascript or other scripts from a webpage, these scripts will be blocked and only get website elements.

Regards,

Xiaoxin Sheng