帮助优化MDX查询 [英] Help optimizing a MDX Query

问题描述

我是MDX的新手（不是），我需要有人来帮我解决这个问题。

I am quite new to MDX (who isn't) and I need someone to help me with this query.

GENERATE
(
  FILTER
  (
    [Dim Products].[Product].[Product].members * [Dim Date].Week.Week.members, [Measures].[Price]
  ),
  NONEMPTY
  ( 
    TOPCOUNT
    (
      [Dim Price].[Price].[Price]*[Dim Products].[Product].currentmember * [Dim Date].Week.currentmember , 1, [Measures].[Price Count]
    )
  )
)

我在仪表板工具（Dundas Dashboard）中使用上面的命名集来检索MODE（价格值重复最多）。它确实显示正确的结果，但它很慢。如果一周有过滤器则需要2-3秒，如果一周没有过滤器则需要大约6-7秒（显示所有周的数据）。这是在SSMS中，但客户端工具需要更长的时间才能获得结果集，有时会超时。



经过一些测试后，事实表中的行数似乎不会影响性能，我已大幅减少但仍然相同。然而，一旦我减少了2个尺寸表中的尺寸数量 - [Dim Price]，[Dim Products]，性能就提高了。最初它需要20多秒才能获得结果集但是一旦我减少了尺寸数量就改进了6-7秒，如下所示：

表行之前的行数

[昏暗价格] 2400 620

[昏暗产品] 1080 101



这让我觉得三维之间有一个笛卡尔积这会影响性能。



我想再次提到我是新手，因此我需要有人建议如何改进查询以提高性能。



提前谢谢！

Mihai

I am using the above Named Set in a dashboard tool (Dundas Dashboard) in order to retrieve the MODE (price value that repeats the most). It does show correct results however it is slow. It takes 2-3 seconds if there is a filter on a single week and takes about 6-7 seconds if there is no filter on week (shows data for all weeks). And this is in SSMS but the client tool takes even longer to get the result set, sometimes timing out.

After some tests it appears that the number of the rows in the fact table does not affect the performance, I've drastically decreased it but still the same. The performance increased, however, once I've decreased the number of dimensions in 2 of the dimension tables - [Dim Price], [Dim Products]. Initially it was taking 20+ seconds to get result set but improved to 6-7 seconds once I've decreased the number of dimensions as follows:
Table Rows Before Rows After
[Dim Price] 2400 620
[Dim Products] 1080 101

This makes me think there is a Cartesian Product between the 3 dimensions that is affecting the performance.

Again I want to mention that I am a newbie therefore I need someone to advise how I can improve the query to increase the performance.

Thank you in advance!
Mihai

推荐答案

有什么建议吗？我需要尽快提供帮助。

Any advise please? I need help with this as soon as possible.

这篇关于帮助优化MDX查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！