Forum Discussion
Why is loading/refreshing data in powerbi cloud or same problem also in desktop so slow ?
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).
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
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)
As I see on last screenshot you have big amount of tables imported into PBI report.
- Do you use all of tables you've loaded in this particular report?
- Do you use all field of each table?
- 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.
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)