Excel 数据透视表 MDX 速度慢？

Question

我遇到了一个非常困难的时期，因为 Excel 与数据透视表交互时速度非常慢。添加/删除字段、更改过滤器或切片器都需要 Excel 冻结几分钟才能响应。

看来生成的 MDX 效率极低。我可以理解他们必须动态生成 MDX 并且必须支持数据透视表的许多功能，但是慢 100 倍是荒谬的。

当他们为行或列上的字段生成 MDX 时，他们使用 DrilldownLevel(... [Property Dimension].[County])

我不确定 Excel 更复杂的方法的目的是什么，但我希望有我可以取消选中某些选项，以便 Excel 不需要使用 DrilldownLevel 函数。

相反，我通常省略 Drilldownlevel 函数，只执行 [Property Dimension].[County].[County] 来访问属性。

使用 Excel 的 MDX 查询相同的结果集需要 5 分钟，而使用我的 MDX 则需要不到 5 秒。

我已经验证缓慢不是 Excel 渲染/格式化结果的问题，因为我采用了 Excel 使用的 MDX 并直接在 SSMS 中运行它来验证计时。我可以查看服务器上的任务管理器，并观察 CPU 在处理结果时的运行情况。

请注意，我并不是责怪服务器，因为我可以创建运行速度极快并提供相同结果的 MDX 查询。

如何让 Excel 生成更高效的 MDX？我正在使用 Excel 2010。

我听说 powerpivot 生成更高效的 MDX，但是 Powerpivot 无法在 SSAS 之上使用，因为它不利用 SSAS 多维数据集。因此，我要简短地解释一下为什么 SSAS 之上的 Powerpivot 不起作用。如果将数据从 SSAS 导入到 powerpivot 中，那么您实际上要做的就是执行巨大的交叉联接，以将数据从 SSAS 迁移到 Powerpivot 表中。如果您尝试过此操作，您会发现它会生成字段名称/标签，例如“Property DimensionCountyCounty Name”...哇，真的吗？然后，您只需使用本地 Powerpivot 的 OLAP 引擎处理数据，因此需要依赖具有 64 位操作系统的客户端计算机才能处理合理大小的数据集。就好像您只是放弃了 SSAS，扔掉了构建复杂的 OLAP 数据库以及所有元数据、计算、聚合等的所有辛苦工作。使用 SSAS 的一半原因是它可以汇总粒度数据在返回给客户端之前，客户端不需要64位操作系统，也不需要客户端占用大量资源。我非常努力地尝试让 powerpivot 针对 SSAS 可用，但在尝试了多种方法并与用户反复交流后，它确实离可用还差得很远。不要批评 Powerpivot，因为我认为它在许多其他场景中都很有用，但是如果您的 SSAS 多维数据集是系统的重要组成部分（即计算、在服务器端聚合大量记录等），那么 powerpivot 似乎是错误的选择。

以下是我的查询示例：

SELECT 
NON EMPTY CrossJoin(
{[Department Dimension].[Name].[Name]},
 {[Finance Month].[Report Year].[Report Year]}
 )
  ON COLUMNS , 
 CrossJoin(
   {[Department Finance Line Type Dimension].[Display Order].[Display Order] }, 
   {[Department Finance Line Type Dimension].[Line Number].[Line Number]},
   {[Department Finance Line Type Dimension].[Display Name].[Display Name]}
   ) 
   ON ROWS  
   FROM 
   (
   SELECT ({[Department Dimension].[County].&[Seminole],[Department Dimension].[County].&[Sarasota]}) ON COLUMNS  FROM [HYP Data View]
   ) 
   WHERE ([Department Finance Line Type Dimension].[Section Name].&[Part 1 - Balance Sheet],
   [Measures].[Amount]
   ) CELL PROPERTIES VALUE

下面是 Excel 生成的内容。实际上，当我尝试简化 Excel 查询以确定罪魁祸首时，我已经删除了 Excel 查询的其他几个方面。这就是查询仍然运行缓慢时的样子，然后我采取的下一步是删除 DrilldownLevel 并将 .[All] 替换为 .[属性名称]，它开始运行很多快得多。

非常非常慢的查询：

SELECT 
NON EMPTY CrossJoin(
{DrilldownLevel({[Department Dimension].[Name].[All]})},
 {DrilldownLevel({[Finance Month].[Report Year].[All]})}
 )
 DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , 
 CrossJoin(
   {DrilldownLevel({[Department Finance Line Type Dimension].[Display Order].[All] })}, 
   {DrilldownLevel({[Department Finance Line Type Dimension].[Line Number].[All]})},
   {DrilldownLevel({[Department Finance Line Type Dimension].[Display Name].[All]})}
   ) 
   DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  
   FROM (
   SELECT ({[Department Dimension].[County].&[Seminole],[Department Dimension].[County].&[Sarasota]}) ON COLUMNS  FROM [Afr Data View]
   ) 
   WHERE ([Department Finance Line Type Dimension].[Section Name].&[Part 1 - Balance Sheet],
   [Measures].[Amount]
   ) CELL PROPERTIES VALUE

为什么需要 DrilldownLevel(...[All])？是否有一个选项可以让 Excel 不生成查询的该部分，以便它运行得更快？

Answer 1

不幸的是，尽管我在 SSAS MOLAP 多维数据集上广泛使用 Excel 和数据透视表，但我尚未在纯 Excel 中找到一种机制来更改其生成的查询。

不过，我使用了一个名为 XLCubed 的第三方工具，该工具在处理来自 SSAS OLAP 多维数据集的 Excel 和 MDX 查询时非常强大，我可以建议您根据自己的目的尝试一下吗？您甚至可以将用于“网格”的 MDX 替换为如上所述的完全自定义的 MDX。尽管我发现这通常不是必需的，但它们对数据透视表“网格”的替代似乎通常会生成更高效的 MDX。

愿 SSAS 之神与你同在。