Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?

[mpalmerlee]

I would like to create some summary tables that would summarize Monthly data. Should I use my existing Date Dimension and simply link to the first day of the month or should I create a new Month Dimension Table?

I know it would work either way but I'm looking for what is considered best practice, thanks,
-Matt

[ngalemmo]

Thats fine, pick a row, there is no need to create another table. It would be different if dealing with a very large dimension where you only need a few attributes due to the desired aggregation, in which case a much smaller junk dimension for those attributes would be a better choice.

[mpalmerlee]

Ok, that makes sense. At first it seemed cleaner to have a month dimension because it would be obvious to anyone using your fact table that the Date keys pointed to the whole month, but since you probably want summaries at a week, a quarter and a year too, it quickly becomes a pain to manage all these separate dimensions and probably just easier to pick something that makes sense in the existing table to maximize re-usability and flexibility.

[Sponsored content]