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

如何覆盖用于负债计算的SSAS商业智能“货币转换”

ssas mdx

Meff · 技术社区 · 15 年前

我正在analysis services中建立一个新的多维数据集,并使用商业智能向导解决货币转换问题。现在,这一切都完美地工作了,货币在叶级进行转换,并在用户选择的报表货币中汇总显示。

我现在的问题是债务的计算。对于负债,我需要对每种货币的金额进行汇总,然后使用最新的“日终汇率”进行换算。我将“日终率”作为最后的非空度量,但我看不出如何避免叶级转换,如下所示:

// This is the Many to One section  
// All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension 
Scope ({ Measures.[Money] } ); 
  Scope( Leaves([Date]) ,[Reporting Currency].[GBP], Leaves([Currency])); 

    // Convert Local value into Pivot currency for selected Measures that must be         converted with Measure rate [End Of Day Rate] 
        Scope( { Measures.[Money] } )
            This = [Reporting Currency].[Local] * Measures.[End Of Day Rate]; 
        End Scope; 
  End Scope;    

  // This is the One to Many section
  // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension   
  Scope( Leaves([Date]) , Except([Reporting Currency].[Currency].[Currency].Members, {[Reporting Currency].[Currency].[Currency].[GBP], [Reporting Currency].[Currency].[Currency].[Local]})); 

    // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [End Of Day Rate]â¦ 
    // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
    Scope( { Measures.[Money] } ); 
        This = [Reporting Currency].[Currency].[GBP] / (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])); 
    End Scope;

  End Scope;  // Leaves of time, all reporting currencies but local and pivot currency  
End Scope;  // Measures

货币是以不同的货币支付的,每种货币都有一个货币维度和一个“日终汇率”。

我计算负债的最佳计划是什么?我正在考虑复制[货币]度量,但是为了避免货币转换而有额外的度量似乎是浪费——另外,在真实的立方体中还有几个度量需要计算,因此它不仅仅是额外的度量。

还有人面对类似的事情吗?

1 回复 | 直到 15 年前

Meff 15 年前

好吧,所以我最终创建了一个不可见的[度量值]。[金钱-负债],它不是由上面的代码自动转换的,最后我在脚本中进行了以下计算:

[Measures].[Liability] = 
(
    SUM 
    ( [Currency].[Currency].[Currency], 
        SUM 
        ( 
            { NULL : [Date].[Date Key].CurrentMember }, 
           (   
                [Money - Liability]
            )
      ) 
      / [Measures].[End Of Day Rate] 
    ) 
    * (Measures.[End Of Day Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency]))
);