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?
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.
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