ssas - 时间不在轴上时的 ParallelPeriod 问题
问题描述
此查询中计算的成员 [Amount LM] (=amount lastmonth) 有什么问题?如果我在行上指定时间,它似乎可以工作,但如果我不这样做,它会失败,即使我在 where 子句中有一个日期。
WITH
MEMBER [Amount LM] as
(
[Measures].[Amount],
ParallelPeriod (
[Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month],
1,
[Date].[Year - Quarter - Month - Date - Hierarchy].CurrentMember
)
)
SELECT
{[Amount LM], [Measures].[Amount]} ON COLUMNS,
[Measure Types].[Name].&[22] --not related to [Measures].[Amount]; Used as a measure switch in the real case but adjusted here for simplicity
-- comment out the next line to fail
* [Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month]
ON rows
FROM [Cube]
WHERE [Date].[Year Number].&[2019]
;
工作案例中的结果:
Amount LM | Amount
Costs | Jan 2019 | (null) | 109600
Costs | Feb 2019 | 109600 | 218300.5
Costs | Mar 2019 | 218300.5 | 392250
Costs | Apr 2019 | 392250 | 206800
Costs | May 2019 | 206800 | 174700
Costs | Jun 2019 | 174700 | 298400
Costs | Jul 2019 | 298400 | 264550
Costs | Aug 2019 | 264550 | 424100
Costs | Sep 2019 | 424100 | 129650
Costs | Oct 2019 | 129650 | 330050
Costs | Nov 2019 | 330050 | (null)
Costs | Dec 2019 | (null) | (null)
没有
* [Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month]
部分,金额 LM ist NULL
|Amount LM| Amount
Costs | (null) | 2548400.5
解决方案
你的计算没有问题。要了解 Null,您需要知道
- ParallelPeriod 的工作原理
- UserHierarchy 上的“CurrentMember”如何工作
当你有
“[日期]。[年 - 季度 - 月 - 日期 - 层次结构]。[年和月]”
在你的行轴
“[日期].[年 - 季度 - 月 - 日期 - 层次结构].CurrentMember”
上面的“.currentmember”表达式返回 USER HIERARCHY“[Year - Quarter - Month - Date - Hierarchy]”的当前成员。PARALLEL PERIOD 函数然后尝试查找与 LEVEL "[Year and Month]" 相关的表亲成员。
当您从 rows 中删除用户层次结构时, currentmember 返回 2019,它是 LEVEL "YEAR Number" 的成员,因此并行期间找不到与 LEVEL "[Year and Month]" 相关的表亲(因为传递的 currentmember 在上面指定的级别)。要检查用月份成员(并且行上没有日期)替换其中的年份,它将起作用。要解决此问题,您需要提供备用逻辑,以防更高级别的成员通过或其他边缘情况使用下面的脚本,在任何边缘情况下都将返回 100。
WITH
MEMBER [Amount LM] as
case when [Date].[Year - Quarter - Month - Date - Hierarchy].CurrentMember.level.name <>"Year and Month" then 100
else
(
[Measures].[Amount],
ParallelPeriod (
[Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month],
1,
[Date].[Year - Quarter - Month - Date - Hierarchy].CurrentMember
)
)
end
以下是您在 Adventure 作品中遇到的问题的示例。我添加了两列来检查返回的 ParallelPeriod 和 .currentmember
WITH
MEMBER [Amount LM] as
(
[Measures].[Internet Sales Amount],
ParallelPeriod ([Date].[Calendar].[Month],1,[Date].[Calendar].currentmember)
)
MEMBER [Amount LM_ParallelPeriodName] as
(
ParallelPeriod ([Date].[Calendar].[Month],1,[Date].[Calendar].currentmember).name
)
MEMBER [Amount LM_CalendarCurrentmember] as
(
[Date].[Calendar].currentmember.name
)
SELECT
{[Amount LM], [Measures].[Internet Sales Amount],[Amount LM_ParallelPeriodName],[Amount LM_CalendarCurrentmember]} ON COLUMNS,
[Product].[Category].&[1] * [Date].[Calendar].[Month]
ON rows
from [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]
现在让我们评论
“[日期].[日历].[月份]”
WITH
MEMBER [Amount LM] as
(
[Measures].[Internet Sales Amount],
ParallelPeriod ([Date].[Calendar].[Month],1,[Date].[Calendar].currentmember)
)
MEMBER [Amount LM_ParallelPeriodName] as
(
ParallelPeriod ([Date].[Calendar].[Month],1,[Date].[Calendar].currentmember).name
)
MEMBER [Amount LM_CalendarCurrentmember] as
(
[Date].[Calendar].currentmember.name
)
SELECT
{[Amount LM], [Measures].[Internet Sales Amount],[Amount LM_ParallelPeriodName],[Amount LM_CalendarCurrentmember]} ON COLUMNS,
[Product].[Category].&[1] --* [Date].[Calendar].[Month]
ON rows
from [Adventure Works]
WHERE [Date].[Calendar Year].&[2013]
结果 注意 ParallelPeriod 返回的 Null,现在让我们从 Month 中替换 Year。这使得 paralellperiod 返回一些有用的东西
WITH
MEMBER [Amount LM] as
(
[Measures].[Internet Sales Amount],
ParallelPeriod ([Date].[Calendar].[Month],1,[Date].[Calendar].currentmember)
)
MEMBER [Amount LM_ParallelPeriodName] as
(
ParallelPeriod ([Date].[Calendar].[Month],1,[Date].[Calendar].currentmember).name
)
MEMBER [Amount LM_CalendarCurrentmember] as
(
[Date].[Calendar].currentmember.name
)
SELECT
{[Amount LM], [Measures].[Internet Sales Amount],[Amount LM_ParallelPeriodName],[Amount LM_CalendarCurrentmember]} ON COLUMNS,
[Product].[Category].&[1] * [Date].[Calendar].[Month]
ON rows
from [Adventure Works]
WHERE [Date].[Month Name].[September 2013]
推荐阅读
- sql - 将 sql 导入 pgAdmin
- reactjs - 如何以编程方式关闭 chakra Ui 中的抽屉
- bash - Bash中-z命令的否定是什么?
- php - Twig / Symfony:我无法访问我的关系变量
- c - 我们如何知道 C 中的文件中存在漏洞?
- kubernetes - 使用具有多个静态 IP 地址的 Cloud NAT 时,是否可以在 GKE 中为 Pod 分配特定的出站 IP 地址?
- typescript - 如何修复 redux-toolkit 中的 typescript 错误?
- swift - 线程 1:EXC_BAD_ACCESS (code=2, address=0x7ffee5c95ff8) 我是 swiftUI 的新手,我收到了这个错误。任何帮助表示赞赏
- c++ - 如果类中存在向量成员,为什么基本类型 int x 会被初始化?
- java - 无法通过 Windows 命令行运行 Java 主类