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