如何覆盖 SSAS 商业智能“货币转换”用于责任计算
我正在 Analysis Services 中设置一个新的多维数据集,并使用商业智能向导来解决货币转换问题。现在这一切都很完美,货币在叶级别进行转换并汇总以显示在用户选择的报告货币中。
我现在的问题是责任的计算。对于责任,我需要总结每种货币的金额,然后使用最新的“日终汇率”进行转换。我将“日终汇率”作为 LastNonEmpty 度量,但我不知道如何避免叶级转换,如下所示:
// 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
[Money] 度量以不同的货币支付,并且每种货币都与一个货币维度和“日终汇率”。
我计算责任的最佳计划是什么?我正在考虑复制 [金钱] 度量,但仅仅为了避免货币转换而添加额外的度量似乎很浪费 - 另外,在真实的多维数据集中还有其他几个需要计算的度量,因此它不仅仅是额外的一。
还有其他人遇到过类似的事情吗?
I'm setting up a new cube in Analysis Services, and used the Business Intelligence wizard to work out the currency conversion issues. Now this all works perfectly, money is converted at the leaf level and summed up for display in the user's choice of reporting currency.
My problem now is the calculation of liability. For liability, I need to sum up the money in each currency, then convert it using the most recent 'End of Day Rate'. I have 'End of Day Rate' as a LastNonEmpty measure, but I can't see how to avoid the leaf-level conversion as shown below:
// 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
The [Money] measure is paid in in different currencies, and each currency is keyed to a currency dimension and an 'End of Day Rate.
What is my best plan for calculating the liability? I'm considering replicating the [Money] measure, but it seems wasteful to have extra measures in just to avoid the currency conversion - Plus in the real cube there are several more measures that require the calculation so it won't just be the extra one.
Anyone else faced something similar?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
好的,我最终创建了一个不可见的 [Measures].[Money - Liability],它没有被上面的代码自动转换,并且我最终在脚本中进行了以下计算:
OK so I ended up creating an invisible [Measures].[Money - Liability] which wasn't auto-converted by the above code, and I ended up with the following calculation in the script: