locked
Out of memory when running MDX query in tabular mode

    Question

  • Hello,

    We are observing an out of memory error when running an MDX query in tabular mode when an equivalent MDX query in multidimensional mode returns in a few seconds. This problem can be reproduced using the Adventureworks sample database for both the tabular model and multidimensional model.  Below are the queries executed in tabular mode and the results

    Query in tabular mode:

    SELECT [Measures].[Internet Total Sales] ON COLUMNS,

           NON EMPTY

              NonEmpty(Crossjoin(

                  [Geography.City].[All].children,

                  NonEmpty(Crossjoin(

                         Descendants([Product.Product Name].[All],1),

                         Descendants([Date.Calendar].[All],4)

                  ))

              )) ON ROWS

    FROM [Internet Sales]

     

    Tabular Mode Result:

     

    Executing the query ...

    The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

    Execution complete

    Query in multidimensional  mode:

    SELECT [Measures].[Internet Sales Amount] ON COLUMNS,

           NON EMPTY

              NonEmpty(Crossjoin(

                  [Geography.City].[All].children,

                  NonEmpty(Crossjoin(

                         Descendants([Product.Product].[All Products],1),

                         Descendants([Date.Calendar].[All],4)

                  ))

              )) ON ROWS

    FROM [Adventure Works]

    Multidimensional Mode Result:

    Executing the query ...

    Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSet

    Formatting.

    Cell set consists of 30182 rows and 4 columns.

    Done formatting.

    Execution complete

    Note that there are slight differences in the MDX between the tabular and multidimensional modes because measure and hierarchy names are slightly different between the Adventure Work database. However I believe they are equivalent for the most part. Also the query in multidimensional mode returned in about 8 seconds while running for several minutes in tabular mode before issuing the error.  We are also using the 64 bit version in both cases.

    We believe the difference in behavior has to do with how the NonEmpty function is interpreted in the tabular mode. It appears that tabular mode does not remove the null when NonEmpty function is specified. However if the NON EMPTY keyword is specified the nulls are removed in the tabular model. The following query in tabular mode returns all the days in January 2003 including nulls even though NonEmpty function is specified:

    SELECT {[Measures].[Reseller Total Sales]} ON COLUMNS,

                  NonEmpty(

                         [Date.Calendar].[All].[2003].[1].[1].[ 1 - January].children

              ) ON ROWS

    FROM [Model]

    If the NON EMPTY keyword is used instead of the NonEmpty function the nulls are removed:

    SELECT {[Measures].[Reseller Total Sales]} ON COLUMNS,

                  NON EMPTY

                         [Date.Calendar].[All].[2003].[1].[1].[ 1 - January].children

               ON ROWS

    FROM [Model]

    Has anyone else observed similar behavior with the tabular model ? Thank you for your help.

    Wednesday, December 07, 2011 8:20 PM

All replies

  • It appears that specifying the optional second argument for the NonEmpty function will improve the performance of the query in tabular mode.  The second argument can be the measure used to determine which cells are not empty. For example the following query will return only the non empty rows where [Measures].[Reseller Total Sales] is not null

    SELECT {[Measures].[Reseller Total Sales]} ON COLUMNS,

                  NonEmpty(

                         [Date.Calendar].[All].[2003].[1].[1].[ 1 - January].children, [Measures].[Reseller Total Sales]

              ) ON ROWS

    FROM [Model]

     

    Similarly with the query which did not return earlier if we add the measure to optional parameter of the non empty call we get results back (12 seconds) instead of the memor error:

     

    SELECT [Measures].[Internet Total Sales] ON COLUMNS,

           NON EMPTY

              NonEmpty(Crossjoin(

                  [Geography.City].[All].children,

                  NonEmpty(Crossjoin(

                         Descendants([Product.Product Name].[All],1),

                         Descendants([Date.Calendar].[All],4)

                  ), [Measures].[Internet Total Sales] )

              ), [Measures].[Internet Total Sales] ) ON ROWS

    FROM [Model]

     

    The number of rows returned by this query (45,012) is greater than the number of rorws returned by the multidimensional instance. Is there a way to specify a default measure in a tabular instance similar to the way you can specify the default measure acube in a multidimensional instance ?

    Thursday, December 08, 2011 8:12 PM
  • The DAX equivalent of the query which returns the large number of rows comes back almost immediately (1 sec). Below is the DAX query

     

    EVALUATE SUMMARIZE( 'Internet Sales',            
    Geography[City],   
    Product[Product Name], 
    'Date'[Month Calendar],       
    "Internet Total Sales", 'Internet Sales'[Internet Total Sales]) 
    ORDER BY Geography[City], Product[Product Name],'Date'[Month Calendar]

    It appear there is a substantial difference in performance when using MDX on an Analysis Services tabular instance.
     

    Thursday, December 08, 2011 9:31 PM
  • Hi Robin, Can you pls. tell me Build of SQL 12. is it RC0 or?

    Karan

     

    Friday, December 09, 2011 7:18 AM
  • This was observed with RC0 (11.0.1750.32).

    Thanks,

    Robin

    Friday, December 09, 2011 4:28 PM
  • Let’s talk about first issue:

     

    ·         [MDX Query]

    ·         SELECT [Measures].[Internet Total Sales]ON COLUMNS,

    NON EMPTY

    NonEmpty(Crossjoin(

    [Geography.City].[All].children,

    NonEmpty(Crossjoin(

    Descendants([Product.Product Name].[All],1),

    Descendants([Date.Calendar].[All],4)

    ))

    ))ON ROWS

    FROM [Internet Sales]

     

    Failing with Memory Error in Tabular mode but takes only 8 seconds to complete in MOLAP MODE

     

    Assessment –

    You did a good job in constricting the issue to NonEmpty(s1,s2) function based on your testing you feel that issue is happening due to NonEmpty function in tabular mode and you are correct it’s not only Tabular Mode same behavior will appear in case of MOLAP Mode issue.

     

    [MOLAP]

    Small test to understand the behavior of nonempty(s1,s2) .

     

    select {[Measures].[Internet Sales Amount],[Measures].defaultmember} on 0

    ,

    ([Date].[Calendar].[Calendar Quarter]) on 1

    from [Adventure Works]

     

     

    Internet Sales is Null only for Q4Cy 2010

    DefaultMember- Reseller Sales is Null for Q3 2008 and Q4 2010.

     

    Now let’s fire another query

    select {[Measures].[Internet Sales Amount]} on 0

    ,nonempty ([Date].[Calendar].[Calendar Quarter]) on 1

    from [Adventure Works]

     

    If you notice in this query - nonempty([Date].[Calendar].[Calendar Quarter]), though at 0<sup> </sup>Axis we have Internet Sales but internally nonempty() is considering Set2 – Measures.DefaultMember because we haven’t explicitly stated Set2 consequently we are getting result based on this - nonempty([Date].[Calendar].[Calendar Quarter],[Measures].defaultmember) and we have data for DefaultMember(Reseller Sales) only till Q2CY2008 that’s why cell value for Q32008 is not appearing for Internet Sales, to override defaultmember we specify second set which is internet sales….

    select {[Measures].[Internet Sales Amount]} on 0

    ,

    nonempty ([Date].[Calendar].[Calendar Quarter],[Measures].[Internet Sales Amount]) on 1

    from [Adventure Works]

     

    Here you go – we are getting nonempty result based nonempty cells in Internet Sales.

    This behavior applies to MOLAP and Tabular, but now question is Default Member – unfortunately Tabular Model doesn’t let you set the default measure explicitly so it must be using some random measure that’s why you need to explicitly state the second set in nonempty.

    If a second set is not provided, the expression is evaluated in the context of the current coordinates of the members of the attribute hierarchies and the default measure in the cube”

    Saturday, December 10, 2011 1:21 AM