Age - Dimension or Fact

View previous topic View next topic Go down

Age - Dimension or Fact

Post  nseidlitz on Wed Dec 04, 2013 8:49 am

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

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  ngalemmo on Wed Dec 04, 2013 11:32 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Age - Dimension or Fact

Post  nseidlitz on Wed Dec 04, 2013 12:41 pm

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!

nseidlitz

Posts : 6
Join date : 2013-12-04

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  Jeff Smith on Wed Dec 04, 2013 2:43 pm

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

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  nseidlitz on Thu Dec 05, 2013 7:54 am

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.

nseidlitz

Posts : 6
Join date : 2013-12-04

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  Jeff Smith on Thu Dec 05, 2013 9:27 am

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

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  nseidlitz on Thu Dec 05, 2013 10:41 am

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.




nseidlitz

Posts : 6
Join date : 2013-12-04

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  LAndrews on Thu Dec 05, 2013 12:26 pm

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

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  nseidlitz on Fri Dec 06, 2013 8:36 am

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?

nseidlitz

Posts : 6
Join date : 2013-12-04

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  LAndrews on Fri Dec 06, 2013 2:06 pm

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

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  nseidlitz on Fri Dec 06, 2013 2:47 pm

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


nseidlitz

Posts : 6
Join date : 2013-12-04

View user profile

Back to top Go down

Re: Age - Dimension or Fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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