快速动态命名集计算 [英] Fast dynamic named set calculation
问题描述
我有一个很长的复杂查询,其中包含许多计算和条件,但主要结构如下:
WITH
MEMBER [Id1] AS [Level].[Level1].CurrentMember.Member_Key
MEMBER [Id2] AS [Level].[Level2].CurrentMember.Member_Key
MEMBER [Level].[Level1].[FirstSet] AS NULL
MEMBER [Level].[Level1].[SecondSet] AS NULL
SET [Set 1] AS {some processed set members}
SET [Set 2] AS {some other processed set members}
SET [Common CrossJoin Set] AS [Level].[Level2].Members
MEMBER [Calculated Measure 1] AS
IIF([Level].[Level].CurrentMember.Member_Key = 'FirstSet',
SUM(existing [Set 1]),
IIF([Level].[Level].CurrentMember.Member_Key = 'SecondSet',
SUM(existing [Set 2]),
SUM([Measures].[Measure1]) * 15
)
)
MEMBER [Calculated Measure 2] AS
IIF([Level].[Level].CurrentMember.Member_Key = 'FirstSet',
SUM(existing [Set 1]),
IIF([Level].[Level].CurrentMember.Member_Key = 'SecondSet',
SUM(existing [Set 2]),
SUM([Measures].[Measure2]) * 20
)
)
SELECT
{ [Id1], [Id2], [Calculated Measure 1], [Calculated Measure 2]} ON COLUMNS,
{ ([Common CrossJoin Set], [Level].[Level1].[FirstSet]),
([Common CrossJoin Set], [Level].[Level1].[SecondSet])
} ON ROWS
FROM [Cube]
结果表如下:
║---------------║---------------------------║Id1║ Id2║Measure1║Measure2║
║L2成员║L1.FirstSet成员║L2-1║L1-8║1║5║
║L2成员║L1.FirstSet成员║L2-2║L1-9║2║6║
║L2成员║L1.SecondSet成员║L2-3║L1-98║3║7║
║L2成员║L1.SecondSet成员║L2-4║L1-99║4║8║
结果正确,但查询速度非常慢(> 4sec).我的实际查询更大,并且包含许多此类Sets和measures,因此似乎问题出在现有功能和总体结构上,从而阻止了引擎执行内部优化.
这种解决方案是错误且丑陋的,但是我该如何重写它并更快地获得相同的结果?
我怀疑瓶颈是因为当您使用Iif
时,两个逻辑分支都不是NULL
,因此您无法获得块模式的计算:是使用Iif
:Iif(someBoolean, X, Null)
或Iif(someBoolean, Null, x)
的更好方法,但是不幸的是,在任何情况下,您都不能使用null.
也许您可以尝试实施Mosha建议的用于替换Iif
的这种模式:
WITH
MEMBER Measures.[Normalized Cost] AS [Measures].[Internet Standard Product Cost]
CELL CALCULATION ScopeEmulator
FOR '([Promotion].[Promotion Type].&[No Discount],measures.[Normalized Cost])'
AS [Measures].[Internet Freight Cost]+[Measures].[Internet Standard Product Cost]
MEMBER [Ship Date].[Date].RSum AS Sum([Ship Date].[Date].[Date].MEMBERS), SOLVE_ORDER=10
SELECT
[Promotion].[Promotion Type].[Promotion Type].MEMBERS on 0
,[Product].[Subcategory].[Subcategory].MEMBERS*[Customer].[State-Province].[State-Province].MEMBERS ON 1
FROM [Adventure Works]
WHERE ([Ship Date].[Date].RSum, Measures.[Normalized Cost])
这是来自有关优化Iif
的博客文章: 解决方案
I suspect that the bottleneck is because when you use Iif
neither of the logical branches is NULL
so you're not getting block mode calculations: this is a better way of using Iif
: Iif(someBoolean, X, Null)
or Iif(someBoolean, Null, x)
but unfortunately in your case you cannot have null in either.
Maybe you could try implementing this type of pattern suggested by Mosha for replacing Iif
:
WITH
MEMBER Measures.[Normalized Cost] AS [Measures].[Internet Standard Product Cost]
CELL CALCULATION ScopeEmulator
FOR '([Promotion].[Promotion Type].&[No Discount],measures.[Normalized Cost])'
AS [Measures].[Internet Freight Cost]+[Measures].[Internet Standard Product Cost]
MEMBER [Ship Date].[Date].RSum AS Sum([Ship Date].[Date].[Date].MEMBERS), SOLVE_ORDER=10
SELECT
[Promotion].[Promotion Type].[Promotion Type].MEMBERS on 0
,[Product].[Subcategory].[Subcategory].MEMBERS*[Customer].[State-Province].[State-Province].MEMBERS ON 1
FROM [Adventure Works]
WHERE ([Ship Date].[Date].RSum, Measures.[Normalized Cost])
This is from this blog post about optimizing Iif
: http://sqlblog.com/blogs/mosha/archive/2007/01/28/performance-of-iif-function-in-mdx.aspx
So looking at one of your calculations - this one:
MEMBER [Calculated Measure 1] AS
IIF([Level].[Level].CurrentMember.Member_Key = 'FirstSet',
SUM(existing [Set 1]),
IIF([Level].[Level].CurrentMember.Member_Key = 'SecondSet',
SUM(existing [Set 2]),
SUM([Measures].[Measure1]) * 15
)
)
I think we could initially break it down to this:
MEMBER [Measures].[x] AS SUM(existing [Set 1])
MEMBER [Measures].[y] AS SUM(existing [Set 2])
MEMBER [Measures].[z] AS SUM([Measures].[Measure1]) * 15
MEMBER [Calculated Measure 1] AS
IIF([Level].[Level].CurrentMember IS [Level].[Level].[Level].[FirstSet],
[Measures].[x],
IIF([Level].[Level].CurrentMember IS [Level].[Level].[Level].[SecondSet],
[Measures].[y],
[Measures].[z]
)
)
Now trying to apply Mosha's pattern (not something I've tried before so you will need to adjust accordingly)
MEMBER [Measures].[z] AS SUM([Measures].[Measure1]) * 15
MEMBER [Measures].[y] AS SUM(existing [Set 2])
MEMBER [Measures].[x] AS SUM(existing [Set 1])
MEMBER [Calculated Measure 1 pre1] AS [Measures].[z]
CELL CALCULATION ScopeEmulator
FOR '([Level].[Level].[Level].[SecondSet],[Calculated Measure 1 pre1])'
AS [Measures].[y]
MEMBER [Calculated Measure 1] AS [Calculated Measure 1 pre1]
CELL CALCULATION ScopeEmulator
FOR '([Level].[Level].[Level].[FirstSet],[Calculated Measure 1])'
AS [Measures].[x]
这篇关于快速动态命名集计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!