代码之家 › 专栏 › 技术社区 › vldmrrdjcc

计算成员的问题总是等于零

mdx ssas

vldmrrdjcc · 技术社区 · 6 年前

我有一个简单的事实表,其中有AverageBalance列、TimeKey(它总是月末日期)、AccountKey和AccountClassKey。

CREATE MEMBER CURRENTCUBE.[Measures].[Average Calc] AS 
    Avg(
      EXISTING([Time].[Processing Date].[Processing Date].MEMBERS) ,
      [Measures].[Avg Bal]
    ) ;

而且效果很好。

但我有一个问题,就是在计算中添加以下逻辑:

所以我尝试了这样的方法(首先我添加了Account类密钥作为度量):

CREATE MEMBER CURRENTCUBE.[Measures].[AvgBalMult] AS
[Measures].[Avg Bal] * (
       CASE 
       WHEN [Measures].[Account Class Key] = 1 THEN 1  
       WHEN [Measures].[Account Class Key] = 2 THEN -1
       else 0
       END);

但是,当我随着时间的推移进行聚合时,我总是在报告中得到零,我意识到这是因为Account类度量首先进行聚合,因此它永远不会有1或2的值,除非我只查看Account level。

最后我想得到这样的东西:

CREATE MEMBER CURRENTCUBE.[Measures].[Average Calc] AS 
    Avg(
      EXISTING([Time].[Processing Date].[Processing Date].MEMBERS) ,
      [Measures].[Avg Bal] * (
       CASE 
       WHEN [Measures].[Account Class Key] = 1 THEN 1  
       WHEN [Measures].[Account Class Key] = 2 THEN -1
       else 0
       END)
    ) ;

在帐户级别,它将给我平均余额没有符号,但当聚合时,它应该与上述逻辑聚合。

2 回复 | 直到 6 年前

Alex Peshik 6 年前

你可以试试 MeasureExpression 财产。它在聚合之前工作。

https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/configure-measure-properties?view=sql-server-2017

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ba16255-55b8-411c-a6ff-027701156f2c/ssas-measureexpression-influence-on-usage-of-aggregations?forum=sqlanalysisservices

https://blog.crossjoin.co.uk/2005/06/30/measure-expressions/

MoazRub 6 年前

CREATE MEMBER CURRENTCUBE.[Measures].[AccountClassKeyMultipiler] AS
(
CASE 
WHEN [DimAccount].[AccountClass].[Account Class Key].currentmember.Properties ("Member_Value",TYPED) = "1" THEN 1  
WHEN [DimAccount].[AccountClass].[Account Class Key].currentmember.Properties ("Member_Value",TYPED) = "2" THEN -1  
else 0
END);

CREATE MEMBER CURRENTCUBE.[Measures].[AvgBalMult] AS
[Measures].[Avg Bal] * [Measures].[AccountClassKeyMultipiler]

编辑在这里,我试图解决一个类似的案例

select 
{[Measures].[Internet Sales Amount]
}
on columns,
(
[Product].[Category].[Category],
[Date].[Month of Year].[Month of Year]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

接下来,我们将上述查询转换为基于月份的每个产品的平均销售额

with member 
[Measures].[Internet Sales AmountAvg]
as
Avg(EXISTING([Date].[Month of Year].[Month of Year]) ,[Measures].[Internet Sales Amount]) 
select 
{
[Measures].[Internet Sales Amount]
,
[Measures].[Internet Sales AmountAvg]
}
on columns,
(
[Product].[Category].[Category]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

现在我们添加一个基于大小写的乘数

with
member [Measures].[Internet Sales AmountAvg]
as
Avg(EXISTING([Date].[Month of Year].[Month of Year]) ,[Measures].[Internet Sales Amount]) 

member [Measures].[Multipiler]
as
CASE 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Accessory' THEN 1  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Mountain' THEN 2  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Road' THEN 3  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Touring' THEN 4 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Components' THEN 5  
else 0
END

member [Measures].[Internet Sales AmountAvg2]
as
[Measures].[Internet Sales AmountAvg]*[Measures].[Multipiler]

select 
{
[Measures].[Internet Sales Amount]
,
[Measures].[Internet Sales AmountAvg]
,
[Measures].[Multipiler]
,
[Measures].[Internet Sales AmountAvg2]
}
on columns,
(
[Product].[Category].[Category],[Product].[Product Line].[Product Line]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

with
member [Measures].[Internet Sales AmountAvg]
as
Avg(EXISTING([Date].[Month of Year].[Month of Year]) ,[Measures].[Internet Sales Amount]) 

member [Measures].[Multipiler]
as
CASE 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Accessory' THEN 1  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Mountain' THEN 2  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Road' THEN 3  
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Touring' THEN 4 
WHEN [Product].[Product Line].currentmember.Properties ("Member_Value",TYPED) = 'Components' THEN 5  
else 0
END

member [Measures].[Internet Sales AmountAvg2]
as
[Measures].[Internet Sales AmountAvg]*[Measures].[Multipiler]

select 
{
[Measures].[Internet Sales Amount]
,
[Measures].[Internet Sales AmountAvg]
,
[Measures].[Multipiler]
,
[Measures].[Internet Sales AmountAvg2]
}
on columns,
(
[Product].[Category].[Category]
)
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

底线是,在这种情况下,如果度量值的行为依赖于维度属性值,则需要在轴之一中具有该维度属性。如果不存在,则使用默认值。如果在逻辑中未处理默认值,则会导致错误的结果