locked
SSAS: Server memory reached 100% while executing below query RRS feed

  • Question

  • Hi All,

    While executing the bellow mdx query my server memory reached to 100% and getting time out. but if i remove any column, query will finished with in few seconds.

    can some one help me to resolve this issue, if there is any column or row limit on mdx queries 

    SELECT NON EMPTY {
    [Measures].[Qty Blocked],
    [Measures].[Qty Unrestricted],
    [Measures].[Qty Quality],
    [Measures].[Qty Total],
    [Measures].[Qty Restricted],
    [Measures].[Qty Returns] } ON COLUMNS,
      NON EMPTY { (
    NONEMPTY([Stock Attributes].[Material No].[Material No].ALLMEMBERS) *
    NONEMPTY([Batch Classification].[Vendor Batch NUMBER].[Vendor Batch NUMBER].ALLMEMBERS) *
    NONEMPTY([Material Master].[Material DESC].[Material DESC].ALLMEMBERS) *
    NONEMPTY([Stock Attributes].[S Loc].[S Loc].ALLMEMBERS) *--
    NONEMPTY([Stock Attributes].[S Loc DESC].[S Loc DESC].ALLMEMBERS) *
    NONEMPTY([Material Master].[Basic Data Text].[Basic Data Text].ALLMEMBERS) *
    NONEMPTY([Material Master].[Sales Text].[Sales Text].ALLMEMBERS)
      ) } DIMENSION PROPERTIES MEMBER_CAPTION,
      MEMBER_UNIQUE_NAME ON ROWS    
    FROM [InventoryA] CELL PROPERTIES VALUE,
      BACK_COLOR,
      FORE_COLOR,
      FORMATTED_VALUE,
      FORMAT_STRING,
      FONT_NAME,
      FONT_SIZE,
      FONT_FLAGS

    Thursday, August 29, 2013 9:46 AM

Answers

  • Hello ,

       As the number of cross join is more you are getting this issue.Perform a nonempty something like below.

    SELECT NON EMPTY { 
    [Measures].[Qty Blocked],
     [Measures].[Qty Unrestricted],
     [Measures].[Qty Quality],
     [Measures].[Qty Total],
     [Measures].[Qty Restricted],
     [Measures].[Qty Returns] } ON COLUMNS,
       NONEMPTY( { (
     ([Stock Attributes].[Material No].[Material No].ALLMEMBERS) *
     ([Batch Classification].[Vendor Batch NUMBER].[Vendor Batch NUMBER].ALLMEMBERS) *
     ([Material Master].[Material DESC].[Material DESC].ALLMEMBERS) *
     ([Stock Attributes].[S Loc].[S Loc].ALLMEMBERS) *--
     ([Stock Attributes].[S Loc DESC].[S Loc DESC].ALLMEMBERS) *
     ([Material Master].[Basic Data Text].[Basic Data Text].ALLMEMBERS) *
     ([Material Master].[Sales Text].[Sales Text].ALLMEMBERS)
       ) } ,[Measures].[Qty Total])DIMENSION PROPERTIES MEMBER_CAPTION,
       MEMBER_UNIQUE_NAME ON ROWS    
     FROM [InventoryA] CELL PROPERTIES VALUE,
       BACK_COLOR,
       FORE_COLOR,
       FORMATTED_VALUE,
       FORMAT_STRING,
       FONT_NAME,
       FONT_SIZE,
       FONT_FLAGS

    • Marked as answer by Robb123 Friday, August 30, 2013 2:46 AM
    Thursday, August 29, 2013 10:13 AM

All replies

  • Hello ,

        Please remove the nonempty you have used multiple times in the cross join.

    THank you!!

    Regards,

    Bharath

    Thursday, August 29, 2013 9:50 AM
  • As a work around i did that. without nonempty  also i got the same issue
    Thursday, August 29, 2013 9:53 AM
  • Hello ,

       As the number of cross join is more you are getting this issue.Perform a nonempty something like below.

    SELECT NON EMPTY { 
    [Measures].[Qty Blocked],
     [Measures].[Qty Unrestricted],
     [Measures].[Qty Quality],
     [Measures].[Qty Total],
     [Measures].[Qty Restricted],
     [Measures].[Qty Returns] } ON COLUMNS,
       NONEMPTY( { (
     ([Stock Attributes].[Material No].[Material No].ALLMEMBERS) *
     ([Batch Classification].[Vendor Batch NUMBER].[Vendor Batch NUMBER].ALLMEMBERS) *
     ([Material Master].[Material DESC].[Material DESC].ALLMEMBERS) *
     ([Stock Attributes].[S Loc].[S Loc].ALLMEMBERS) *--
     ([Stock Attributes].[S Loc DESC].[S Loc DESC].ALLMEMBERS) *
     ([Material Master].[Basic Data Text].[Basic Data Text].ALLMEMBERS) *
     ([Material Master].[Sales Text].[Sales Text].ALLMEMBERS)
       ) } ,[Measures].[Qty Total])DIMENSION PROPERTIES MEMBER_CAPTION,
       MEMBER_UNIQUE_NAME ON ROWS    
     FROM [InventoryA] CELL PROPERTIES VALUE,
       BACK_COLOR,
       FORE_COLOR,
       FORMATTED_VALUE,
       FORMAT_STRING,
       FONT_NAME,
       FONT_SIZE,
       FONT_FLAGS

    • Marked as answer by Robb123 Friday, August 30, 2013 2:46 AM
    Thursday, August 29, 2013 10:13 AM