MDX按维度属性过滤计算的成员 [英] MDX Calculated member filter by dimension attribute

问题描述

我想创建一个计算的成员并按维度过滤。这是工作的例子：

 （
 [Policy]。[Policy Status]。& [Void]，
 [Policy]。[Tran Type]。& [Renewal]，
 [Measures]。[FK Policy Distinct Count] 
）
  pre> 
 
 
但是，如果我想过滤它像这样 
 
 
  
 [Policy]。[Policy Status]。& [Void]，
 [Policy]。[Policy Status]。& [Policy]，
 [Measures]。[FK Policy Distinct Count] 
）
  
比它不工作。它说同一个层次结构在元组中显示多次。另外一个问题是，如何排除行？这里有个想法... 
 
 pre $ 
！[Policy]。[Policy Status]。& [Void] ，
！[Policy]。[Policy Status]。& [Policy]，
 [Measures] [FK Policy Distinct Count] 
）
  

解决方案

首先，了解MDX语法以及它如何与元组，成员和集合。

元组

使用圆括号表示一个元组：

 <$ c $ & [Void]，
 [Policy]。[Tran Type]。& [Renewal]，
 [Measures]。[ FK Policy Distinct Count] 
）
  

元组只能包含任何层级中的单个成员。

集合

要从同一层次中的多个成员检索结果，必须查询一个集合。一个MDX集由大括号表示：

  {
 [Policy]。[Policy Status]。& [Void ]，
 [Policy]。[Policy Status]。& [Policy] 
} 
  

一套是按照定义， / b>

一个有序的零，一个或多个元组集合。

所以，如果你想要针对这两个成员查询 [FK Policy Distinct Count] 度量，那么这个集合的元组必须包含度量： / p>

  {
（[Policy]。[Policy Status]。& [Void]，[Measures]。[FK政策区分计数]），
（[Policy]。[Policy Status]。& [Policy]，[Measures]。[FK Policy Distinct Count]）
} 
  
 
 
为了简化这个表达式，有可能 crossjoin 两套不同的维度ty： 
 
 
  {
 [Policy]。[Policy Status]。& [Void]，
 [Policy]。[Policy Status]。& [Policy]，
 [Policy]。[Policy Status]。& [Something]，
 [Policy]。[Policy Status]。& [ [另外一个成员] 
 
 $ b b 
 [措施] [FK政策] Distinct Count] 
} 
  
 
 
 
排除行
 
 
 
现在我们可以定义集合，现在是时候从一个成员中删除一些成员。在你的例子中，听起来就像你想从一个层次开始（对于MDX引擎来说，它只是一个包含层次结构中每个成员的多维数据集中的预定义集合），并排除某些成员。 MDX有很多函数可以在集合上运行，我们将使用 EXCEPT 。
 
 
 
 c $ c> EXCEPT 函数需要两个参数，第一个参数要从第一个移除的集合，第二个是应该从第一个移除的集合。它返回一个集合。
 
 
在这个例子中，我将假定 [Policy]。[Policy Status] 是属性层次结构，它的唯一级别具有唯一名称 [Policy]。[Policy Status]。[Policy Status] 。
  EXCEPT（
 [Policy]。[Policy Status]。[Policy Status]，
 {
 [Policy]。 & [Void]，
 [Policy]。[Policy Status]。& [Policy] 
} 
）
  
这将从 [Policy]。[Policy Status]。[Policy Status]  [Policy]。[Policy Status]。& [Void] 和 [Policy]。[Policy Status]。&为了得到有用的结果，我们可以通过一个度量来交叉结果： 
  
 
  EXCEPT（
 [Policy]。[Policy Status]。[Policy Status]，
 {
 [Policy]。[政策状态]。& [Void]，
 [Policy]。[Policy Status]。& [Policy] 
} 
）
 * 
 { 
 [Measures]。[FK Policy Distinct Count] 
} 
  
 
 
 
设置为一个成员
 
 
 
设置很好，但是有时我们希望从他们那里把它们当作一个成员，就像在你的计算成员要求中一样。为此，我们需要使用聚合函数。聚合函数接受一个集合并返回一个代表整个集合的成员。  
 
 
有很多这样的，正确的取决于存储在你的数据库中的数据： MIN ， MAX ， COUNT 和 SUM  （请参阅 MDX函数参考中的数字函数以获取更完整的列表）。在这个例子中，我假定你的维度是通过使用SUM进行聚合的： 
 
 
  SUM（
 EXCEPT（
 [Policy]。[Policy Status]。[Policy Status]，
 {
 [Policy]。[Policy Status]。& [Void]，
 [Policy]。 ]。& [政策] 
} 
），
 [措施] [FK政策区分计数] 
）
  pre> 
 
 
在这里，我已经通过了要汇总的度量作为第二个参数给SUM。
 
 
 
 
 
 
 MDX是一种复杂的语言，支持许多常见和不常见的设置操作。如果你还没有，我建议花点时间阅读在线文档，或者拿一本好的MDX书。有很多要知道：）
 
 
< 3 
 
I want to create a calculated member and filter it by dimension. This is WORKING example:
(
    [Policy].[Policy Status].&[Void], 
    [Policy].[Tran Type].&[Renewal], 
    [Measures].[FK Policy Distinct Count]
)
But if I want to filter it like this
(
    [Policy].[Policy Status].&[Void], 
    [Policy].[Policy Status].&[Policy],  
    [Measures].[FK Policy Distinct Count]
)
Than it's NOT working. It says that same hierarchy is showing multiple times in the tuple.

Another thing is, how to exclude rows? Here's the idea...
(
    ![Policy].[Policy Status].&[Void], 
    ![Policy].[Policy Status].&[Policy],  
    [Measures].[FK Policy Distinct Count]
)

解决方案
First, it's important to understand the MDX syntax, and how it related to the concepts of tuples, members and sets.

Tuples

Using parentheses denotes a tuple:
(
  [Policy].[Policy Status].&[Void], 
  [Policy].[Tran Type].&[Renewal], 
  [Measures].[FK Policy Distinct Count]
)
A tuple can only include a single member from any hierarchy.

Sets

To retrieve results from multiple members in the same hierarchy, you must query for a set. An MDX set is denoted by braces:
{
  [Policy].[Policy Status].&[Void], 
  [Policy].[Policy Status].&[Policy]
}
A set is, by definition, 

  
an ordered collection of zero, one or more tuples.
So, if you wish to query for the [FK Policy Distinct Count] measure against both of those members, the set's tuples must each include the measure:
{
  ( [Policy].[Policy Status].&[Void],   [Measures].[FK Policy Distinct Count] ), 
  ( [Policy].[Policy Status].&[Policy], [Measures].[FK Policy Distinct Count] )
}
To simplify this expression, it is possible to crossjoin two sets of different dimensionality:
{
  [Policy].[Policy Status].&[Void],
  [Policy].[Policy Status].&[Policy], 
  [Policy].[Policy Status].&[Something], 
  [Policy].[Policy Status].&[Something else], 
  [Policy].[Policy Status].&[Yet another member]
}
*
{
  [Measures].[FK Policy Distinct Count]
}


Excluding rows

Now that we can define sets, it's time to remove some members from one. In your example, it sounds like you want to start with a level (which, to the MDX engine, is just a predefined set in the cube which includes every member at that level of the hierarchy), and exclude certain members. MDX has lots of functions that operate on sets, and we're going to use EXCEPT.

The EXCEPT function takes two parameters, the first being the set to remove from, and the second being the set which should be removed from the first. It returns a set.

In this example, I'm going to assume [Policy].[Policy Status] is an attribute hierarchy, and that its sole level has the Unique Name of [Policy].[Policy Status].[Policy Status].
EXCEPT(
  [Policy].[Policy Status].[Policy Status],
  {
    [Policy].[Policy Status].&[Void],
    [Policy].[Policy Status].&[Policy]
  }
)
This will return every member from the [Policy].[Policy Status].[Policy Status] level, except for [Policy].[Policy Status].&[Void] and [Policy].[Policy Status].&[Policy].

To get useful results, we can cross-join the result by a measure:
EXCEPT(
  [Policy].[Policy Status].[Policy Status],
  {
    [Policy].[Policy Status].&[Void],
    [Policy].[Policy Status].&[Policy]
  }
)
*
{
  [Measures].[FK Policy Distinct Count]
}


Using a set as a single member

Sets are nice, but sometimes all we want from them is to treat them as a single member, as in your calculated member requirement. To do this, we need to use an aggregation function. Aggregation functions take in a set and return a member that represents the entire set. 

There are a number of these, and the right one to use depends on the data stored in your cube: MIN, MAX, COUNT, and SUM are some of them (see "Numeric Functions" in the MDX Function reference for a more complete list). In this example, I'll assume your dimension aggregates by using SUM:
SUM(
  EXCEPT(
    [Policy].[Policy Status].[Policy Status],
    {
      [Policy].[Policy Status].&[Void],
      [Policy].[Policy Status].&[Policy]
    }
  ),
  [Measures].[FK Policy Distinct Count]
)
Here, I have passed the measure to be aggregated as the second parameter to SUM.



MDX is a complex language which supports many common and uncommon set operations. If you haven't already, I advise taking the time to read over the documentation available online, or grab yourself a good MDX book. There's a lot to know :)

<3

                        
这篇关于MDX按维度属性过滤计算的成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！