通过过滤度量值在 MDX 中定义计算成员

发布于 2024-07-07 00:09:03 字数 881 浏览 14 评论 0原文

我需要在 MDX 中定义一个计算成员（这是 SAS OLAP，但我很感谢使用不同 OLAP 实现的人员提供的答案）。

新度量的值应通过应用附加过滤条件根据现有度量计算得出。我想通过一个例子会更清楚：

现有度量：“总流量”
现有维度：“方向”（“入”或“出”）
我需要创建一个计算成员“传入流量”，它等于“总流量”带有附加过滤器（方向 =“In”）

问题是我不知道 MDX 并且我的日程安排非常紧张（对于新手问题感到抱歉）。我能想到的最好的办法是：

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

除了具有特定方向的单元格之外，几乎可以工作：

example

所以它看起来像方向上的“内在”过滤器被我自己的过滤器覆盖）。我需要“内在”过滤器和我自己的过滤器的交集。我的直觉是，它与正在评估的单元格的内在坐标相交 [Direction].[(All)].[In] 有关，但很难知道我需要什么而不需要什么首先阅读该主题:)

[更新]我最终得到了

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

..但至少在SAS OLAP中这会导致对底层执行额外的查询（以计算[in]的值）数据集，所以最终没有使用。

原文

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:

Existing measure: "Total traffic"
Existing dimension: "Direction" ("In" or "Out")
I need to create a calculated member "Incoming traffic", which equals "Total traffic" with an additional filter (Direction = "In")

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:

example

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 :)

[update] I ended up with

IIF([Direction].currentMember = [Direction].[(All)].[Out],
    0,
    ([Measures].[Total traffic], [Direction].[(All)].[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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

︶￣淡然 2024-07-14 00:09:06

我认为您应该在总流量事实表中添加一列用于输入/输出指示和信息。为 IN & 创建一个 Dim 表输出值。然后您可以根据 IN & 分析您的数据。出去。

回复收藏 0 原文

如此安好 2024-07-14 00:09:05

首先，您可以在 MDX 中定义一个新的计算度量，并告诉它使用另一个度量的值，但应用过滤器：

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

每当您在报表上显示新度量时，它的行为就好像它有一个过滤器“方向>” 无论是否使用方向尺寸，都在其中。

但就你而言，你希望方向维度在使用时优先......所以事情变得有点混乱。您必须检测该维度是否正在使用，并采取相应措施：

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

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

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.

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])
)'

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.

回复收藏 0 原文

~没有更多了~