Forum Discussion
Optimize Service memory usage
So I have a report which has an incremental refresh on a Salesforce source. This Desktop report is published to Service which, fully loaded is approximaly 9GB on Service. This report is (currently) refreshed every 4 hours. I'm working on some other report to put on there as well in the excess of 7GB with a planned refresh of 4 hours and as the source an SQL database.
Now the discussion I'm having with our Power BI Governance team is that we at corporate level are hitting the memory capacitiy of our node of 50 GB. We're having a discussion on slimming down reports but we struggle to define the memory usage in the first place. My argument is that when I would change my report from Import to Direct Query, it would in fact use much more memory as the table will be stored in Cache (Primary Storage aka Memory), while with Import it would be stored in storage (aka secondary storage). My argument would say that the current limitation is on the Memory and not the (secondary) Storage, thus having the report as Import is a better way to publish the report than using Direct Query.
Everyone is making loads of assumptions, including myself on Memory usage and how storage would work on Service. Also the monitor dashboard created by Microsoft used by our Admins is limited in that the Memory usage can't be filtered to the report/ workspace.
One says Direct Query would be the Memory saver while the other (myself) says the import is the Memory saver. What is true?
8 Replies
DouweMeer So when you are using Import mode for your tables, whenever the report is used, the dataset is loaded into the capacity's memory. When the refresh happens, roughly the dataset size x2 is reserved in the memory.
On the other hand when using DirectQuery, only the materialized results are cached in the memory.
We did some tests by uploading a second report non 'used' version of the same. During report usages and refresh we didn't saw a behavior of what you're suggesting. Is there any documentation on the basis what you're referring to?
Note for the Direct Query would be that you would put stress on the server capacity by making requests each time it is used. We don't know how both would size up when compared to each other.
DouweMeer I would start with these two docs.
Import: https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction
DirectQuery: https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance
