Forum Discussion

tktmastr's avatar
tktmastr
Helper I
6 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      tktmastr
      Helper I
      6 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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