Search code examples
sql-serverssasbusiness-intelligenceolapcube

Many to Many Currency Conversion at Different Granularities in SSAS Multidimensional

I have a sales cube in SSAS that I am developing. I have a measure group for the actual sales called "invoiced sales", and a Measure group for the sales forecast called "Sales Budget". The invoiced sales has the individual invoice line level detail. The Sales budget is an estimate on a month by month basis. So, the sales budget measure group has it's granularity set to the month level. I have it set to show the sales budget numbers when looking at the cube at the month level or higher, when you drill down lower than the month level then the budget numbers go away.

Anyway, both the invoiced sales and the sales budget can be in different currencies. I am working on a many to many currency conversion so that the user can pick the desired currency and it will convert everything to it. I have done this successfully with both the business intelligence wizard generated script, and the manual method, Following these videos.

https://www.youtube.com/watch?v=DHuqEvphE4I

https://www.youtube.com/watch?v=gMCIu5Nh93M

However, with either of these methods, I get an error if I try to include any of the metrics in the Sales Budget measure group because it is set to a different granularity. My data model in the data warehouse, cube, etc. are set up identically to those in the videos except that I have an extra fact table for the sales budget that is linked to the other tables just like the sales fact table is. I see no reason why this is logically impossible to achieve, and there must be a work around. I am using SQL Server 2012 Enterprise SP3.

Here are a couple of screen shots of my set up after completing the second video.

Data Model

Dimension Usage

Cube Measures

Solution

  • A simple solution would be to add a second measure group "Monthly Currency Rates" which offers currency rates on a monthly granularity. Use this new measure group for your M:N relationship between dimension "Reporting Currency" and fact "Sales Budget".

    For a quick test create a view of your fact "Currency Rates" where you select only the first day each month and create a key/sid for a join on monthly granularity with your date dimension. (Add this view to your data source view in ssas if you created it on your SQL DB) and add the view as fact to your cube and assign monthly date relationship and reporting currency relation. Finally update your M:N relationship between dimension "Reporting Currency" and fact "Sales Budget" with your created monthly currency fact.

    Edit: Don't forget to change your measure expressions for the measures of fact "Sales Budget" to your new monthly currency rates fact table measure.