Customer Age
4 posters
Page 1 of 1
Customer Age
Hi,
Does anyone know the best way to model customer age? I guess there are 3 options: 1)Just their birth date, 2)Their current age as an attribute, 3)their age as an attribute but make it a type 2 scd so that you get all their age history.
This is for a sales model. Ideally I'd like to know the age that a customer was when they bought a particular product. However, having age as a type 2 scd just doesn't seem quite right to me.
What does everyone think is the best way to model age?
Thanks
John
Does anyone know the best way to model customer age? I guess there are 3 options: 1)Just their birth date, 2)Their current age as an attribute, 3)their age as an attribute but make it a type 2 scd so that you get all their age history.
This is for a sales model. Ideally I'd like to know the age that a customer was when they bought a particular product. However, having age as a type 2 scd just doesn't seem quite right to me.
What does everyone think is the best way to model age?
Thanks
John
jryan- Posts : 33
Join date : 2010-09-27
Re: Customer Age
Normally you store the birthdate in the customer dimension.
If you want point in time attributes, a junk dimension (aka mini-dimension) with various attributes of interest, such as age, can be created and referenced from the facts.
The latter is common in medical claims where it is important to know what the reported age and other demographics were on the claim. You would typically have demographic information in the person dimension, including DOB, but there would also be a demographic dimension which is used to record the 'as reported' information on the claim. The latter would store age rather than DOB.
If you want point in time attributes, a junk dimension (aka mini-dimension) with various attributes of interest, such as age, can be created and referenced from the facts.
The latter is common in medical claims where it is important to know what the reported age and other demographics were on the claim. You would typically have demographic information in the person dimension, including DOB, but there would also be a demographic dimension which is used to record the 'as reported' information on the claim. The latter would store age rather than DOB.
Re: Customer Age
If you just want the age and will never expect to display the data in ranges, then the age as a digenerate dimension will work or include it as part of a junk dimension. But, if you can ever see the need to aggregate the customers into age ranges, then create a member age dimension. I doubt that you will ever have a report that displays the frequency of customers by every single age. I would bet that such a report would group ages into ranges that match the target ages for various merchandise.
You don;t need to have the age dimension perfectly thought out. As long as the dimension is at the age level, you can add various ranges to it later.
You don;t need to have the age dimension perfectly thought out. As long as the dimension is at the age level, you can add various ranges to it later.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Customer Age
As ngalemmo and Jeff suggested, age should be separated out from your customer dimension and have direct entry in the fact either through a mini-dimension or a stand-alone age range dimension. Obviously the logic behind the age is the sales date and DOB.
With mini-dimension, age needs to be grouped into different ranges based on the reporting requirement so that it will not blow out the relatively small mini-dimension.
I would also put age in the fact tabel regardless, as a degenerate dimension as well as a potential fact in case you ever need to aggregate on it, say average age. The individual age in the fact will also allow you to easily re-assign the age-range/mini-dimension keys, without rebuilding the fact, if the age ranges need to be redefined.
With mini-dimension, age needs to be grouped into different ranges based on the reporting requirement so that it will not blow out the relatively small mini-dimension.
I would also put age in the fact tabel regardless, as a degenerate dimension as well as a potential fact in case you ever need to aggregate on it, say average age. The individual age in the fact will also allow you to easily re-assign the age-range/mini-dimension keys, without rebuilding the fact, if the age ranges need to be redefined.
Last edited by hang on Wed Sep 29, 2010 6:59 am; edited 1 time in total
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: Customer Age
Thanks for the replies,
I'm a bit surprised though about the seperate dimension. To me Age should belong to the Customer dim, rather than making it as a seperate dimension.
I appreciate the points though - I guess as the age always changes every year - it would just explode the customer dimension too much. My dimensions are relatively small, but if its not accepted practice to make age an attribute of Customer then I'll give it a miss
I'm a bit surprised though about the seperate dimension. To me Age should belong to the Customer dim, rather than making it as a seperate dimension.
I appreciate the points though - I guess as the age always changes every year - it would just explode the customer dimension too much. My dimensions are relatively small, but if its not accepted practice to make age an attribute of Customer then I'll give it a miss
jryan- Posts : 33
Join date : 2010-09-27
Re: Customer Age
From the standpoint of the customer, age is transitory and change is predictable. You don't want to get into a situation where you have some batch process that regularly generates new rows (in a type 2 dimension) to keep age up to date... or even updating a type 1. It's their birthdate that is static. Age can always be calculated very easily at any time.
On the other hand, from the standpoint of a transaction, age at the time of the transaction is static.
You need to treat attributes in their proper context. If you need age at the time of the transaction the best approach is to record it as a dimension. Either a degenerate dimension, if that is the only thing you are concerned about, or in a full dimension with other attributes.
On the other hand, from the standpoint of a transaction, age at the time of the transaction is static.
You need to treat attributes in their proper context. If you need age at the time of the transaction the best approach is to record it as a dimension. Either a degenerate dimension, if that is the only thing you are concerned about, or in a full dimension with other attributes.
Similar topics
» Merging customer data from disparate sources to create a master customer dimension
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» customer & customer account
» Customer Contacts
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» customer & customer account
» Customer Contacts
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|