Dynamic age of customer based on transactions being viewed

Hi

I am after some ideas on how to deal with customer ages and dynamically linking them to transactions. For example if I purchased something 2 years ago I would be obviously 2 years younger then now but if I have age as a standard dimension all of my purchases would be tied to my current age, assuming age is updated and overwritten. The ideas I have are a slow changing dimension which means that I would have a new entry per customer's birthdate or somehow tie the age calculation to the transaction date. The reason I want this is that the purchasing decisions of say a 20 year old are different than a 25 year old for us. Anyone dealt with this type of issue before.

Thanks in advance.

Derek

One approach, which would avoid a new entry per birthday, is to compute the age by joining the transaction fact table to the customer table in a SQL view (or named query in AS 2005). There could be an "Age" dimension table, starting at the day level, which is joined to the computed age in days (eg: DateDiff("D", TransDate, BirthDate)).

Obviously, if the BirthDate of the customer changes (assuming that's even permitted), then there will have to be a new entry in the Customer dimension table.

|||thanks for the idea Deepak, I'll have a look into that option. I suppose I'm basically adding the slow changing dimension to the fact table.|||It'll be interesting to see which solution works best in your scenario, because there must be others working on similar problems.