Capturing Age at point in time - SCD2 / Calculated Member/ New Dimension??

[meb97me]

I have come up against the design issue of identifying how old a customer was at a particular point for various events that have happened to them stored in our main event fact table.

At present the custoomer dimension just captures their DOB and then in the DSV in SSAS we have a calculated member for AGE but this obviously only gives us their CURRENT age.

I have idenified 3 possible was round this
1) Create an AGE dimension (or add the values to the existing Junk dimensionwe have) and capture the appropriate SK in the FACT table along with the CustomerSK
2) Treat the age attribute as a SCD type 2 change, but this is likely to create a monster dimension table as each year a new row is created for each customer
3) Create a "Age at Event Date" calculated member which would would calculate a customers age for the given events chosen and time period

any comments/suggestion/previous experience very much welcomed

Cheers

Matt

[gvarga]

Hi,
I would creat a bracketed AGE dimension

SurrKey
Low value
High value

During the ETL process you will calculate from the date of the transaction and from the birthdate of the customer ( stored in the Customer dimension) the SurrKey of the Age dimension.

The brackets must be agreed with your users.

[Jeff Smith]

I would create an Age Dimension that has AGE as it's lowest level and roles up to various buckets. It could also have indicators for Minor Vs Adult, or any other roll ups that make sense for your business. If you have products aimed at particular age groups, I would inclde those age groups in the dimension.

You could put the Age Dimension Key on the Fact Table to capture the age at the time of the event but you could also attach the dimension to the Member dimension (snow flake) to capture the members Current age.

[hang]

I would calculate the age and leave it in the fact as both measure and degenerate dimension. In SSAS you can configure it as a fact dimension if you are insterested in individual age. For the bracketed age group, you may put the relevant hierachical attributes in a junk dimension if you have one, otherwise create a new age group dimension and include the FK in the fact.

However, don't try to link the age group dimension to the member for current age as it could change everyday. If you do need the current age group, work it out on the fly based on DOB and current date.

Please refer to the similar topic as follows:

http://forum.kimballgroup.com/t742-customer-age

[meb97me]

thanks as always for the reply gents

i think seperate AGE dimension will do us with the FK in the Fact table

thats for the other link as well hang, i did a search but must have missed that

Regards

[Sponsored content]