MDX查询添加行维度后部分设备结果异常求助

问题描述 #

我编写的MDX查询仅以[Dim Equipment Attribute].[Equipment Name]为行维度时运行正常，逻辑为：若设备无[Measures].[Count]值，则获取该设备所属源段设备内最后一个有Count值的设备的Count。但添加[Dim Equipment Attribute].[Equipment Angle]作为行维度后，部分设备的查询结果出现异常。我推测是EXISTING关键字导致问题，但替换后原正常查询也失效，这是我首次编写MDX查询，寻求解决办法。

正常查询语句 #

WITH MEMBER [Measures].[test] AS
           IIF
   (
        NOT ISEMPTY([Measures].[Count]) ,
        ([Measures].[Count]),
        
            TAIL(
            (EXISTING ([Dim Equipment Attribute].[Source Segment Equipment Id].[Source Segment Equipment Id].Members)
            ,FILTER(
                [Equipment Name].[Equipment Name].Members ,
                [Measures].[Count]>0))
            ,1).Item(0)                         
    )
SELECT
{[Measures].[test]} ON COLUMNS,
([Dim Equipment Attribute].[Equipment Name].[Equipment Name].Members) ON ROWS
FROM [DB DWH]

添加行维度后的异常查询语句 #

WITH MEMBER [Measures].[test] AS
           IIF
   (
        NOT ISEMPTY([Measures].[Count]) ,
        ([Measures].[Count]),
        
            TAIL(
            (EXISTING ([Dim Equipment Attribute].[Source Segment Equipment Id].[Source Segment Equipment Id].Members)
            ,FILTER(
                [Equipment Name].[Equipment Name].Members ,
                [Measures].[Count]>0))
            ,1).Item(0)                         
    )
SELECT
{[Measures].[test]} ON COLUMNS,
([Dim Equipment Attribute].[Equipment Name].[Equipment Name].Members, [Dim Equipment Attribute].[Equipment Angle].[Equipment Angle].Members) ON ROWS
FROM [DB DWH]

问题根源 #

添加Equipment Angle行维度后，EXISTING的上下文范围被扩大——当前行同时包含设备名称和角度两个维度属性，EXISTING会基于这两个维度的组合进行过滤，导致同属源段的其他设备因不匹配当前行的角度值，被排除在筛选范围外，最终返回错误结果。

修正方案 #

方案一：锁定源段上下文，排除角度维度干扰 #

WITH MEMBER [Measures].[test] AS
    IIF(
        NOT ISEMPTY([Measures].[Count]),
        [Measures].[Count],
        // 获取当前设备所属的源段，筛选该源段下有Count值的设备，取最后一个
        TAIL(
            NONEMPTY(
                [Dim Equipment Attribute].[Source Segment Equipment Id].CurrentMember *
                [Dim Equipment Attribute].[Equipment Name].[Equipment Name].Members,
                [Measures].[Count]
            ), 1
        ).Item(0).Item(0)
    )
SELECT
    {[Measures].[test]} ON COLUMNS,
    (
        [Dim Equipment Attribute].[Equipment Name].[Equipment Name].Members,
        [Dim Equipment Attribute].[Equipment Angle].[Equipment Angle].Members
    ) ON ROWS
FROM [DB DWH]

方案二：利用层级关系获取父级源段（若维度有层级） #

如果Equipment Name是Source Segment Equipment Id的子成员，可直接用Ancestor函数定位父级源段，逻辑更清晰：

WITH MEMBER [Measures].[test] AS
    IIF(
        NOT ISEMPTY([Measures].[Count]),
        [Measures].[Count],
        TAIL(
            NONEMPTY(
                Ancestor(
                    [Dim Equipment Attribute].[Equipment Name].CurrentMember,
                    [Dim Equipment Attribute].[Source Segment Equipment Id]
                ) * [Dim Equipment Attribute].[Equipment Name].[Equipment Name].Members,
                [Measures].[Count]
            ), 1
        ).Item(0).Item(0)
    )
SELECT
    {[Measures].[test]} ON COLUMNS,
    (
        [Dim Equipment Attribute].[Equipment Name].[Equipment Name].Members,
        [Dim Equipment Attribute].[Equipment Angle].[Equipment Angle].Members
    ) ON ROWS
FROM [DB DWH]

关键修改说明 #

• 用CurrentMember直接锁定当前行的源段设备ID，避免多维度上下文对筛选范围的干扰
• 替换FILTER为NONEMPTY，更高效地筛选存在有效Count值的设备成员
• 明确从TAIL返回的元组中提取Count值，避免维度上下文混乱导致的取值错误

内容的提问来源于stack exchange，提问作者RandyBC