What is a good design to store unit of measures ?
We have a measure dimension with all the measures and we are building a scorecard fact table that leverages these measures and build performance metrics.
I could think of three options with third one as a best pick:
Option 1: Build a UOM dimension and snowflake it to Measure dimension
Option 2: Build a stand alone UOM dimension and use that in the scorecard fact table
Option 3: Include UOM column in the fact table so that if a measure need to be calculated in 2 different ways mis-calculations can be limited unlike in above options.
If there is any other better approach please advise. Much appreciated. Thanks!