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 for 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 records below 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).

You could also 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.