Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to Solution.
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)
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).
@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.
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 is
waiting on refresh, after changes in selection (maybe 5 minutes)
As I see on last screenshot you have big amount of tables imported into PBI report.
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.
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.
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:
5. Then open new PBI report/other existing one -> Presss Get Data -> Analysis Services
6. Enter connection details (where mention localhost:your_port, where your_port you received two steps before)
7. Select what to import/connect live (if needed)
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
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)
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).
Hi @zoloturu
Here we go;
@Anonymous,
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:
Then go to Power BI Service -> Settings -> Admin portal to assign your workspaces to capacity
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.
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 reduction
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
49 | |
28 | |
20 | |
20 | |
19 |
User | Count |
---|---|
52 | |
51 | |
26 | |
26 | |
22 |