通过过滤度量值在MDX中定义一个计算成员 [英] Define a calculated member in MDX by filtering a measure's value

问题描述

我需要在MDX中定义一个计算所得的成员(这是SAS OLAP，但无论如何，我都会感谢使用不同OLAP实现的人员的回答.)

I need to define a calculated member in MDX (this is SAS OLAP, but I'd appreciate answers from people who work with different OLAP implementations anyway).

应通过应用其他过滤条件，根据现有度量来计算新度量的值.我想用一个例子会更清楚:

The new measure's value should be calculated from an existing measure by applying an additional filter condition. I suppose it will be clearer with an example:

• 现有度量:总点击量"
• 现有尺寸:方向"(入"或出")
• 我需要创建一个计算所得的成员传入流量"，该成员等于总流量"，并带有一个额外的过滤器(方向=输入")

问题是我不知道MDX，而且我的时间表很紧(所以很抱歉出现新手问题).我能想到的最好的方法是:

The problem is that I don't know MDX and I'm on a very tight schedule (so sorry for a newbie question). The best I could come up with is:

([Measures].[Total traffic], [Direction].[(All)].[In])

除了具有特定方向的单元格外，几乎可以使用

Which almost works, except for cells with specific direction:

因此，方向"上的本征"过滤器似乎被我自己的过滤器覆盖了).我需要本征"过滤器和我自己的过滤器的交集.我的直觉是它与被评估单元格的内在坐标相交[Direction].[(All)].[In]有关，但是如果不先阅读该主题，很难知道我需要什么:)

So it looks like the "intrinsic" filter on Direction is overridden with my own filter). I need an intersection of the "intrinsic" filter and my own. My gut feeling was that it has to do with Intersecting [Direction].[(All)].[In] with the intrinsic coords of the cell being evaluated, but it's hard to know what I need without first reading up on the subject :)

[更新] 我最终以

IIF([Direction].currentMember = [Direction].[(All)].[Out],
    0,
    ([Measures].[Total traffic], [Direction].[(All)].[In])
)

..但是至少在SAS OLAP中，这会导致对基础数据集执行额外的查询(以计算[in]的值)，因此我最后没有使用它.

..but at least in SAS OLAP this causes extra queries to be performed (to calculate the value for [in]) to the underlying data set, so I didn't use it in the end.

推荐答案

首先，您可以在MDX中定义一个新的计算量度，并告诉它使用其他量度的值，但要应用过滤器: /p>

To begin with, you can define a new calculated measure in your MDX, and tell it to use the value of another measure, but with a filter applied:

WITH MEMBER [Measures].[Incoming Traffic] AS
'([Measures].[Total traffic], [Direction].[(All)].[In])'

无论何时在报表上显示新度量，无论是否完全使用了方向"维度，它的行为都将像在其上具有方向>内"过滤器一样.

Whenever you show the new measure on a report, it will behave as if it has a filter of 'Direction > In' on it, regardless of whether the Direction dimension is used at all.

但是在您的情况下，您希望使用Direction维度时要优先使用....因此事情会变得有些混乱.您将必须检测此尺寸是否在使用中，并采取相应措施:

But in your case, you WANT the Direction dimension to take precendence when used....so things get a little messy. You will have to detect if this dimension is in use, and act accordingly:

WITH MEMBER [Measures].[Incoming Traffic] AS
'IIF([Direction].currentMember = [Direction].[(All)].[Out],
    ([Measures].[Total traffic]),
    ([Measures].[Total traffic], [Directon].[(All)].[In])
)'

要查看维度是否正在使用，我们检查当前单元格是否正在使用OUT.如果是这样，我们可以按原样返回总流量.如果没有，我们可以告诉它在元组中使用IN.

To see if the Dimension is in use, we check if the current cell is using OUT. If so we can return Total Traffic as it is. If not, we can tell it to use IN in our tuple.

这篇关于通过过滤度量值在MDX中定义一个计算成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！