Forum Discussion
Working with big database
Hello to everyone,
I am currently working with a big database that has information of 2018, 2019 and 2020. The information of this year is updated monthly. I have three different tables corresponding to the three different years imported from SQL into my Power Bi desktop.
I have had some trouble working with the tables separated and I decided to append the three tables. The problem is that I append the three tables and I still have to keep the original separated tables, thus I have the information loaded twice in my report (I have the 3 tables from 2018 to 2020 and I have the appended table). This is causing my report to be twice heavier, so I can´t share it to Power Bi services and then sharing to my colleagues and it is also becoming very slow and sometimes it freezes.
1) I would like to know which solutions I can use to solve this document weight problem, remember that information from 2020 is updated monthly.
2) Also, I would like to know if there is a way to upload only the new month information because so far, when I update the information it updates the entire appended table and it takes a lot of time to update whereas updating only the last month would we faster.
3) I would like to know ways I can share my report when it is too heavy to be uploaded to Power Bi Services.
Thank you very much for any help and advice.
Best,
JALV
To reduce your file size, you can do the following:
1. In query editor, right click on the queries that are appended and uncheck Enable Load, so you don't load your data twice. They will still be refreshed but not loaded. Also get rid of any columns and rows you don't need, especially columns with high granularity.
2. If available, you can set up incremental refresh to reduce refresh time significantly.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
9 Replies
To reduce your file size, you can do the following:
1. In query editor, right click on the queries that are appended and uncheck Enable Load, so you don't load your data twice. They will still be refreshed but not loaded. Also get rid of any columns and rows you don't need, especially columns with high granularity.
2. If available, you can set up incremental refresh to reduce refresh time significantly.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hello mahoneypat ,
Thank you for replying, regarding 1), it is a good advice as it can help me to reduce the update/refresh time but I am interested also in reducing the size of the document. Do you have any advice on how I can work with three different tables (2018 to 2020) in a way I can reduce document size?
As I mentioned in my post, I appended the three tables but I have to keep the original separated tables because I guess that if I delete the original tables, it will affect the appended table.
2) How can I use incremental refresh? will that help me to update only the last month of 2020?
That's the nice thing about unchecking Enable Load, it still refreshes the data when you hit refresh but it isn't loaded in your model. You don't need to load them. The appended table will be the same.
That should get you a big reduction (half + I'm guessing). After that, get rid of as many columns as you can (and rows of course, if possible). See this video on incremental refresh with pro licenses - https://www.youtube.com/watch?v=-pjmKI66iRQ
If you have premium, it is even easier.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
You should have a look at trying implement incremental refresh https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh
This should allow you to only load the data once and only refresh the current month.
Did you uncheck enable load on the three tables you append? It will reduce your file size by a lot.
Regards,
Pat
