Having fact in a dimension

View previous topic View next topic Go down

Having fact in a dimension

Post  atc23 on Sun Dec 27, 2009 10:56 am

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

atc23

Posts : 3
Join date : 2009-12-27

View user profile

Back to top Go down

Re: Having fact in a dimension

Post  VHF on Mon Dec 28, 2009 11:31 am

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

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Having fact in a dimension

Post  ngalemmo on Mon Dec 28, 2009 12:32 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Having fact in a dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum