Forum Discussion

andyspa's avatar
andyspa
Helper I
4 years ago

Queries long to load

Hello everybody!

 

I've built a model that is getting data from different sources' types ".csv & web".

 

For each country folder I have the first query that is scrapping data from a web table. Then I have a second query that is invoking a custom functions since the web tables are more than one-page long. Then I have a 3rd query coming from .csv file. I cleaned all queries to have same column names and format, and all are merged in the "Overview" query (see bottom).

 

The problem is that is taking far too long to load queries in the model once closed the query editor. I unticked already "enable load" and "include in report refresh" all of them besides the Overview, which is the only one I want to keep in the model. However what I noticed is that I cannot untick "include in report refresh" the queries that scrap data from web. So I'm afraid every time I refresh it's fetching the data from the web, hence the slow. Any idea how can I avoid that? The data taken from the web are static so don't need a daily refresh, maybe every six months only. 

 

Thanks,

a.,

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    andyspa , Create some parameters and restrict the data load, If you are a premium customer, you should be able to change those in test env, to load more data there

    Premium- Deployment Pipeline, Load More Data on Test/Prod : https://youtu.be/l69cnWkoGX0

     

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi andyspa 

     

    As the data taken from the web are static and don't need a daily refresh, you can store the web data in a csv file, and load data from this csv file into your report. This would be faster. 

     

    You can also create a dataflow in Power BI Service to hold data from the web and refresh the dataflow every six months. Then in your report, connect to this dataflow to get the web data. When you refresh the report, it will load data from the dataflow without needing to query the web.

     

    Have a try!

     

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

    • andyspa's avatar
      andyspa
      Helper I
      4 years ago

      THanks my friend. You mean to create a table once and export as csv, and use that one as source in another file?

       

      Regarding the dataflow, it looks like I don't have that option:

       

       

      Thanks,

      a.

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        4 years ago

        Hi andyspa 

         

        As you said the web data just needs to be refreshed every six months, so I think exporting it into a csv file might improve the loading speed. But you still need to test this. 

         

        For dataflow, it is not available in My workspace. If you have access to other workspaces, you can create a dataflow there. Creating a dataflow - Power BI | Microsoft Docs

         

        Jing