Forum Discussion

ludovit8's avatar
ludovit8
Helper I
8 years ago
Solved

Why is loading/refreshing data in powerbi cloud or same problem also in desktop so slow ?

Every change of some selection start new loading/refreshing data but it is too slow. In PBI desktop but also in BI cloud. Our manažment can not wait for every change in selection one minute  :-(.  H...
Need Help
  • zoloturu's avatar
    zoloturu
    8 years ago

    ludovit8,

     

    What I recommend to you in developing report is to use only tables and columns you need. 

    Your RAM spending = SUM of memory size of each field together

    If you decrease number of columns using SQL then you will decrease it very much.

     

    When you connect to your SQL Server/Azure SQL Database you can mention SQL script as shown below. It can be simple SELECT or something complicated as stored procedure which returns dataset

    (In case of Analysis Services it can be written in MDX or DAX)

     

  • ludovit8's avatar
    ludovit8
    8 years ago

    The best solution is this one.

    http://community.powerbi.com/t5/Desktop/Combine-multiple-tables-into-one-table/m-p/60752#M24933

     

     

    Now it works perfectly, everyrefresh/changing parameters in visualization takes 1-2 seconds....   I can consider this link as a solution for everybody who has problem with slow PBI with a lot of tables.

     

    I have maybe 10 bookeepings joined in one table now, 10 tables of invoices joined in one table and so....  one added column in table to indentify company. And all those sources have in querries turn off "enable load" option  (so total refresh is working but it is slowing down powerBI).

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

zoloturu Does this mean that we have to make this change to every PBIX and then republish it? If so, this is not very convinient solution.

  • zoloturu's avatar
    zoloturu
    Memorable Member
    8 years ago

    Anonymous,

     

    You are correct. These settings should be applied to each file separately. 

     

    Give me some more details on your reports and systems. I will try to give an alternative.

    1. Which source type do you use? (SQL Server, Azure SQL, SSAS, Excel etc.)
    2. Do you use Data Gateway to connect to your source?
    3. Which connection type you have in the report? (DirectQuery/Import/Live)
    4. Are you Free or Pro Power BI Service User? 
    5. Do you have Power BI Premium capacity?
    6. Are you an admin on your PC?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi zoloturu 

      Here we go;

      1. Which source type do you use? (SQL Server, Azure SQL, SSAS, Excel etc.)
        1. On Prem
          1. SQL Server, Oracle, Excel, CSV, SSRS
        2. Cloud/Online Services
          1. One Drive, SharePoint Online, web sources 
      2. Do you use Data Gateway to connect to your source?
        1. We have the Enterprise Data Gateway 
      3. Which connection type you have in the report? (DirectQuery/Import/Live)
        1. Almost all of them are Import
      4. Are you Free or Pro Power BI Service User? 
        1. Pro Users all of them
      5. Do you have Power BI Premium capacity?
        1. Not yet
      6. Are you an admin on your PC?
        1. I am admin on my machine, not sure of my business users who are PBI authors
      • zoloturu's avatar
        zoloturu
        Memorable Member
        8 years ago

        Anonymous,

         

        1. You've said that it is not convinient to use settings per each file? Can you describe why?
        2. One of the improvements can be to set SQL Server Analysis Services (SSAS) Tabular instance on your machine between your sources and Power BI reports. As result you will have:
        • Ability to connect Live to your SSAS database. Much more faster then other connection types. Automatic refresh of report from SSAS
        • You will import all your sources to one SSAS database. Easy to build new reports. Only one source to connect to
    • ludovit8's avatar
      ludovit8
      Helper I
      8 years ago

      Details:

      - mostly sql and some excel

      - If I understand it correctly, yes I have on-premises data gateway (personal mode) for daily data update, but I usually refresh it manually. 90% of data sources are sql database, not big, maybe up to 1 ir 5 thouasnds rows.

      - I am pro power BI user.   [email protected]

      - I dont have power Bi premium capacity, I dont know what it is.

      - Yes I am admin. But problem is not only in PBI desktop, it is slow also in PBI could = your services/hardware

      • ludovit8's avatar
        ludovit8
        Helper I
        8 years ago

        Hi, this advice doesnt help.

        *Go to File -> Options and Settings -> Options -> Query reduction (CURRENT FILE) and select checkboxes

        We need use some filters/selections but the main problém is still the very slow refreshing after changing some filters.

         

        I am using PBI desktop, my virtual server has 64GB ram, I have there 4 intel cores and not fully used, maybe just about 30-70%.

        But I had to wait sometimes 2-5 minutes, sometimes longer as you can see in first post, in my video. I also show some of my formulas/measures which I used.

        Settings in datasource of sql database is import, not DirectQuerry.

         

        So....if I am not talking about BI cloud, but only PBI deskop and my server doesnt seems to be fully used it is maybe some problem with program itself ?  Or maybe my calcutions are bad? This your hint, like use apply button, or other options in query reduction doesnt solve the problem. I hope you can maybe help with some other ideas, I like powerbi but it had to be much faster.

         

        Can you give me some other hints for making it faster?

        (if you have some additional question just ask me)

         

        my bi virtual server where bi power desktop iswaiting on refresh, after changes in selection (maybe 5 minutes)