Having fact in a dimension

[atc23]

We have a dimension that contain analysis code by plant and product group with a minimum and maximum value for the result

Fact table record every analysis transactions with the relust for each analysis code. They want to compare analysys result to the min/max value if the result is accurate at the transaction level, by day, week, plant.

The min/max value could change at any time and all the transactions in fact table should be compare to the new min/max for that plant/product group/analysis code.

Keeping the min/max value as a fact in the dimension table does't work when trying to compare at the transaction level. should we keep the min/max value on each fact record. If the min/max change do we need to perform like a SCD type 1 but in the fact table ?. for each record

By example

Dimension

Plant 001, product group 100, analysis code 001 and min/max value as 3.52 / 4.78

Fact table

transaction 123, date 2009/12/27, plant 001, product group 100, analysis code 001 result 4.01

Report

transaction plant group analyse code metric min result max
123 001 100 001 3.52 4.01 4.78

[VHF]

Why doesn't keeping the min/max value as a fact in the dimension table work when trying to compare at the transaction level?

If you really don't need history of min/max values, and you always want to compare a fact record against the most current min/max values, storing them in a SCD Type 1 Dimension would seem to work. A simple star-schema join will return the min/max values for each analysis code/product/plant group. (You should have a surrogate key (SK) on the dimension table and use that as the FK in the fact record.)

[ngalemmo]

Are these min/max values the current min/max of the facts or are they tolerance bounds the user is supplying?

If they are the min/max of the facts, you should be able to caclulate it on the fly in most BI tools using an alias or view (2 queries joined on conforming dimensions). If they are tolerance bounds entered by the user then they are simply attributes that are best kept in a dimension table.

[Sponsored content]