Separate fact tables denominated in weeks and months should be avoided at all costs. Weekly and monthly data is forever incompatible. In last month's column, I stated that one of the central data warehouse team's responsibilities was to make sure that all of the dimensions used by separate divisional data marts were conformed. One of the most important conformation steps is to make sure that a uniform time rollup is used in all of the separate data marts. The most flexible base on which to start is the daily time grain.
Daily data rolls up to virtually every possible calendar.
If your company operates on calendar month reporting, then you should organize around days, months, quarters, and years. It is okay to overlay incompatible seasonal periods based on specific spans of days onto either one of these schemes, but seasonal periods such as Christmas or Easter will not roll up to any of the other calendar periods. Adding seasonal interpretations to the calendar is easy if you start at a daily time grain. This is one of the advantages of the dimensional approach. A data mart designer might be tempted to model a slowly changing dimension such as Product as a kind of fact table with a time key in addition to the original product key, as shown in Figure 3 .