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 |
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.