For an aggregated fact table which requires its data to be dimensioned by gender and age (in years), is there any benefit in creating these as dimensions at all even if it may help in cross fact analysis or not to bother as the gender and age values would be the same as the dimensional key values (i.e., age_key 16 for 16-year-olds)?


Chuck Kelley's Answer: This will depend on your physical design. If you can use bit map indexes in your fact table, then I would place them there. If not, then I would create separate dimensions. But my first question for you to think about is "is it true that year will not need to be changed every year?" You are basing your decision on that question and it is important to get it right.

Clay Rehm's Answer:This is a good question. Regarding the gender, there should be three values - M for Male, F for Female and U for Unknown. If you make the key the code M, F or U, there is really no need for a dimension table unless your users need to see the description of the gender (Female instead of F).

This would hold true for the age - assuming you made the age the key, you possibly could create a column with the age spelled out such as sixteen. Again, you could argue that this is not needed unless your users needed to see the spelled out age.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access