tTimeGenerator

Background : In many Data Mart / Data Warehouse projects, we need a time dimension, normally with 3 levels, being Year, Quarter and Month.
This dimension is used in most of the analysis and also to calculate special time rollups (YTD, QTD, MAT etc).
Most of the BI consultants already have a script to generate and populate the needed tables in ORacle , MS SQL Server etc.
We are here proposing a simple implementation using a single Talend component.

The solution

A single component can generate values for the 3 levels (Year, Quarter, Month) in a specified period. It is also possible to join the tables to create a single time dimension table if needed.
No name decoding for the months has been embedded since this would depend from the language needed, however this can be easily achieved adding a csv file with the month names, such as the following :

NONTH_NUMBER,MONTH_NAME_SHORT,MONTH_NAME_LONG
1,"Jan","January"
2,"Feb","February"
3,"Mar","March"
4,"Apr","April"
5,"May","May"
6,"Jun","June"
7,"Jul","July"
8,"Aug","August"
9,"Sep","September"
10,"Oct","October"
11,"Nov","November"
12,"Dec","December"

The generated tables have a fixed schema, containing the basic information, you can erich it using a tMap component and adding eventual calculated fields etc.

YEAR

Field Name	Field Type	Description
YEAR_ID	Integer	Year Unique Identifier in the format YYYY
YEAR_NUMBER	Integer	Same as YEAR_ID, in the format YYYY
IS_LEAP	Integer	Flag that indicates if the year is leap, values are 1=true, 0=false

QUARTER

Field Name	Field Type	Description
QUARTER_ID	Integer	Quarter Unique Identifier in the format YYYYQQ
YEAR_ID	Integer	Foreign Key to the Year Table
QUARTER_NUMBER	Integer	A number from 1 to 4 representing the Quarter within a year
QUARTER_PROGRESSIVE	Integer	It is a unique number that identifies the Quarter and allows simeple aritmetics between quarters. Progressive values can be subtracted to identify deltas between two quarters or a numerical constant can be added or subtracted to find the value of X quarter ahead (or behind)

MONTH

Field Name	Field Type	Description
MONTH_ID	Integer	Month Unique Identifier in the format YYYYMM
QUARTER_ID	Integer	Foreign Key to the Quarter Table
YEAR_ID	Integer	Foreign Key to the Year Table
MONTH_NUMBER	Integer	A number from 1 to 12 rfepresenting the month within the year
MONTH_PROGRESSIVE	Integer	Unique number that can be used to perform calculations between two periods, similar to the QUARTER_PROGRESSIVE
DAYS_IN_MONTH	Integer	Number of calendar days within the specific month.It uses the "is_leap" information to calculate the exact number of days in February

Current version

Version : 0.4
Release Date : Sep 11 2012
Status : Beta

Example

The job tTimeGenerastorDemo demomstrates the usage of the tTimeGenerator component

This simple job shows how to use tTimeGenerator.
This component needs only two parameters being the start and end period, specified in numeric format YYYYMMDD.
To have all the months between Jan 1980 till Dec 2040, the two parameters will be 19800101 and 20401231, note that currently the component is not using the day information, therefore the two last digits can have any value.

The left subjob explains how to use the component to generate 3 different tables, one for Year, one for Qaurter and one for Month.Obviously it is not mandatory to generrate all three of them.
It also shows how to add other information, such as "month long name" and "month short name" in this case from a csv file.

The right subjob instead uses multime tTimeGenerators and joins them in a tMap to create a single time dimension table.Generally, in BI projects you may want to have the 3 levels separated (that eases relative time calculations, prompts etc), but with Talend it is easy to use either approach (also both two if needed).

Downloads

Download tTimeGenerator component

Download sample Job Remember to configure the location of the month_en.csv file

Download sample month data

License

THIS SOFTWARE IS PROVIDED BY POWERUP ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL POWERUP BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.