PowerUp
Talend Time Dimension component

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 NameField TypeDescription
YEAR_IDIntegerYear Unique Identifier in the format YYYY
YEAR_NUMBERIntegerSame as YEAR_ID, in the format YYYY
IS_LEAPIntegerFlag that indicates if the year is leap, values are 1=true, 0=false



QUARTER

Field NameField TypeDescription
QUARTER_IDIntegerQuarter Unique Identifier in the format YYYYQQ
YEAR_IDIntegerForeign Key to the Year Table
QUARTER_NUMBERIntegerA number from 1 to 4 representing the Quarter within a year
QUARTER_PROGRESSIVEIntegerIt 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 NameField TypeDescription
MONTH_IDIntegerMonth Unique Identifier in the format YYYYMM
QUARTER_IDIntegerForeign Key to the Quarter Table
YEAR_IDIntegerForeign Key to the Year Table
MONTH_NUMBERIntegerA number from 1 to 12 rfepresenting the month within the year
MONTH_PROGRESSIVEIntegerUnique number that can be used to perform calculations between two periods, similar to the QUARTER_PROGRESSIVE
DAYS_IN_MONTHIntegerNumber 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.