Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tktmastr
Helper I
Helper I

Slow import from web API

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

 

web api.png

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.