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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ludovit8
Helper I
Helper I

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  :-(.  How to make it faster?

Look on this video pls.

 

2 ACCEPTED SOLUTIONS

@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)

 

sql server.png

View solution in original post

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).

 

 

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

@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.

@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?

 

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.   bi1@esingroup.onmicrosoft.com

- 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

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 ismy bi virtual server where bi power desktop iswaiting on refresh, after changes in selection  (maybe 5 minutes)waiting on refresh, after changes in selection (maybe 5 minutes)

 

@ludovit8,

 

As I see on last screenshot you have big amount of tables imported into PBI report. 

 

  1. Do you use all of tables you've loaded in this particular report?
  2. Do you use all field of each table?
  3. What is your level of SQL knowledge?

 

Very often better to prepare most of all calculated/joined fields on database side. It is not the best when you load all into report. It is better to load 1-2 tables which will have all required information. Good written SQL query can help to optimize speed of loading data from source to report.

 

 

1. Yes. It is bookeeping of our comapnies. I need also tab of accounts and also tab of invoices and items of invoices.

So, I have there maybe 10 companies and every have sql database:

- bookeepings  (maybe 3-8k rows), two new columns there

- list of accounts (100 rows) connected to excel where we have sorting criteria for that accounts (man. accounting)

- invoices (500 rows)

- items of invoices (2k rows)

 

Then I have there maybe 2 supporting tables (which use data which are already in pbix, like date, or some sorting/selection) and 2 small excels.

But I am hasitating if can be such an amount of tables really the problem. I can imagine other big companies who can have there much more tables, or not?

 

2. What do you mean by field?  I use mostly several columns but I didnt have time yet make selection/check for dowloading just certain columns. Some table can have 50 columns, another just 10. Do you think that checking for import just that columns which I need can significanlty help ?

 

3. My sql skills are weak but I have profesional for that. I hope :-).

 

 

If it will be possible I will try to make some additional formulas on database side.

 

One more question.

Can I make from pbix new database (some export or so), can I save it so there are all columns already counted?

Then I can use this new file as a new source just for presentation.

 

 

 

@ludovit8,

 

Regarding 

One more question.

Can I make from pbix new database (some export or so), can I save it so there are all columns already counted?

Then I can use this new file as a new source just for presentation.

 

There is desktop way to connect to your PBI report.

Each PBIX has SQL Server Analysis Services instance behind. And you can connect to it from any application (Excel, Power BI Desktop, SSMS) to view tables.

 

To do that:

 

1. Open your PBIX report in PBI Desktop

 

2. Go to path on your machine - 

C:\Users\YourUserName\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServices

 

3. Then find last created folder there. If you have opened only one report then there will be only one.pbi SSAS folder.PNG

 

 

 

 

 

4. If you go to that folder, then Data folder and open file msmdsrv.port.txt

(I've created shortcut with this path to go there anytime if necessary 🙂 )

You will see port which can be used to connect:pbi port.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5. Then open new PBI report/other existing one -> Presss Get Data -> Analysis Servicesconnect pbi SSAS 1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6. Enter connection details (where mention localhost:your_port, where your_port you received two steps before)

connect pbi SSAS 2.pngconnect pbi SSAS 3.png

 

7. Select what to import/connect live (if needed)

 

connect pbi SSAS 4.png

Anonymous
Not applicable

@ludovit8

Here are my comments;

1 - You're right, bigger companies have complicated database structure depending upon size and how companies & BUs have been structured, however there are some smarts that you can implement, on SQL side. Ask him how can you, in your environment, can use one table for all companies. This may or may not be a good idea, though :). 

 

2 - As a rule of thumb, keep/bring only those columns that you are using. For example, in our environment, I bring my entire chart of accounts from ERP, but I pick and choose the columns that I need. I bring in account codee (7 level hierarchy), BUs and sort indexer (column to sort accounts) but leave everything else behind. Note that over here, the sort indexer column is a helper/supporting column but I am still bringing it in. In other words ,whatever column you need for reporting, bring it in, leave out the rest. The DAX egnine is column senstive and # of columns does have its impact on the performance. 

 

3 - Not everyone is good at everything, so SQL is not your forte, which is okay. Imortant thing is that you know what do you need in terms of reporting, how and what to present. Discuss this with your SQL guy, and work it out.

 

I did not quite get the following;

One more question.

Can I make from pbix new database (some export or so), can I save it so there are all columns already counted?

Then I can use this new file as a new source just for presentation.

 

If you PBIX is published in the Service (Cloud), you can export it as a PPTX but it's in beta mode, which means, that some visuals might or might not work :). 

 

Let me know if you need any sort of help, happy to pitch in as and when I can. 

 

Thanks
Kaz

@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)

 

sql server.png

Thank for tips, I apppreciate it!

 

I will test everything...in next days. 

 

My own idea from yesterday was divide pbix into two pbix.  I have 5 sheets in one pbix  (three sheets was about accouting and next two was about key paramaters for produce and costs).  So now I have two pbix and each has just half of tables. Managment have to look into two reports now, instead of only one, but it is not a big problem. But the speed is now signifacantly better.

 

I will also test all advices mentioned above.

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
Not applicable

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

@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
Anonymous
Not applicable

Hi @zoloturu

 

I can see the benefits here however it's inconvinient because I'll have to go into each report, set the option and publish it again. The manual part of this job bears the inconvience. 

@Anonymous,

 

Do you look into speed improvement on reports in Power BI Service only? (Do not tuch Desktop and republish)?

 

If so, then you need to buy capacity:

  • Power BI Embedded (on the Azure portal)
  • Power BI Premium (on the Office 365 portal)

 

Then go to Power BI Service -> Settings -> Admin portal to assign your workspaces to capacity

Anonymous
Not applicable

@zoloturu

We are yet to buy capacity therefore cannot try that option. However; even if we had bought capacity, I wouldn't be keen to try that option simply because it creates governance issues. It will cause PBIXs to get out of sync with published reports. 

zoloturu
Memorable Member
Memorable Member

Hi @ludovit8,

 

For speed improvement you can choose below option.

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

 

query reductionquery reduction

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors