MDX Query Taking very long time when executed from SSRS

Question

**

I have a query that takes very long time when queries from an SSRS Report (25 Minutes)
When i try to execute it from SSMS i get the following error after 1 minute :
Executing the query ...
Exception of type 'System.OutOfMemoryException' was thrown.

**

SELECT NON EMPTY { [Measures].[Invoice Qty MT - VW Fact Total Sales], [Measures].[Invoice Value EGPUSD - VW Fact Total Sales] } ON COLUMNS, NON EMPTY { ([Dim Invoice Date].[Year].[Year].ALLMEMBERS * [Dim Company].[Data Area ID].[Data Area ID].ALLMEMBERS * [Dim Customer].[Customer Type].[Customer Type].ALLMEMBERS * [Dim Invoice Date].[Month Str].[Month Str].ALLMEMBERS * [Dim Invoice Date].[Month Eng].[Month Eng].ALLMEMBERS * [Dim Customer].[Customer Classification].[Customer Classification].ALLMEMBERS * [Dim Item].[Item Number].[Item Number].ALLMEMBERS * [Dim Item].[Item Name].[Item Name].ALLMEMBERS * [Dim Customer].[Customer Num].[Customer Num].ALLMEMBERS * [Dim Customer].[Customer Name].[Customer Name].ALLMEMBERS * [Dim Item].[Factory Packing Group EN].[Factory Packing Group EN].ALLMEMBERS * [Dim Item].[Factory Packaging Group ID].[Factory Packaging Group ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Dim Packing Group].[Packing Group ID].&[1], [Dim Packing Group].[Packing Group ID].&[2], [Dim Packing Group].[Packing Group ID].&[3], [Dim Packing Group].[Packing Group ID].&[4], [Dim Packing Group].[Packing Group ID].&[5], [Dim Packing Group].[Packing Group ID].&[6], [Dim Packing Group].[Packing Group ID].&[7] } ) ON COLUMNS FROM [BI_Cube]) WHERE ( [Dim Packing Group].[Packing Group ID].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

GregGalloway GregGalloway · Accepted Answer · 2019-08-26T00:58:35

First, the System.OutOfMemory error is a client-side error in SSMS which means the query returned more data than fit in memory. Try closing and reopening SSMS. Also the following query changes will help (specifically reducing the cell properties that are returned to just VALUE).

SELECT { [Measures].[Invoice Qty MT - VW Fact Total Sales], [Measures].[Invoice Value EGPUSD - VW Fact Total Sales] } ON COLUMNS, 
NON EMPTY { (
  [Dim Invoice Date].[Year].[Year].ALLMEMBERS 
* [Dim Invoice Date].[Month Str].[Month Str].ALLMEMBERS
* [Dim Invoice Date].[Month Eng].[Month Eng].ALLMEMBERS
* [Dim Company].[Data Area ID].[Data Area ID].ALLMEMBERS 
* [Dim Customer].[Customer Type].[Customer Type].ALLMEMBERS
* [Dim Customer].[Customer Classification].[Customer Classification].ALLMEMBERS
* [Dim Customer].[Customer Num].[Customer Num].ALLMEMBERS 
* [Dim Customer].[Customer Name].[Customer Name].ALLMEMBERS 
* [Dim Item].[Item Number].[Item Number].ALLMEMBERS
* [Dim Item].[Item Name].[Item Name].ALLMEMBERS
* [Dim Item].[Factory Packing Group EN].[Factory Packing Group EN].ALLMEMBERS 
* [Dim Item].[Factory Packaging Group ID].[Factory Packaging Group ID].ALLMEMBERS 
) } 
DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS 
FROM ( SELECT ( { [Dim Packing Group].[Packing Group ID].&[1], [Dim Packing Group].[Packing Group ID].&[2], [Dim Packing Group].[Packing Group ID].&[3], [Dim Packing Group].[Packing Group ID].&[4], [Dim Packing Group].[Packing Group ID].&[5], [Dim Packing Group].[Packing Group ID].&[6], [Dim Packing Group].[Packing Group ID].&[7] } ) ON COLUMNS FROM [BI_Cube]) WHERE ( [Dim Packing Group].[Packing Group ID].CurrentMember ) 
CELL PROPERTIES VALUE

Putting all attributes from a single dimension together should help performance. Also I trimmed down the CELL PROPERTIES and DIMENSION PROPERTIES to just the ones you likely use in the SSRS report.

MDX Query Taking very long time when executed from SSRS

2 Answers