Weekly summary table; how to reference the time dimension

Question

We're thinking about adding a weekly summary table to our little data warehouse. We have a classic time dimension down to the daily level (Year/Month/Day) with the appropriate Week/Quarter/etc. columns.

We'd like to have the time key in this new weekly summary table reference our time dimension. What's the best practice here—have the time key reference the id of the first day in the week it represents? Or the last day? Or something entirely different?

Answer 1

By convention, the fact tables with date period aggregations (week, month...) reference the DateKey of the last day of the period -- so, for this example you would reference the last day of the week.

Kind of logical too, the week must end in order to be aggregated.

It is important to clearly state (somewhere) that the grain of the fact table is one-week, so that report designers are aware of this.