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

MDX计算的进位顺序错误

olap-cube mdx ssas

Jamiec · 技术社区 · 14 年前

我对MDX查询有问题,我认为它归结为计算聚合和计算成员之间的优先顺序。

让我从基础数据开始,它围绕着一个估值(有一个日期,以及一些其他数据,例如成员类型、方案——并且对于这个问题至关重要;一个加载因子)和一个相关的值。

数据

赋值表

Id | Valuation Date | Member Type | Scheme   | Loading Factor
=============================================================
1  | 2010-01-01     | TypeA       | Scheme X | 0.02
2  | 2010-01-01     | TypeB       | Scheme X | 0.02
3  | 2010-01-01     | TypeA       | Scheme Y | 0.02
4  | 2010-01-01     | TypeB       | Scheme Y | 0.02

评估值表

ValuationId | Value
====================
1           | 1000.0
2           | 2000.0
3           | 3000.0
4           | 4000.0

当加载到多维数据集中时,它具有一个具有属性memberType、scheme和date的评估维度。以及一个具有包含值度量值的度量值组valuation value的多维数据集,以及一个包含加载因子的度量值组,如:

Cube
 -Measure Groups
  - Valuation
    |_Loading Factor
  - ValuationValue
    |_Value
 - Dimensions
  - Valuation
    |_MemberType
    |_Scheme
    |_Date

问题

加载因子用于加载值,将其视为一种税,因此0.02表示“加载量是值的2%”。当从查询返回值时,我还需要计算加载该值的金额。典型的查询可能看起来像

SELECT
{
 [Measures].[Value] 
} ON 0,
[Valuation].[Scheme] ON 1
FROM Cube

这将返回2行,通过与上面的数据进行比较,可以看到,它在memberType中正确地求和:

Scheme   | Value
=================
Scheme X | 3000.0
Scheme Y | 7000.0

现在,如果我尝试在那个查询中计算我的加载因子,所有的都会出错——我将演示。给出以下查询:

WITH MEMBER [Measures].[Loading Value]
AS
(
   [Measures].[Value] * [Measures].[Loading Factor]
)
SELECT
{
 [Measures].[Value] ,
 [Measures].[Loading Value]
} ON 0,
[Valuation].[Scheme] ON 1
FROM Cube

Scheme   | Value  | Loading Value
=================================
Scheme X | 3000.0 | 120.0
Scheme Y | 7000.0 | 280.0

1000 * 0.02 + 2000 * 0.02 = 60 3000 * 0.04 = 120

3 回复 | 直到 14 年前

Meff 14 年前

CREATE MEMBER [Measures].[Loading Value] AS NULL

Scope( { [Measures].[Loading Value] } );   

    Scope( Leaves([Valuation]) );                                         

            This = [Measures].[Value] * [Measures].[Loading Factor]                                  
            Format_String(This) = "#,##0.00;-#,##0.00";                                                                                

    End Scope;   
End Scope;

AustinDahl 14 年前

 WITH
 MEMBER [Measures].[Custom Calculation] AS
   '([Measures].[Sales Count] - [Measures].[Unit Returns])',
   SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE
 SELECT
 {[Measures].[Custom Calculation]} ON COLUMNS,
 NON EMPTY [Time].[YQMD].[Day].AllMembers ON ROWS
 FROM [Waremart]

Jamiec 14 年前

WITH MEMBER [Measures].[Loading Value]
AS
(
   [Measures].[Value] * [Measures].[Loading Factor]
)
WITH MEMBER [Measures].[Total Loading Value]
AS
SUM (
  EXISTING [Valuation].[Id].[Id],
  [Measures].[Loading Value]
)
SELECT
{
 [Measures].[Value] ,
 [Measures].[Measures].[Total Loading Value]
} ON 0,
[Valuation].[Scheme] ON 1
FROM Cube