Multiple Attributes for Fact Table

[Goodie666]

Hello,

I'm in the process of modeling a fact table that tracks the history of a certain product (arguably not really a fact table but that's another question altogether...). My problem is that there is a need to show an 'old value' and a 'new value' for changes in this product's attributes. I'm wondering how I can represent this. I've been thinking of varchar fields for old and new value but this may actually need to be dimensions, but depending on the type of history, it may relate to a different dimension.

So I'm wondering if I'm not better off having for example an OLD_PRODUCT_ID and NEW_PRODUCT_ID and other attributes OLD_GROUP_ID and NEW_GROUP_ID etc... for all the possible scenarios and fill the appropriate fields based on the type of evolution recorded. The varchar fields are a good idea but not practical for translation or dimension changes... Otherwise if I have only 2 attributes 'old' and 'new' I'm not sure it'll be very clear for users designing reports...

Any opinion on how to handle this type of situation is welcome

Regards,

Greg

 

[blom0344]

Changes in the attributes are usually covered with a slowly changing dimension table.
You mention OLD_PRODUCT_ID and NEW_PRODUCT_ID, but from your story I gather that attibutes change and not the ID's of a product?

Ties Blom

 

[Goodie666]

Hello,

I think I was not very clear sorry about that. What I mean is that my fact tracks changes to my product. The changes can be for a change in price or a number of other things. There is an attribute change_type that defines what the change is. My problem is that the old and new values will refer to different dimensions based on the type of change happening. I was just wondering how people usually handle this type of case.

Regards,

Greg

 

[blom0344]

We may have a different view on the concept of dimensions, but if you track attribute changes of products ,then these will always be related to to the dimension product?
Or do you consider product price to be a dimension?

From your example it looks like you are building an event type of table that seems to match the slowly changing dimension concept on the product dim?

Ties Blom

 

[Hans63]

Isn't this fact table actually a way to split of the changing aspect of your product dimension? And normalized at that.
Why do you want it this way?
Is it due to the size and the change-rate of the dimension? Or do you specifically want to check the changes? The last can be done by adding an attribute field to the dimension as well, that records the changes compared to the previous values.

 

[Goodie666]

Hello,

Thanks for the replies. I think talking about this issue made me realize a few things... I was trying to treat a fact like a dimension and that was not good... I wanted to have the descriptive value of my fact be part of another dimension when it should have been part of the fact to begin with.

Many thanks, this discussion has helped me figure that one out

Greg