Finally, our last set of best practices on data modeling involves dealing with dates and times. When analyzing data, time often plays an important role. Initially, it's not much of the individual transactions and events that users are interested in, but rather the rolled up totals per period, or trends over multiple periods.
Source systems usually record the date at which a particular transaction or event took place, but do not contain any further information for time grouping. This makes sense, as transactional systems strive not to include redundant data. In our QlikView documents, however, we strive to make the selections and aggregations as easy as possible for our users. That is why, in addition to the original date, we include attributes such as the month, quarter, and year components in our data model.
Rather than placing these attributes directly in our fact table, as we've done until now, the best practice is to create a separate master calendar dimension table...
