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

Taikoma: SQL Server 2016 Service Pack 1SQL Server 2016 Business IntelligenceSQL 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


This issue is fixed in the following cumulative updates for SQL Server:

       Cumulative Update 7 for SQL Server 2017

       Cumulative update 8 for SQL Server 2016 SP1

       Cumulative Update 11 for SQL Server 2014 SP2

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.