Table Visualization results uncontrolled SSAS memory growth

[JS-cubelogic]

I created a report with a table visualization running against an SSAS Live Connection. The table contained 5 dimension values and 2 measures. There were many rows of data > 1000 in some cases. The report ran without issue against SQL Server 2012 Analysis Services.

When attempting to run the report against SQL Server 2016 SSAS. The Analysis Services process started to grab more and more memory until eventually the server ran out of memory.

The table visualization had totals configured ON, by turning the totals OFF the problem was resiolved.

[v-qiuyu-msft]

Hi @JS-cubelogic,

I find the same issue reported before, and you can refer to below information: 

SSAS 2016 changed behavior to avoid possible incorrect results for multi-level nested operations in DAXMD, as a result requires that we materialize some of the intermediate results which can generate very large datasets. 

The behavior seen in customer is by design, so SSAS and Power BI team are working on VSTS 9229662 to change Power BI queries in order to user SuperDAX, which will show better performance.

The workaround is continue to use SSAS 2014 or simplify queries to avoid pattern that consumes a lot of resources in SSAS 2016.

I will send a email to consult the status about VSTS 9229662. Once I get information, will update here. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @JS-cubelogic,

Where do you run the report then the issue occurs, Power BI desktop or Power BI service? 

If it's in Power BI desktop, please enable trace log, repeat the steps till error occur then share the log with us. 

If in Power Bi service, please share on-premise data gateway log ( C:\Users\PBIEgwService\AppData\Local\Microsoft\on-premises data gateway\Gateway*.log ) with us. 

Best Regards,
Qiuyun Yu 

[JS-cubelogic]

I re-created the problem running Power Bi Desktop (sept 2017) and just turning the Totals ON on the table visualization.

I enabled logging and generated a log file but it is too large to post.  The last few lines are as follows

DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.5473512Z","Action":"UISettingsStorage/SetSettings","MetadataSet":[["ShowStartPageForDataInsightsSummit2016","0"],["FileHistory",4553],["PowerBIServiceFileHistory",2],["SSRSFileHistory",2],["showStartPage","1"],["MultiArtifactEditorWindowScale_Left","100"],["MultiArtifactEditorWindowScale_Top","100"],["MultiArtifactEditorWindowScale_Width","1480"],["MultiArtifactEditorWindowScale_Height","800"],["MultiArtifactEditorWindowScale_Maximized","1"],["multiArtifactEditorRibbonQat",444],["FormulaBuilderHistory",3289],["LastKnownPBICloudConfig",2242],["ShowGridlines","1"],["DisplayCommandBar","0"],["EditorTaskPaneWidth","266"],["QueriesEditorWindowScale_Left","91"],["QueriesEditorWindowScale_Top","91"],["QueriesEditorWindowScale_Width","1498"],["QueriesEditorWindowScale_Height","856"],["QueriesEditorWindowScale_Maximized","0"],["multiArtifactQueriesEditorRibbonQat",326],["ShowConvertTableDialog","0"],["MaxCacheSize","4294967296"],["DisplayFormulaBar","0"],["UseFixedWidthFont","0"],["AlwaysAllowParameterization","0"],["ShowImportCustomVisualSecurityWarningDialog","1"],["DisableNativeDbQueries","0"],["WebPreviewWarningLevel","1"],["DisableUpdateNotification","0"],["EnableEsriMap","0"],["SnapToGrid","1"]],"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0817636"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.6292036Z","Action":"SharedLocalStorageAccessor/AcquireMutex","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0000179"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.6291889Z","Action":"UISettingsStorage/GetSettings","MetadataSet":[["ShowStartPageForDataInsightsSummit2016","0"],["FileHistory",4553],["PowerBIServiceFileHistory",2],["SSRSFileHistory",2],["showStartPage","1"],["MultiArtifactEditorWindowScale_Left","100"],["MultiArtifactEditorWindowScale_Top","100"],["MultiArtifactEditorWindowScale_Width","1480"],["MultiArtifactEditorWindowScale_Height","800"],["MultiArtifactEditorWindowScale_Maximized","1"],["multiArtifactEditorRibbonQat",444],["FormulaBuilderHistory",3289],["LastKnownPBICloudConfig",2242],["ShowGridlines","1"],["DisplayCommandBar","0"],["EditorTaskPaneWidth","266"],["QueriesEditorWindowScale_Left","91"],["QueriesEditorWindowScale_Top","91"],["QueriesEditorWindowScale_Width","1498"],["QueriesEditorWindowScale_Height","856"],["QueriesEditorWindowScale_Maximized","0"],["multiArtifactQueriesEditorRibbonQat",326],["ShowConvertTableDialog","0"],["MaxCacheSize","4294967296"],["DisplayFormulaBar","0"],["UseFixedWidthFont","0"],["AlwaysAllowParameterization","0"],["ShowImportCustomVisualSecurityWarningDialog","1"],["DisableNativeDbQueries","0"],["WebPreviewWarningLevel","1"],["DisableUpdateNotification","0"],["EnableEsriMap","0"],["SnapToGrid","1"]],"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0003141"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.6295544Z","Action":"SharedLocalStorageAccessor/AcquireMutex","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0000102"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.6301599Z","Action":"SharedLocalStorageAccessor/WriteTempFile","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0466877"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.6769074Z","Action":"SharedLocalStorageAccessor/CopyTempFileToActualFile","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0387615"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.7157180Z","Action":"SharedLocalStorageAccessor/DeleteTempFile","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0060007"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.6295236Z","Action":"UISettingsStorage/SetSettings","MetadataSet":[["ShowStartPageForDataInsightsSummit2016","0"],["FileHistory",4553],["PowerBIServiceFileHistory",2],["SSRSFileHistory",2],["showStartPage","1"],["MultiArtifactEditorWindowScale_Left","100"],["MultiArtifactEditorWindowScale_Top","100"],["MultiArtifactEditorWindowScale_Width","1480"],["MultiArtifactEditorWindowScale_Height","800"],["MultiArtifactEditorWindowScale_Maximized","1"],["multiArtifactEditorRibbonQat",444],["FormulaBuilderHistory",3289],["LastKnownPBICloudConfig",2242],["ShowGridlines","1"],["DisplayCommandBar","0"],["EditorTaskPaneWidth","266"],["QueriesEditorWindowScale_Left","91"],["QueriesEditorWindowScale_Top","91"],["QueriesEditorWindowScale_Width","1498"],["QueriesEditorWindowScale_Height","856"],["QueriesEditorWindowScale_Maximized","0"],["multiArtifactQueriesEditorRibbonQat",326],["ShowConvertTableDialog","0"],["MaxCacheSize","4294967296"],["DisplayFormulaBar","0"],["UseFixedWidthFont","0"],["AlwaysAllowParameterization","0"],["ShowImportCustomVisualSecurityWarningDialog","1"],["DisableNativeDbQueries","0"],["WebPreviewWarningLevel","1"],["DisableUpdateNotification","0"],["EnableEsriMap","0"],["SnapToGrid","1"]],"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0925570"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.9748144Z","Action":"SharedLocalStorageAccessor/AcquireMutex","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0000147"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.9747992Z","Action":"UISettingsStorage/GetSettings","MetadataSet":[["ShowStartPageForDataInsightsSummit2016","0"],["FileHistory",4553],["PowerBIServiceFileHistory",2],["SSRSFileHistory",2],["showStartPage","1"],["MultiArtifactEditorWindowScale_Left","100"],["MultiArtifactEditorWindowScale_Top","100"],["MultiArtifactEditorWindowScale_Width","1480"],["MultiArtifactEditorWindowScale_Height","800"],["MultiArtifactEditorWindowScale_Maximized","1"],["multiArtifactEditorRibbonQat",444],["FormulaBuilderHistory",3289],["LastKnownPBICloudConfig",2242],["ShowGridlines","1"],["DisplayCommandBar","0"],["EditorTaskPaneWidth","266"],["QueriesEditorWindowScale_Left","91"],["QueriesEditorWindowScale_Top","91"],["QueriesEditorWindowScale_Width","1498"],["QueriesEditorWindowScale_Height","856"],["QueriesEditorWindowScale_Maximized","0"],["multiArtifactQueriesEditorRibbonQat",326],["ShowConvertTableDialog","0"],["MaxCacheSize","4294967296"],["DisplayFormulaBar","0"],["UseFixedWidthFont","0"],["AlwaysAllowParameterization","0"],["ShowImportCustomVisualSecurityWarningDialog","1"],["DisableNativeDbQueries","0"],["WebPreviewWarningLevel","1"],["DisableUpdateNotification","0"],["EnableEsriMap","0"],["SnapToGrid","1"]],"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0003740"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.9752182Z","Action":"SharedLocalStorageAccessor/AcquireMutex","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0000085"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.9756930Z","Action":"SharedLocalStorageAccessor/WriteTempFile","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0328416"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:27.0085868Z","Action":"SharedLocalStorageAccessor/CopyTempFileToActualFile","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0447787"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:27.0534253Z","Action":"SharedLocalStorageAccessor/DeleteTempFile","ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0037913"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:26.9751955Z","Action":"UISettingsStorage/SetSettings","MetadataSet":[["ShowStartPageForDataInsightsSummit2016","0"],["FileHistory",4553],["PowerBIServiceFileHistory",2],["SSRSFileHistory",2],["showStartPage","1"],["MultiArtifactEditorWindowScale_Left","100"],["MultiArtifactEditorWindowScale_Top","100"],["MultiArtifactEditorWindowScale_Width","1480"],["MultiArtifactEditorWindowScale_Height","800"],["MultiArtifactEditorWindowScale_Maximized","1"],["multiArtifactEditorRibbonQat",444],["FormulaBuilderHistory",3289],["LastKnownPBICloudConfig",2242],["ShowGridlines","1"],["DisplayCommandBar","0"],["EditorTaskPaneWidth","266"],["QueriesEditorWindowScale_Left","91"],["QueriesEditorWindowScale_Top","91"],["QueriesEditorWindowScale_Width","1498"],["QueriesEditorWindowScale_Height","856"],["QueriesEditorWindowScale_Maximized","0"],["multiArtifactQueriesEditorRibbonQat",326],["ShowConvertTableDialog","0"],["MaxCacheSize","4294967296"],["DisplayFormulaBar","0"],["UseFixedWidthFont","0"],["AlwaysAllowParameterization","0"],["ShowImportCustomVisualSecurityWarningDialog","1"],["DisableNativeDbQueries","0"],["WebPreviewWarningLevel","1"],["DisableUpdateNotification","0"],["EnableEsriMap","0"],["SnapToGrid","1"]],"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0823357"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:27.6810713Z","Action":"AnalysisManager/CancelSessionEvaluation","PackageReference":[["64466f4e-6e62-4b56-817c-25aae408d9b2"],[6]],"RestartContainer":false,"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0007261"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:27.6818630Z","Action":"PackageSessionStorage/DeleteSession","PackageReference":[["64466f4e-6e62-4b56-817c-25aae408d9b2"],[6]],"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0010148"}
DataMashup.Trace Information: 24579 : {"Start":"2017-09-13T08:51:27.6830598Z","Action":"WorkspaceStorage/DeleteItem","PackagePath":["64466f4e-6e62-4b56-817c-25aae408d9b2"],"ProductVersion":"2.50.4859.502 (PBIDesktop)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"PBIDesktop","Pid":20240,"Tid":1,"Duration":"00:00:00.0023553"}