Age - Dimension or Fact
4 posters
Page 1 of 1
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.
nseidlitz- Posts : 6
Join date : 2013-12-04
Re: Age - Dimension or Fact
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.
Re: Age - Dimension or Fact
Ah, I had brain freeze earlier! "degenerate" was the word that I couldn't recall when writing this! Thanks!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.
nseidlitz- Posts : 6
Join date : 2013-12-04
Re: Age - Dimension or Fact
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- Posts : 471
Join date : 2009-02-03
Re: Age - Dimension or Fact
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.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.
nseidlitz- Posts : 6
Join date : 2013-12-04
Re: Age - Dimension or Fact
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- Posts : 471
Join date : 2009-02-03
Re: Age - Dimension or Fact
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.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.
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.
nseidlitz- Posts : 6
Join date : 2013-12-04
Re: Age - Dimension or Fact
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 ....
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- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: Age - Dimension or Fact
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?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.
nseidlitz- Posts : 6
Join date : 2013-12-04
Re: Age - Dimension or Fact
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.
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- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: Age - Dimension or Fact
Thanks again everyone, a degenerate dimesion it is. for now...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.
nseidlitz- Posts : 6
Join date : 2013-12-04
![-](https://2img.net/i/empty.gif)
» Dimension Design with intermediate tables between fact and dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum