Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Customer Age

4 posters

Go down

Customer Age Empty Customer Age

Post  jryan Mon Sep 27, 2010 11:26 am

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

jryan

Posts : 33
Join date : 2010-09-27

Back to top Go down

Customer Age Empty Re: Customer Age

Post  ngalemmo Mon Sep 27, 2010 12:20 pm

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

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

http://aginity.com

Back to top Go down

Customer Age Empty Re: Customer Age

Post  Jeff Smith Mon Sep 27, 2010 1:33 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Customer Age Empty Re: Customer Age

Post  hang Mon Sep 27, 2010 7:26 pm

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.


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

Back to top Go down

Customer Age Empty Re: Customer Age

Post  jryan Tue Sep 28, 2010 3:55 pm

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

jryan

Posts : 33
Join date : 2010-09-27

Back to top Go down

Customer Age Empty Re: Customer Age

Post  ngalemmo Tue Sep 28, 2010 4:56 pm

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

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

http://aginity.com

Back to top Go down

Customer Age Empty Re: Customer Age

Post  jryan Wed Sep 29, 2010 10:16 am

Thanks again for the help....

jryan

Posts : 33
Join date : 2010-09-27

Back to top Go down

Customer Age Empty Re: Customer Age

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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