检查任何维度层次结构中的级别是否为 [Foo]

发布于 2024-11-18 20:54:00 字数 921 浏览 2 评论 0原文

在我的日期维度中，我有 2 个层次结构：

[年 - 周 - 日期] 和 [年 - 月 - 日期]

我想检查当前级别的多维数据集计算日期维度的值是[日期]（最低级别）或更高级别。

我怎样才能实现这个目标？

背景：我需要它来计算员工在一段时间内有多少个工作日。我目前有这段代码（未经测试），应该可以解决 1 个层次结构的问题，但我想当用户使用 [年 - 周 - 日期] 层次结构时，这会失败。

CASE WHEN
    [Date].[Year - Month - Date].CURRENTMEMBER.Level
IS
    [Date].[Year - Month - Date].[Date]
THEN
    //at day level,

    //if there is any duration booked, this is a working day
    IIF([Measures].[Duration] = 0,0,1)

ELSE
    //at higher than day level,

    //count days
    COUNT(
        // where duration > 0 (employee work day)
        FILTER(
            Descendants([Date].[Year - Month - Date].CURRENTMEMBER, [Date].[Year - Month - Date].[Date]),
            [Measures].[Duration] > 0
        )
    )

END

tl;dr 如何使上述代码也以尽可能简洁的方式适用于 [年 - 周 - 日期] 层次结构。

原文

In my Date Dimension I have 2 hierarchies:

[Year - Week - Date] and [Year - Month - Date]

I want to check in a Cube Calculation if the current level of the Date Dimension is [Date] (the lowest level) or something higher.

How can I achieve this?

Background: I need this to calculate how many working days there were in a period, for an employee.
I currently have this code (untested) that should do the trick for 1 hierarchy, but I guess this will fail when users are using the [Year - Week - Date] hierarchy.

CASE WHEN
    [Date].[Year - Month - Date].CURRENTMEMBER.Level
IS
    [Date].[Year - Month - Date].[Date]
THEN
    //at day level,

    //if there is any duration booked, this is a working day
    IIF([Measures].[Duration] = 0,0,1)

ELSE
    //at higher than day level,

    //count days
    COUNT(
        // where duration > 0 (employee work day)
        FILTER(
            Descendants([Date].[Year - Month - Date].CURRENTMEMBER, [Date].[Year - Month - Date].[Date]),
            [Measures].[Duration] > 0
        )
    )

END

tl;dr how do I make the above code also work for [Year - Week - Date] hierarchy in the cleanest way possible.

分享到QQ

分享到微博

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

发布评论

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

|煩躁 2024-11-25 20:54:00

我们假设层次结构（也称为属性）[Date].[Date] 存在。如果是这种情况，您可以简化：

  COUNT(
    FILTER( Existing [Date].[Date].members, [Measures].[Duration] > 0 )
  )

现有将强制应用自动存在于 [日期] 维度上。这主要是性能改进，因为它避免了评估（事实上）所有元组。

一旦前面的示例清晰，我们就可以将其与您的版本合并以获得最快的解决方案（不需要第一个 iif）：

  COUNT(
    FILTER( Existing 
       Descendants([Date].[Year - Month - Date].CURRENTMEMBER, [Date].[Year - Month - Date].[Date],self) 
       , [Measures].[Duration] > 0 )
  )

可能会进行进一步的改进，添加具有不同聚合类型的新度量或添加 iif 来更改哪个后代使用两个层次结构（例如，当前成员和默认成员不相等的层次结构）

Let's assume the hierarchy (aka attribute) [Date].[Date] exists. If this is the case you can simplify :

  COUNT(
    FILTER( Existing [Date].[Date].members, [Measures].[Duration] > 0 )
  )

The Existing will force to apply an autoexists on the [Date] dimension. This is mainly a performance improvement as it's avoid to evaluate (fact-vise) all tuples.

Once the former examples is clear, we can merge it with your version for the fastest solution (no need for the first iif) :

  COUNT(
    FILTER( Existing 
       Descendants([Date].[Year - Month - Date].CURRENTMEMBER, [Date].[Year - Month - Date].[Date],self) 
       , [Measures].[Duration] > 0 )
  )

Further improvements may be possible adding a new measure with a different aggregation type or adding an iif to change which of the two hierarchies is used for descendants (e.g. the one where the currentmember and the defaulmember is not equal)

回复收藏 0 原文

帝王念 2024-11-25 20:54:00

如果您正在寻找最简洁的方法来计算一段时间内有多少个工作日，则必须模仿常规测量的行为。否则，在 [日期] 上进行多选时，您将收到错误或错误数据。此外，最好去掉 Count(Filter(...)) 表达式以保持块计算模式（请参阅优化 Count(Filter(...) ) 中的表达式MDX）。为此，请按照下列步骤操作：

转到数据源视图。
在 [Duration] 列旁边创建一个新的命名计算（在同一事实表中）。
列名称为“工作日”，表达式为“null”。
基于“工作日”列创建新的常规度量。聚合函数是 Sum。
在MDX脚本中写入：
<前><代码>(
[日期].[日期].[日期].成员们，
[措施].[工作日]
) = Iif( [测量].[持续时间] > 0, 1, null );

If you are looking for the cleanest way to calculate how many working days there were in a period, you have to emulate the behavior of regular measure. Otherwise you will get error or bad data in the case of a multiselect on [Date]. In addition, it is desirable to get rid of the Count(Filter(...)) expression to keep the block computation mode (see Optimizing Count(Filter(...)) expressions in MDX). To do this, follow these steps:

Go to the data source view.
Create a new named calculation next to the [Duration] column (in the same fact table).
Column name is "Working days", expression is "null".
Create a new regular measure based on "Working days" column. Aggregation function is Sum.

Write in the MDX script:

(
    [Date].[Date].[Date].Members,
    [Measures].[Working days]
) = Iif( [Measures].[Duration] > 0, 1, null );

回复收藏 0 原文

像极了他 2024-11-25 20:54:00

IsLeaf() 会告诉您成员是否处于底层。

回复收藏 0 原文

述情 2024-11-25 20:54:00

查看计算脚本中的 SCOPE 命令，我对 YMD 日历和 YWD 日历做了类似的操作：

CREATE MEMBER CurrentCube.Measures.Example AS NULL //Dummy will be calc'd later

SCOPE (DESCENDANTS([Date].[Calender Y M D],,AFTER));    
 Measures.Example = 1; //Fill in for monthly calcs
END SCOPE

SCOPE (DESCENDANTS([Date].[Calender Y W D],,AFTER));    
 Measures.Example = 2; //Fill in for weekly calcs
END SCOPE

带有 ,,AFTER 的语法是排除 All 成员（如果我没记错的话），我正在尝试区分链接，但是找不到它。或者，如果计算适用于 ALL 成员，则只需使用 SCOPE([Date].[Calender YWD])

Look at SCOPE commands in your calculation script, I do something similar for my YMD calender and my YWD calendar:

CREATE MEMBER CurrentCube.Measures.Example AS NULL //Dummy will be calc'd later

SCOPE (DESCENDANTS([Date].[Calender Y M D],,AFTER));    
 Measures.Example = 1; //Fill in for monthly calcs
END SCOPE

SCOPE (DESCENDANTS([Date].[Calender Y W D],,AFTER));    
 Measures.Example = 2; //Fill in for weekly calcs
END SCOPE

The syntax with the ,,AFTER is to exclude the All member if I remember rightly, I'm trying to dif the link up but can't find it. Alternatively, if the calculation works well with the ALL member, just use SCOPE([Date].[Calender Y W D])

回复收藏 0 原文

~没有更多了~