Q:  

In designing a star schema for grocery, we have promotion dimension. Should we have the promotion key as part of the primary key of the fact table? Another question about the same dimension, do I have to consider the promotion to be in the grain?

A:  

Les Barbusinski's Answer: It depends.

If, for example, you have an aggregated fact table that only contains promotional sales data, then yes, you would have to include the promotion key within the PK of the fact table. In other words, since the promotion helps define a specific row on the fact table (i.e. it defines its "grain") it must be included in the PK.

If, on the other hand, you have an atomic-level sales fact table, then you would not include the promotion key in the PK of the fact table. In other words, since an atomic sales transaction can exist with - or without - a promotion, the promotion code is not needed to define a specific row on the fact table ... hence, its key should not be in the PK of the fact table (although it does have to appear as an FK in the fact table). Hope this helps.

Chuck Kelley's Answer: You can answer your own question. Since the Fact is the intersection of the dimensions, by definition, if the promotion key is part of the primary key of the fact table, then it must be part of the grain. So, in your case, you need to be able to determine whether it is correct for your star schema to have it part of the grain. I would bet yes.

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