Forum Discussion
Power BI RAM Issues
If your model size is 500MB (is not same as file size, because file is double compressed), you need 500MB of RAM for model, + additional 500MB during processing, + additional RAM for source data (it can be much larger, because it is uncompressed raw data, but it's limited up two 8milions segments which processed parallely). Is not count easy, best way is check memory allocation during idle, and memory allocation during processing peak.
Also depends on number of reports, if you have 500MB model you need 500MB constantly, +e.g. 2GB during processing. But if you have 10 reports by 500MB, you need 5GB constantly, + and probably 2GB for processing (because you will not process everything at same time)
Thanks josef78 very much,
So which means to do Import Method via Reporting Server, this is the process of consumption for RAM.
And it all depends upons Import Reports size from Reporting Server and based on RAM(64 GB) in Server(and also depends upon Power Query steps, SQL Query writing as well) which means it should not be enough because currently already 80% consumption happening in Development area..May be its not great way to this process import method if we don't extend RAM.
Thanks for making me understanding this.
And what if we access Power BI Import process via Service, is it same consumption of RAM in Production Server? may be not for Power Query steps because it will take our local memory isn't it? and for SQL query in Import Method will it take from Server Memory?
We just worried of Data Governance to make sure data is not going outside like end users should not export before we move to service and also our reporting service sits in Citrix currently so we want to take steps carefully before we move to service.
Any inputs highly appreciated.
Yes, generally, amount of RAM which you need for model (after processing) on your development in Power BI Desktop, you will need also on production environment. And amount of additional RAM which you need during processing in Power BI Desktop, you will need also on production environment (if you will process on same server).
But, RAM required for processing is not very dependent on PowerQuery steps, more important is tabular compression (there not big difference if powerquery rewrite to sql, except filtering rows and collumns in case when query folding cannot be applied).
Eg. If you have table with 100mil rows with source size 10GB, and this table, after columnar compression, will take e.g. 500MB. You will need:
500MB RAM anytime.
and additional during processing:
+500MB RAM for working version
+1.6GB RAM for raw data (because it is process sequentially by two 8mil segments (segment size 8/100mil*10GB=0,8GB in this case)
it's mean you will need 500GB in idle, and 2,6GB during processing, in this case.
Be aware about loading large data which will not be used (columns or rows filtered-out during power query, or columns which you don't need in model), is better filter-out unnecessary data during query (by custom sql query, or db view, or also in powerquery with query folding (which translate power query steps down to sql)
Additional tips:
For large environments is recommended use scale-out deployment, and separate front-end power bi report servers and dedicated power bi report servers for processing.
For large models is better use dedicated SSAS instance for models with live connected PBI reports.