MDX 查询运行缓慢，但仅限于某些安排
我以编程方式生成以下格式的 MDX 查询：
WITH
MEMBER [Measures].[MTD] AS
SUM ({[Time].[Calendar].[Date].&[2011-11-01T00:00:00]:[Time].[Calendar].[Date].&[2011-11-30T00:00:00]
* {[Dimension1].[Dimension1].[Val1]}
* {[Dimension2].[Dimension2].[Val2]}
* {[Dimension3].[Dimension3].[Val3]}
* {{[Dimension4].[Dimension4].[XXXX],[Dimension4].[Dimension4].[DBS],[Dimension4].[Dimension4].[FD],[Dimension4].[Dimension4].[GSCFDASIA],[Dimension4].[Dimension4].[FDEM],[Dimension4].[Dimension4].[DX],[Dimension4].[Dimension4].[OSWP],[Dimension4].[Dimension4].[OOO],[Dimension4].[Dimension4].[CFD]}}
* {-{[Dimension5].[Dimension5].[SELI],[Dimension5].[Dimension5].[SELE],[Dimension5].[Dimension5].[MRDN],[Dimension5].[Dimension5].[WCSG],[Dimension5].[Dimension5].[NEWB],[Dimension5].[Dimension5].[VASS]}}
}, [Measures].[Trade Count])
如您所见，这是创建维度值交集的简单方法，并且它已经运行良好一段时间了。
最近，我重写了多维数据集，从使用具有许多属性的单个维度表，到为每个维度使用单独的表（出于处理性能的原因）。
问题是，当我同时按维度 4 和维度 5 进行过滤时，突然之间真的慢的！如果我注释掉维度 4 和 5，查询需要一秒钟。如果我取消注释维度 4 查询需要 40 秒。如果我取消注释维度 5 行，查询需要 8 分钟。
维度通过 ID 字段 (int) 连接到度量，
维度有 238 个值
如果我执行简单查询，则
：选择列计数， {-{[维度5].[维度5].[SELI],[维度5].[维度5].[SELE],[维度5].[维度5].[MRDN],[维度5].[维度5].[WCSG] ,[Dimension5].[Dimension5].[NEWB],[Dimension5].[Dimension5].[VASS]}} 对于 MyCube 中的行，
查询需要亚秒的时间。这是因为维度的组合（在顶部）导致它变得如此缓慢。不知道如何解决它。
可能值得注意的是，我的多维数据集有 13 个分区，并且我在设置分区时没有设置“分区聚合”步骤。
I am programmaticly generating a MDX query in the following format:
WITH
MEMBER [Measures].[MTD] AS
SUM ({[Time].[Calendar].[Date].&[2011-11-01T00:00:00]:[Time].[Calendar].[Date].&[2011-11-30T00:00:00]
* {[Dimension1].[Dimension1].[Val1]}
* {[Dimension2].[Dimension2].[Val2]}
* {[Dimension3].[Dimension3].[Val3]}
* {{[Dimension4].[Dimension4].[XXXX],[Dimension4].[Dimension4].[DBS],[Dimension4].[Dimension4].[FD],[Dimension4].[Dimension4].[GSCFDASIA],[Dimension4].[Dimension4].[FDEM],[Dimension4].[Dimension4].[DX],[Dimension4].[Dimension4].[OSWP],[Dimension4].[Dimension4].[OOO],[Dimension4].[Dimension4].[CFD]}}
* {-{[Dimension5].[Dimension5].[SELI],[Dimension5].[Dimension5].[SELE],[Dimension5].[Dimension5].[MRDN],[Dimension5].[Dimension5].[WCSG],[Dimension5].[Dimension5].[NEWB],[Dimension5].[Dimension5].[VASS]}}
}, [Measures].[Trade Count])
As you can see, it's a simple way to create an intersection of dimensional values, and it has been working great for some time.
Recently i rewrote the cube from using a single dimension table with many attributes, to using a separate table for each dimension (for processing performance reasons)
The problem, is that when i filter by Dimension4 and Dimension5 together, it is all of a sudden really slow! if i comment out dimension4 and 5, the query takes a second.. if i un-comment Dimension4 query takes 40 seconds.. if i then un-comment Dimension5 line, the query takes 8 minutes.
the dimension is joined to the measure by ID field (int)
the dimension has 238 values
if i do a simple query:
select count on Columns, {-{[Dimension5].[Dimension5].[SELI],[Dimension5].[Dimension5].[SELE],[Dimension5].[Dimension5].[MRDN],[Dimension5].[Dimension5].[WCSG],[Dimension5].[Dimension5].[NEWB],[Dimension5].[Dimension5].[VASS]}} on rows from MyCube
the query takes sub second. It is something about having a combination of dimensions (up top) that is making it so painfully slow. No idea how to troubleshoot it.
something that may be worth noting, is that my cube has 13 partitions, and i have not set up "partition aggregation" step while setting up partitions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
经过大量试验和错误后，我的查询在 6 分钟内得到了处理（原来是 3.5 小时）。
有两件事让我能够以这种方式优化它：
1。在日期范围中使用月/年成员而不是日期
之前：
之后
这是巨大的。由于 OLAP 将每个日期成员视为单独的实体，因此它将范围内的所有日期与所有其他过滤器交叉连接。就我而言，我想要一整月或一整年，所以不需要日期范围。
2.将所有通用过滤器（在所有成员中）放入通用 WHERE 子句
在我的例子中，我有相当多的成员共享相同的过滤器。只需将它们放入查询底部，速度就又提高了 40%。
最后，我的查询时间从 4 小时缩短到 6.5 分钟。我可以通过在多维数据集定义中添加适当的聚合来进一步加快速度。但还没有到达那部分。
After a lot of trial and error, I got my query to process in 6 minutes (from 3.5 hours).
2 things allowed me to optimize it this way:
1. Use Month/Year members in Date Range instead of Dates
Before:
After
This was huge. Since OLAP treats each date member as a separate entity, it cross joins all the dates in the range, with all other filters. In my case, i wanted a full month, or full year, so there was no need for a date range.
2. Put all common filters (in all the members) into common WHERE clause
In my case, i had quite a few members sharing the same filters. Simply putting them into the bottom of the query, sped things up another 40%.
In the end, my query went from 4 hours, to 6.5 minutes. I can further speed it up by adding appropriate aggregations in my cube definition. but didn't get to that part yet.
你能尝试一下这个版本吗？ :
[Dimension5] 前面的 - 符号是错误的吗？
它使交叉连接变得更小，并将单个值放入元组中。如果这可以提高性能，那么这是个好问题。
Can you try with this version ? :
The - sign before [Dimension5] it's a mistake ?
It's making the crossjoin a bit smaller and putting single values in the tuple. Good questions if this improves performance.