×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Multiple Attributes for Fact Table

Multiple Attributes for Fact Table

Multiple Attributes for Fact Table

(OP)
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

RE: Multiple Attributes for Fact Table

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
 
 

RE: Multiple Attributes for Fact Table

(OP)
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

RE: Multiple Attributes for Fact Table

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
 
 

RE: Multiple Attributes for Fact Table

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.

RE: Multiple Attributes for Fact Table

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close