SSAS Excel查询性能真的很慢 - 我似乎知道为什么但不确定如何修复 [英] SSAS Excel query performance really slow - I seem to know why but not sure how to fix

问题描述

大家好，

我们有一些excel数据透视表报告在连接到我们的SSAS 2008和2014服务器上的多维数据集时执行速度非常慢。我想我知道问题是什么，因为我修改了MDX查询，它在服务器上运行得更快。该问题与为报告选择
财务年度日历范围有关。例如，当用户选择2010/11至2012/13的财务年度范围时，excel会生成包含以下代码的MDX代码（在SQL事件探查器中捕获）：

We have a few excel pivot table reports performing very slowly when connected to a cube on our SSAS 2008 and 2014 server. I think I know what the issue is as I modified the MDX query and it ran much faster on the server. The issue is related to selecting financial year calendar ranges for the reports. When the user selects a financial year range from 2010/11 to 2012/13 for example, excel generates MDX code containing the following code (as captured in SQL Profiler):

...

SELECT（{

[日历]。[日历]。[Qtr标签]。& [2010年第3季度]，

[Calendar].[Calendar].[Qtr Label].&[Qtr 3, 2010],

[日历]。[日历]。[Qtr标签]。& [2010年4月4日]，            

[Calendar].[Calendar].[Qtr Label].&[Qtr 4, 2010],             

[日历] [日历] [年货号]安培;。[2011]，           

[Calendar].[Calendar].[Year Num].&[2011],          

[日历] [日历] [QTR标签]安培;。[QTR 1，2012]，        &NBSP ; 

[Calendar].[Calendar].[Qtr Label].&[Qtr 1, 2012],          

[日历]。[日历]。[Qtr标签]。& [Qtr 2,2012]}      

[Calendar].[Calendar].[Qtr Label].&[Qtr 2, 2012]}      

）ON COLUMNS，

) ON COLUMNS,

这很慢，运行excel报告大约需要15分钟。即使我在SSMS中直接在服务器上运行代码，也需要相似的时间。但是，如果我修改代码并在SSMS中的服务器上运行以下代码，则需要2分钟：

This is slow, and takes about over 15min to run the excel report. Even if I run the code directly on the server in SSMS, it takes similar time. However if I modify the code and run the following on the server in SSMS, it takes 2 min:

... 

选择（{

[日历]。[日历]。[Qtr标签]。& [2010年第3季度]，        ;  

[Calendar].[Calendar].[Qtr Label].&[Qtr 3, 2010],          

[日历]。[日历]。[Qtr标签]。& [2010年4月4日]，           

[Calendar].[Calendar].[Qtr Label].&[Qtr 4, 2010],             

[日历] [日历] [QTR标签]安培;。[QTR 1，2011]，       &NBSP ;  

[Calendar].[Calendar].[Qtr Label].&[Qtr 1, 2011],          

[日历] [日历] [QTR标签]安培;。[季度2，2011]，           

[Calendar].[Calendar].[Qtr Label].&[Qtr 2, 2011],          

[日历] [日历] [QTR标签]安培;。[QTR 3，2011]，        &NBSP ;  

[Calendar].[Calendar].[Qtr Label].&[Qtr 3, 2011],             

[日历]。[日历]。[Qtr标签]。& [2011年4月4日]，           

[Calendar].[Calendar].[Qtr Label].&[Qtr 4, 2011],          

[日历]。[日历]。[Qtr标签]。& [Qtr 1,2012]，       & NBSP;  

[Calendar].[Calendar].[Qtr Label].&[Qtr 1, 2012],          

[日历]。[日历]。[Qtr标签]。& [Qtr 2,2012]

[Calendar].[Calendar].[Qtr Label].&[Qtr 2, 2012]

）ON COLUMNS，...

) ON COLUMNS, ...

时间就好像我分别为2010/11年和2011/12年度分别报告，每次报告1分钟，因此总计为2分钟。所以它线性扩展。我已经测试了这个更宽的日历范围，并且它以这种方式运行。但是，excel不会生成这个优化的代码，并且会在脚本中包含[Year Num]，这会导致运行时间变慢。

The timing is as if I run a report for 2010/11 and another for 2011/12 separately, each taking 1 min, and therefore combined is 2 minutes. So it scales linearly. I have tested this for wider calendar ranges and it behaves in this fashion. However excel does not generate this optimized code, and resorts to including [Year Num] in the script which causes slow run times.

In excel，当用户从Calendar PivotTable字段中选择日历范围时，似乎是一个层次结构：

In excel, when the user selects a calendar range from the Calendar PivotTable field, is seems to be a hierarchy:

2011 -

2011 -

     Qrt 1,2011

     Qrt 1, 2011

     2011年第2季度

     Qrt 2, 2011

     2011年第3季度

     Qrt 3, 2011

     Qrt 4,2011

     Qrt 4, 2011

如果他们选择2011，则选择所有Qrt，但Qrt不会发送到脚本，仅2011年将作为[Year Num]发送到脚本。

If they select 2011, all the Qrt's are selected, but the Qrt's are not sent to the script, only 2011 alone is sent to the script as [Year Num].

我不明白为什么我们得到的结果很慢。也许是因为我们在脚本中的层次结构中包含混合级别[Qtr Label]和[Year Num]，并且SSAS在某种程度上变得混乱。但是，如果我们只包含[Qtr Label]它会在
级别运行并且不会混淆。

I don't understand why we are getting slow results. Maybe because we are including mixed levels [Qtr Label] and [Year Num] within a hierarchy in the script and SSAS is getting confused somehow. If however we just include [Qtr Label] it runs things at that level and isn't confused.

有人知道如何解决这个问题吗？这是可以在Excel中配置的，还是我必须在SSAS上修复日历层次结构，或者更好的解决方案是在SSAS多维数据集中包含财务年度层次结构？任何帮助
解决这个问题都会很棒。

Would anyone know how this can be fixed? Is this something that can be configured in Excel, or will I have to fix the Calendar hierarchy on SSAS, or possibly would the better solution be to include a Financial Year hierarchy on the SSAS cube? Any help in resolving this would be fantastic.

谢谢。

推荐答案

你好fuzz7，

Hi fuzz7,

感谢您的提问。

根据您的描述，问题更可能与立方体结构设计相关。基于此，您需要重新配置维度表"日历"，并设置相关属性 层次结构。有关详细信息，请参阅：

Per your description, the issue more likely related cube structure design. Based on this, you need to reconfigure the dimension table "Calendar", and set related attribute hierarchy. For more details, please refer:

Analysis服务查询性能十大最佳实践（优化立方体和度量组设计）

Analysis Services Query Performance Top 10 Best Practices(Optimize cube and measure group design)

最好的问候，

将

这篇关于SSAS Excel查询性能真的很慢 - 我似乎知道为什么但不确定如何修复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！