MDX 中的 LastChild 模拟 - 多个层次结构

发布于 2024-12-13 03:33:44 字数 693 浏览 3 评论 0原文

我有一个 SSAS 多维数据集，其中有一个度量需要通过另一个度量中的百分比进行分配。我将所有这些设置为我的“股权金额”度量中的度量表达式，并且效果很好。

我的问题是，这个“股权金额”度量实际上是一个快照，所以我需要它使用 LastChild 函数进行聚合。事实证明，您不能在半加性度量中拥有度量表达式，因此我尝试伪造 MDX 中的 LastChild 函数。

我在网络上随处看到了很多示例，但它们都没有谈到在日期维度中具有多个层次结构。我有“日历年”和“财政年度”层次结构。

我的 MDX 适用于一个层次结构，但一旦我作用于第二个层次结构，第一个层次结构就会被覆盖。我猜我需要在一个声明中处理这两个层次结构，但要让它发挥作用真的很难。

这是我的一个层次结构的 MDX。任何人都可以帮助修改它以适应多个层次结构，或者有其他方法可以解决我的问题吗？

Scope([Measures].[Equity Value]);
    This = iif(isleaf([Calendar].[By Calendar Year].CurrentMember),
                [Measures].[Equity Value],
                ([Calendar].[By Calendar Year].CurrentMember.LastChild,[Measures].[Equity Value]));
End Scope;

原文

I have a SSAS cube in which there is a measure that needs to be allocated via a percentage located in another measure. I have all this set up as a Measure Expression in my "Equity Amount" measure and it works great.

My problem is that this "Equity Amount" measure is actually a snapshot so I would need it to aggregate using the LastChild function. It turns out that you cannot have a measure expression in a semi additive measure so i'm trying to fake the LastChild function in MDX.

I've seen a lot of examples everywhere on the web and all but none of them talk about having multiple hierarchies in the date dimension. I have both "calendar Year" and "Fiscal Year" hierarchies.

My MDX works for one hierarchy but as soon as I scope for the second hierarchy, the first one gets overwritten. I'm guessing I need to treat both hierarchies in a single statement but am having a real tough time getting it to work.

Here is my MDX for one hierarchy. Can anyone help modify it for multiple hierarchies or is there any other way to solve my problem ?

Scope([Measures].[Equity Value]);
    This = iif(isleaf([Calendar].[By Calendar Year].CurrentMember),
                [Measures].[Equity Value],
                ([Calendar].[By Calendar Year].CurrentMember.LastChild,[Measures].[Equity Value]));
End Scope;

分享到QQ

分享到微博

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

发布评论

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

寒江雪… 2024-12-20 03:33:44

David，

1）您将范围用作计算成员。您可以通过声明仅在子多维数据集中工作的范围来摆脱 iif ：

 scope ([Measures].[Equity Value],[Calendar].[By Calendar Year].levels(0).... )
  This = (the expression)

2）不确定是否理解您的问题，但具有相同维度的两个层次结构的两个成员的元组可以通过构造为空：作为示例，您的日历的第一天（2010 年 1 月 1 日）和会计日历的第一天（例如 2020 年 1 月 6 日）不是同一天，因此实际上为空。相同维度的两个层次结构只是表示相同坐标的方式（这里指的是），您通过声明一个元组来进行交集。

不确定我是否在帮助你...

David,

1) You're using scope as a calculated member. You can get rid of your iif by declaring the scope working only in a sub-cube :

 scope ([Measures].[Equity Value],[Calendar].[By Calendar Year].levels(0).... )
  This = (the expression)

2) Not sure to understand your problem but a tuple with two members of two hierarchies of the same dimension can be null by construction: As an example your first day of the calendar (1/1/2010) and the first day of your fiscal calendar (e.g. 1/6/2020) are not the same day so actually null. Two hierarchies of a same dimension are only ways of representing the same coordindates (here days), you're doing an intersection by declaring a tuple.

Not sure I'm helping you...

回复收藏 0 原文