Age - Dimension or Fact

I am looking for help as to whether Age (A persons Age at time of occurance): (Integer) should be a Dimension or simply a Fact attribute. In our system, Age, remains constant over time, so I do not need a DOB column to determine current age over time.

Its a dimension attribute. The fact that it is a dimension attribute has nothing to do with where it is stored.  If you store it on a fact, which is a reasonable thing to do, it is referred to as a degenerate dimension.

ngalemmo wrote:Its a dimension attribute. The fact that it is a dimension attribute has nothing to do with where it is stored.  If you store it on a fact, which is a reasonable thing to do, it is referred to as a degenerate dimension.

Ah, I had brain freeze earlier! "degenerate" was the word that I couldn't recall when writing this! Thanks!

You could do a degenerate dimension but I wouldn't. I would create a dimension table with one or 2 roll ups. Aggregating facts by age usually provides too many rows. you can always change the roll ups later.

Jeff Smith wrote:You could do a degenerate dimension but I wouldn't.  I would create a dimension table with one or 2 roll ups.  Aggregating facts by age usually provides too many rows.  you can always change the roll ups later.

Thanks for your reply Jeff. I guess I'm not seeing your point regarding, "too many rows". Wouldn't there should be the same number of rows in the fact table with either a Dimension table or Degenerate Dimension? If we need to report on specific ages, I would just go to the fact table to get them. If I need to produce some type of rollups, such as ages 0-18, 19-26, >65, I could just create an age_category dimension, but that doesn't change the number of rows, only number of columns in the fact table.

One of the points of a dimensional model is to not force users to "create" data or rollup points. As to "too many" rows, a report by ages that has 100 or more rows (one row for each age) isn't very useful.

Jeff Smith wrote:One of the points of a dimensional model is to not force users to "create" data or rollup points.  As to "too many" rows, a report by ages that has 100 or more rows (one row for each age) isn't very useful.

We don't force user to create data, we simply store pre-aggregated data in our data warehouse. We have reports and graphs that show, for example, graduation programs vertically (X Axis), and Age horizontally, (Y Axis) with total counts in the intersections. If there is no data, the graph displays 0, or in a report the column may not be displayed. Certainly a graph that shows graduation rates over the entire spectrum of age is visually useful if that is what the customer requests.
So back to my original question which was whether to create a dimension with all possible ages and use the FK in the fact table, or just to use the specified age as a "degenerate" dimension. Since age seems to be an implicit key, it isn't clear to me why I would create a dimension to do the same thing. So, in my case, if the meaning or value of "Age" changed over time, then I understand that creating a dimension would be more appropriate, however, that is just not a possibility.

Although it may seem like overkill for the initial requirement, I'd lean towards creating an age dimension to allow for future flexibility.

For example, if in the future you get a requirement for age-categories/bands, you could add those attributes to the age dimension, eliminating the need to modifiy/reload your fact tables.

Also, the age dimension could be beneficial when you get a requirement to see ages with no data ....


just some thoughts ....

LAndrews wrote:For example, if in the future you get a requirement for age-categories/bands, you could add those attributes to the age dimension, eliminating the need to modifiy/reload your fact tables.

thanks LAndrews! We do have reporting categories of age, however, they overlap. For example, 0-13, 0-18, 18-21, 13-21. We handle this "on-the-fly" categorization in our reporting tool. I am not quite sure how this would be handled in the age dimension by adding category, as certainly, there would be multiple PK id's for the same Age. Can you elaborate, as I'm just not sure how to model that?

Multiple categories for the same age can be modelled in a couple different ways.

If the groups of categories is fixed, then you can just add multiple category attributes to the age dimension (e.g. Sales_Age_Category, Finance_Age_Category).

If the groups of Categories are large or unknown/changing, then you'd create an age_category dimension, which has a many-to-many relationship with your fact table, so you may want to leverage a bridge table. Kimball provides many examples of this approach.

LAndrews wrote:Multiple categories for the same age can be modelled in a couple different ways.

If the groups of categories is fixed, then you can just add multiple category attributes to the age dimension (e.g. Sales_Age_Category, Finance_Age_Category).

If the groups of Categories are large or unknown/changing, then you'd create an age_category dimension, which has a many-to-many relationship with your fact table, so you may want to leverage a bridge table. Kimball provides many examples of this approach.

Thanks again everyone, a degenerate dimesion it is. for now...