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?

Answer 1

Got this figured out finally. Sorry I don't have screenshots. I don't have the project available at the moment. If it is desired, let me know, and I can add them later.

You have to create a many to many relationship with two UOM dimension tables, a from and a to UOM dimension table that are identical, with the uom conversion table in between, with an entry for the conversion rate for each direction. Including a conversion with a rate of 1 each way in the fact conversion table when the UOM is the same, like ST to ST rate is 1.

Relationship foreign keys are like this.

1. fact_sales <-- 2. dim_from_UOM --> 3. fact_OUM_conversion <-- 4. dim_to_uom

After you create this, then set up the many to many in the relationship usage tab, and then a measure expression to multiply your conversion rate.

Similar set up as the typical many to many currency conversion, as shown in the youtube video below, but the dim_from_uom replaces your date table. https://www.youtube.com/watch?v=gMCIu5Nh93M

Answer 2

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