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.