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