FIX: Out of memory occurs and query fails when you run MDX query with NON EMPTY option in SSAS

Applies to: Microsoft SQL Server 2016 Service Pack 1Microsoft SQL Server 2016 Business IntelligenceMicrosoft SQL Server 2014 Service Pack 2

Symptoms


When you run a tableau generated Multidimensional Expressions (MDX) query with the NON EMPTY option and Query-Scoped calculated member in slicer in Microsoft SQL Server Analysis Services (SSAS), out of memory occurs and the query fails.

Resolution


More information


The following is the example query for this issue:

WITH MEMBER [Measures].[Test] AS CASE WHEN IsEmpty([Date].[Date].CurrentMember.MemberValue)OR [Date].[Date].CurrentMember.MemberValue = NULL THEN NULLELSE Cdate([Date].[Date].CurrentMember.MemberValue)END SELECT [Measures].[Internet Order Count] DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,NON EMPTY [Date].[Fiscal].[Fiscal Year].ALLMEMBERS DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME], [MEMBER_CAPTION] ON ROWSFROM [Adventure Works]WHERE StripCalculatedMembers(CrossJoin({[Product].[Product Categories].[Subcategory].&[26]},Filter([Date].[Date].[Date].ALLMEMBERS,[Measures].[Test] >= Cdate(40369) AND [Measures].[Test] <= Cdate(42735))));

 

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References


Learn about the terminology that Microsoft uses to describe software updates.