SSAS Cube中如何用单计算度量值实现双时间层级的选中时段PY总额计算

当前可针对单个时间维度层级计算选中时间维度属性的PY（上年）合计，但无法实现单个计算度量值同时适配两个时间维度层级的计算需求。

现有实现代码 #

此前使用Dynamic SET填充天数并对该集合执行AGGREGATE聚合，两段独立可用的代码如下：

-- 适配[Time].[Time]层级的计算
CREATE DYNAMIC SET CURRENTCUBE.[Days]                                                        
 AS {EXISTING [Time].[Time].[Day]};                
CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                    
 AS AGGREGATE(                                                               
        GENERATE(EXISTING [Days],    
            {PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember)}
        ),
[Measures].[Sales]
),  
FORMAT_STRING = "#,#", 
VISIBLE = 1;

-- 适配[Time].[Current]层级的计算
CREATE DYNAMIC SET CURRENTCUBE.[Days] 
 AS {EXISTING [Time].[Current].[Curr Day Flag]};               
CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                    
 AS AGGREGATE(                                                               
        GENERATE(EXISTING [Days],    
            {PARALLELPERIOD([Time].[Current].[Curr Year Flag],1,[Time].[Current].CurrentMember)}
        ),
[Measures].[Sales]
),  
FORMAT_STRING = "#,#", 
VISIBLE = 1;

待解决问题 #

上述两个计算各自独立运行正常，需要将二者合并，仅保留一个[Measures].[PY Sales Selected]计算度量值，可自动适配两个时间维度层级的计算场景。

时间维度结构参考 #

• 时间维度层级结构：
• 属性关系配置：

解决方案 #

你可以通过ISFILTERED函数判断当前生效的时间层级，动态选择对应的计算逻辑即可，具体实现代码如下：

CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                    
 AS 
 CASE
    -- 判断当前是否筛选了[Time].[Time]层级的属性
    WHEN ISFILTERED([Time].[Time]) THEN
        AGGREGATE(                                                               
            GENERATE(EXISTING [Time].[Time].[Day],    
                {PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember)}
            ),
            [Measures].[Sales]
        )
    -- 判断当前是否筛选了[Time].[Current]层级的属性
    WHEN ISFILTERED([Time].[Current]) THEN
        AGGREGATE(                                                               
            GENERATE(EXISTING [Time].[Current].[Curr Day Flag],    
                {PARALLELPERIOD([Time].[Current].[Curr Year Flag],1,[Time].[Current].CurrentMember)}
            ),
            [Measures].[Sales]
        )
    ELSE
        -- 无对应层级筛选时可按需返回上年全量销售额或者空值
        AGGREGATE(PARALLELPERIOD([Time].[Time].[Year],1), [Measures].[Sales])
 END,
FORMAT_STRING = "#,#", 
VISIBLE = 1;

如果需要提升大维度下的计算性能，也可以提前定义两个动态SET分别对应两个层级的天数集合，再在CASE分支中引用即可：

CREATE DYNAMIC SET CURRENTCUBE.[Days_TimeHierarchy]                                                        
 AS {EXISTING [Time].[Time].[Day]};    

CREATE DYNAMIC SET CURRENTCUBE.[Days_CurrentHierarchy] 
 AS {EXISTING [Time].[Current].[Curr Day Flag]};    

CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                    
 AS 
 CASE
    WHEN ISFILTERED([Time].[Time]) THEN
        AGGREGATE(                                                               
            GENERATE([Days_TimeHierarchy],    
                {PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember)}
            ),
            [Measures].[Sales]
        )
    WHEN ISFILTERED([Time].[Current]) THEN
        AGGREGATE(                                                               
            GENERATE([Days_CurrentHierarchy],    
                {PARALLELPERIOD([Time].[Current].[Curr Year Flag],1,[Time].[Current].CurrentMember)}
            ),
            [Measures].[Sales]
        )
    ELSE
        AGGREGATE(PARALLELPERIOD([Time].[Time].[Year],1), [Measures].[Sales])
 END,
FORMAT_STRING = "#,#", 
VISIBLE = 1;

该实现会自动识别当前用户筛选的时间层级，自动匹配对应的计算逻辑，无需维护两个独立的度量值。

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