Many to Many Unit Conversion SSAS Multi-Dimensional

Question

I have a sales fact table, and the sales can happen in different weight units: ST, MT, lb, kg, etc. The users like to see it in different units of measure (UOM), depending on where they live usually. I wanted them to be able to pick a UOM and it would show all the quantities in that UOM. Same way you would with a many to many currency conversion. It is a little different than currencies though because you don't have a date table in between the two fact tables.

In short how would I design the UOM dimension, and fact conversion rate tables, the relationship between them and the fact sales table. Lastly how would I put this in the cube. Can I do it with a many to many relationship with a measure expression, or is there some sort of calculation that I need to make?

John Cappelletti John Cappelletti · Accepted Answer · 2016-12-21T21:59:50

If it helps, the following is a stripped down version of my conversion function. Only showing MASS here, but the technique is easily extended.

The simple trick is storing all conversion factors in base units, then the math is simple. You may notice that I store as varchar() and then perform then recast. This way the precision is dynamic

Declare @Conversion table (MapType varchar(50),ConvUnit varchar(50),ConvFactor varchar(50))
Insert Into @Conversion values
('Mass','tonnes (metric)','1000'),
('Mass','tons (US)'      ,'907.18474'),
('Mass','tons (UK)'      ,'1016.0469088'),
('Mass','stones'         ,'6.35029318'),
('Mass','slugs(g-pounds)','14.593903'),
('Mass','Solar masses'   ,'1.989e30'),
('Mass','pounds (troy)'  ,'0.3732417216'),
('Mass','pounds'         ,'0.45359237'),
('Mass','picograms'      ,'1e-15'),
('Mass','ounces'         ,'0.028349523'),
('Mass','ounces (troy)'  ,'0.0311034768'),
('Mass','nanograms'      ,'1e-12'),
('Mass','milligrams'     ,'1e-6'),
('Mass','micrograms'     ,'1e-9'),
('Mass','megatonnes'     ,'1e9'),
('Mass','kilotonnes'     ,'1e6'),
('Mass','kilograms'      ,'1'),               --- << Base
('Mass','hundredweights' ,'50.80234544'),
('Mass','hectograms'     ,'0.1'),
('Mass','grams'          ,'1e-3'),
('Mass','grains'         ,'0.00006479891'),
('Mass','femtograms'     ,'1e-18'),
('Mass','Earth masses'   ,'5.980e24'),
('Mass','decagrams'      ,'0.01'),
('Mass','cental'         ,'45.359237'),
('Mass','carats (metric)','0.0002')

So for example, One Conversion

Declare @Value float      = 1
Declare @From  varchar(50)= 'tonnes (metric)'
Declare @To    varchar(50)= 'pounds'

-- For just the single conversion
Select @Value * Max(IIF(ConvUnit=@From,cast(ConvFactor as float),null))  / Max(IIF(ConvUnit=@To,cast(ConvFactor as float),null)) 
      ,@To
 From  @Conversion 
 Where ConvUnit in(@From,@To)

Returns

2204.62262184878    pounds

Now, If you want ALL conversions for a value

-- To Convert ALL
Select Concat(@Value,' ',@From)
      ,@Value * (Select cast(ConvFactor as float) from @Conversion where ConvUnit=@From) / cast(ConvFactor as float)
      ,ConvUnit
 From  @Conversion

Returns

Many to Many Unit Conversion SSAS Multi-Dimensional

2 Answers