Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JS-cubelogic
Regular Visitor

Table Visualization results uncontrolled SSAS memory growth

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.

 

 

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-qiuyu-msft
Community Support
Community Support

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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"}

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors