MDX-使用范围重写查询 [英] MDX - rewrite a query using scope
问题描述
我有一个MDX
查询
IIF
(
IsLeaf([PnL].[PnL_A].CurrentMember)
,
[Measures].[PnL - Plan] * [PnL].[Flag 5].CurrentMember.MemberValue
,Sum
(
[PnL].[PnL_A].CurrentMember.Children
,[Measures].[PnL- Plan (signed)]
)
)
它的作用: 整个事情代表了盈亏.不幸的是,它的构造方式有两列:损益值和另一列标志.
What it does: The whole thing represents profit and loss. Unfortunately, it is constructed in a way that there are two columns: value of a profit or loss, and flag in the other column.
因此,如果标志([PnL].[Flag 5]
)设置为-1
,则值([Measures].[PnL - Plan]
)是亏损,如果标志是1
-值是利润.我无法改变.
So if the flag ([PnL].[Flag 5]
) is set to -1
, the value ([Measures].[PnL - Plan]
) is a loss, if the flag is a 1
- the value is a profit. I can't change that.
查询查找层次结构的叶子(利润或亏损的最深来源),并将该标志与该值相乘.对于非叶成员,它只是聚合叶子.
The query finds leaves of the hierarchy (single deepest source of a profit or loss) and multiplies the flag with the value. For non-leaf members it just aggregates it's leaves.
我的问题是它的运行速度太慢-我想使用SCOPE
重写此查询,但我不知道该怎么做.
My problem is that it works too slow - I wanted to rewrite this query using SCOPE
but I have no idea how.
推荐答案
由于我对您的多维数据集结构完全一无所知,所以假设您的成员结构为
Since I have absolutely no idea on your cube structure, let's say your member structure is
Pnl-->Pnl_A-->NonLeaf-->Leaf
Pnl-->Pnl_A-->NonLeaf-->Leaf
您可以如下定义范围-
CREATE MEMBER CurrentCube.[Measures].[ProfitOrLossValue] AS NULL;
//Current member is leaf and Flag5 is 1
SCOPE
(
[Measures].[ProfitOrLossValue],
[PnL].[Flag 5].&[1],
[PnL].[PnL_A].[Leaf].[All].CHILDREN
);
This = [Measures].[PnL - Plan];
END SCOPE;
SCOPE
(
[Measures].[ProfitOrLossValue],
[PnL].[Flag 5].&[-1],
[PnL].[PnL_A].[Leaf].[All].CHILDREN
);
This = ([Measures].[PnL - Plan] * -1);
END SCOPE;
//Current member is non-leaf
SCOPE
(
[Measures].[ProfitOrLossValue],
[PnL].[PnL_A].[NonLeaf].[All].CHILDREN
);
This = Sum
(
[PnL].[PnL_A].CurrentMember.Children,
[Measures].[PnL- Plan (signed)]
);
END SCOPE;
希望有帮助.
这篇关于MDX-使用范围重写查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!