Creating a time dimension with Talend

Here's a short description how to create a time dimension with Talend. This job generates integers and calculates dates from it. It will give you the following fields:

  • id
    The primary key for the time dimension
  • CalendarDate
    The date
  • DayOfMonth
    The day of the month (1..31)
  • Month
    The month number (1..12)
  • Year
    The year in four digits
  • DayName
    The full name of the day
  • MonthName
    The full name of the month
  • WeekDay
    If the day is a working day e.g. mo-fri (1 or 0)
  • DayOfWeek
    The day in the week (1..7)
  • WeekNumber
    The number of the week in the year (1..53)
  • Quarter
    The number of the quarter (1..4)
  • QuarterName
    The name of the quarter

Job CreateTimeDimension

This is the global overview of the Job to create the time dimension.

On the left there's a simple row generator that generates integers from 0 to 20.000 (in this case). At the center there's a mapping component where all the taget fields are determined. On the right there's the target table.

Mapping of columns

Some detailed screenshots of the mapping.

Conversion class

A conversion class used in the mappings.

  1. public class Conversie {
  2.  
  3.     /**
  4.      * DateFromDays: Return calendar object with days from 1970-01-01
  5.      * 
  6.      * @param number of days
  7.      * @return the date from days from 1970-01-01
  8.      * 
  9.      * {talendTypes} Integer
  10.      * 
  11.      * {Category} User Defined
  12.      * 
  13.      * {param} integer(1) input: The number of days from 1970-01-01
  14.      * 
  15.      * {example} DateFromDays(1) # 1970-01-02
  16.      */    
  17.  
  18.     public static java.util.Date DateFromDays(Integer numDays) {
  19.     	 Calendar cal = Calendar.getInstance();
  20.     	 cal.set(1970, 0, 01,0,0,0);
  21.     	 cal.add(Calendar.DATE , numDays-1);
  22.     	 return cal.getTime();
  23.     }
  24.  
  25.     /**
  26.      * Formats a Date into a date/time string.
  27.      * 
  28.      * @param pattern the pattern to format.
  29.      * @param date the time value to be formatted into a time string.
  30.      * @return the formatted time as an integer.
  31.      * 
  32.      * {talendTypes} String
  33.      * 
  34.      * {Category} TalendDate
  35.      * 
  36.      * {param} string("yyyy-MM-dd HH:mm:ss") pattern : the pattern to format
  37.      * 
  38.      * {param} date(myDate) date : the time value to be formatted into a time string
  39.      * 
  40.      * {example} formatDate("yyyy-MM-dd", new Date()) #
  41.      */
  42.     public synchronized static Integer formatDateNum(String pattern, java.util.Date date) {
  43.     	SimpleDateFormat df = new SimpleDateFormat(pattern);
  44.     	StringBuilder sb = new StringBuilder( df.format( date ) );
  45.     	String s = sb.toString();
  46.         return Integer.parseInt(s);
  47.     }    
  48.  
  49. }
  50.