Calculation Items Errors Out In Analysis Services: When some data is being pulled from Power BI desktop/Excel, the SSAS server memory is increasing until the max memory of the VM and failed giving out of memory error.
“Executing the query …
There’s not enough memory to complete this operation. Please try again later when there may be more memory available.
Run complete“
Now if you look at this below image you can see that when I am trying to pull the calculated column in the excel, the excel goes in loop with never ending query and when I check the profiler to check the query which excel is sending, it is very complex with calculated fields.
Calculation Items Errors Out In Analysis Services
MDX performance issue when using SSAS 2019 CU4 or later with Tabular model using calculation groups and having calc item with a format string, fails wit memory out of issue.
Issue starts with SSAS 2019 CU4 or higher versions. The same mdx query which ran fine (in secs) in SSAS 2019 CU3, will not complete and cause Engine out of memory in SSAS 2019 CU4+.
Note: In some cases, I have this problem happening in virions which is below CU4, so upgrading the server above CU4 doesn’t mean that it will fix the issue. So, follow the below action plan. Want to get started with SSAS please check this doc!
If you have Format String on Calculation Group which might consume a lot of QPU and memory on MDX query. To activate this setting, set the server setting
ConfigurationSettings\Feature\CalculationGroupsOptimizations to “3” after making this change, you may need to pause and then restart the server. If your server does not have the fix deployed yet, this command will fail. This is the easiest way to check if the fix has been applied.
The above option in the server, if you are not able to see in the server properties, then please add it manually by hardcoding theses below values under the features option in “msmdsrv.ini” file as shown in the below image.
Step 1: Locate the file “msmdsrv.ini” for your SSAS server and open that with notepad to make the changes.
Step 2: Under the feature tag, you will not find the below green color highlighted option, then you copy the below code and hardcode the way it is specified in the below image.
<CalculationGroupsOptimizations>3</CalculationGroupsOptimizations>
Please restart the SSAS server to make changes effect. Hope this will fix the issue, if not please remove “<CalculationGroupsOptimizations>3</CalculationGroupsOptimizations>” and put the below code and then check the behaviour.
Other value if above option did not fix the issue
<MDXCalcGroupsOptimizationEnabled>1</MDXCalcGroupsOptimizationEnabled>
<MDXSonarOptimizationEnabled>1</MDXSonarOptimizationEnabled
Important
Please make sure to start the SSAS server from services option and it might fix the issue, also note be prepared for the potential cause when making these changes, there could be chance that it might affect the server. As per my experience I have not faced one, but if you do so, please take the backup of “msmdsrv.ini” file so that you can revert the changes.
For more about calculated groups please check out this document: Calculation groups in Analysis Services tabular models | Microsoft Learn
