Working with big database

[Andvil]

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

[d_gosbell]

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. 

[Andvil]

Thank you @d_gosbell ,

I just saw that incremental refresh is only for PRO and PREMIUM users. Thank you for the advice, hopefully one day I can have the subscription and use this feature.

Any other advice on how to reduce the size of the document will be helful.

Best,

JALV

[mahoneypat]

Did you uncheck enable load on the three tables you append?  It will reduce your file size by a lot.

Regards,

Pat

[Andvil]

Hi @mahoneypat 

I unchecked the 2018 and 2019 tables. The 2020 table I didn´t uncheck because that table gets updated monthly, should I uncheck 2020 too?

Also, my file is still very heavy.

Regards,

JALV

[mahoneypat]

Yes.  You should also uncheck 2020, assuming you appended all three tables into a new query.  If you appended into the 2020 query, then you need to keep load enabled.  Are there high granularity tables you can remove/not load?  How big is your file size after that?

Regards,

Pat

[Andvil]

Thank you @mahoneypat after unchecking unable load in all the tables, my file size was considerably reduced. Thank you for the advice!!

[mahoneypat · Accepted solution]

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

[Andvil]

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?

[mahoneypat]

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