The age problem

Hi

I'm looking for some guidance on how to model the age of ppl in a dimensional model.
If the requirements are to be able to slice the cube, on age of members/customers etc, within the relevant time context.

e.g.
A development graph, of the sum of pension saving, an average 50 years old person have, over the last 10 years.

To answer this question, we will need to know which of our members were 50 years old, in the last 10 years, but it's by nature not the same members.

I thought that making an age dimension, and use it as a key in the fact tables which are relevant. attributes to the age dimension will be relevant age groups. The ETL process will be responsible to resolve the age from the birthday of the member, and the timestamp of the fact.

Any input, ideas, other solution?

Which age? For current age, it is calculated as needed based on the birth date. For some industries, such as health care, they also store an age value with claims and admittance information to record the stated age of the patient at the time of the event.

The current age is rather elementary, but being able to group members events/measure, by the age of the member when the event happend, that's the non trivial problem.

You have a table with age bands (low and high) with a description (label) for the band. Join by age between the band and aggregate on the label.