Generate Time Dimension
Generate Time Dimension creates date records, one for each day of the date range you specify. You can then write these records to a time dimension table in a database using the Write to DB stage. The time dimension table can then be used to perform accurate calculations based on a time period. For example, sales by quarter, budget spend by quarter, and revenue by day are all analyses that require a time dimension. Time dimension tables also enable you to account for fiscal years or non-standard quarters in the analysis.
Example Use of a Time Dimension Table
Time dimension tables are necessary for accurate time-based calculations because you sometimes cannot easily extract the necessary date data from the records. For example, the following records are in a sales database. Note that there are time gaps between records. For example, there is no record for the day 1/4/2012.
| Date | Product | Amount |
|---|---|---|
| 1/3/2012 | Red Shirt | $10.00 |
| 1/5/2012 | Red Shirt | $5.00 |
| 1/7/2012 | Red Shirt | $15.00 |
If you query these records and calculate the average sales per day, the answer would be $10.00 ($30 / 3 records). However, this is incorrect because the three records actually span a period of five days. If you have a time dimension table with a record for each day, you could join that table with the above table to get this:
| Date | Product | Amount |
|---|---|---|
| 1/3/2012 | Red Shirt | $10.00 |
| 1/4/2012 | ||
| 1/5/2012 | Red Shirt | $5.00 |
| 1/6/2012 | ||
| 1/7/2012 | Red Shirt | $15.00 |
Calculating the average sales per day using these records, you would get the correct answer: $6.00 ($30 / 5 days).
In addition, you could account for arbitrary time attributes such as holidays, weekends, and quarters in your calculation. For example, if 1/6/2012 happened to be a holiday and you were only interested in average sales per workday then the answer would be $7.50.