从SSRS执行MDX查询需要很长时间

Question

我有一个查询，当来自SSRS报告的查询花费很长时间(25分钟)。

当我尝试从SSMS执行它时，在1分钟后得到以下错误：

执行查询..。 引发了“System.OutOfMemoryException”类型的异常。

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 

Answer 1

首先，System.OutOfMemory错误是SSMS中的客户端错误，这意味着查询返回的数据多于内存中的数据。尝试关闭并重新打开SSMS。另外，下面的查询更改也会有所帮助(特别是将返回到仅值的单元格属性减少)。

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

将单个维度的所有属性放在一起应该有助于性能。此外，我还将单元格属性和维度属性缩减为您可能在SSRS报告中使用的属性。

Answer 2

错误是不言自明的.你没有内存来计算你到达的巨大的交叉连接。

尝试删除其中的一些，更多地过滤您的数据，或者使用Properties，而不是将所有内容都包含在交联接中。

用户定义的成员属性