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屋！