Generating Date Dimension


Create a new job called date_dim. First we will define a variable for start date, because we will use this job in various projects and we might require a different start date each time:

Click on the Context tab and then on the + button to add a new context variable. Give it the name myStartDate of type Date and define a value for it.



Next add a tRowGenerator component to the design area and double click on it to activate the settings dialog. The idea is to create X amount of rows: The first row will hold our start date and each subsequent row will increment the date by one day.


1.       Click the + button to add a new column. Name it date and set the type to Date.
2.       Click in the Environment variables cell on the right hand side and then you will see the parameters displayed in the Function parameters tab on the bottom left hand side.
3.       Define the number of rows that should be generated in Number of Rows for RowGenerator.
4.       In the Function parameters tab set the date parameter value to context.myStartDate. This will ensure that the context variable which we defined earlier will be used.
5.       Set the nb parameter to Numeric. sequence(“s1”, 1, 1) - 1. Use the expression builder for a more convenient setup. This will create a sequence which we will use to add days to our start date. The reason why we subtract 1 at the end is because we want to keep our start date.
6.       Set the dateType parameter value to “dd”. This ensures that days will be added to our date.
7.       Click on the Preview tab and check if the result set looks as expected.
8.       Click Ok to close the component settings.

A.       Now add a tMap component and create a row from the tRowGenerator to the tMap component. Double click the tMap component:

  1. Click the + button on the right hand side to create a new output table.
  2. For year column extract the year using TalendDate.formatDate("yyyy",row1.date)
  3. For Quater details: (Integer.parseInt(TalendDate.formatDate("M",row1.date))+3-1) /  3   
  4. For Month number : TalendDate.formatDate("MM",row1.date)
  5. For Month name : TalendDate.formatDate("MMM",row1.date)
  6. For Weekday number (1-5) : row1.date.getDay()
  7.  For day part of the date (1-31) : row1.date.getDate()
  8. For week of the year(1-52): TalendDate.formatDate("ww",row1.date)
  9. For day of the year(1-366) : TalendDate.formatDate("DDD",row1.date)
  10. Click Ok.
  11. Add a database output component of your choice (in my case I used one for PostgreSQL) and create a row from the tMap to the database output component. Double click the database output component and provide all the necessary settings. That’s it: Now you can run the job and examine the data in your table.

Comments

Post a Comment

Popular posts from this blog

Increase Java Memory For Pentaho Data Integration

Pentaho runs inside a Java virtual machine, and hence is bound by the properties of that VM. These optimisations can apply to just about any Java application, including the Pentaho BI Server and GUI tools. Method to increase memory allocation: Open the file spoon.sh or spoon.bat in a text editor Look for a section that looks like this: # ****************************************************************** # ** Set java runtime options                                     ** # ** Change 256m to higher values in case you run out of memory.  ** # ****************************************************************** OPT="-Xmx256m -cp $CLASSPATH -Djava.library.path=$LIBPATH -DKETTLE_HOME=$KETTLE_HOME -DKETTLE_REPOSITORY=$KETTLE_REPOSITORY -DKETTLE_USER=$KETTLE_USER -DKETTLE_PASSWORD=$KETTLE_PASSWORD -DKETTLE_PLUGIN_PACKAGES=$KETTLE_PLUGIN_PACKAGES" Change the -Xmx parameter to alter the max...
Read more

Null pointer exception in tmap

You might have faced a common error in Talend i.e. the  NullPointerException in tMap "Exception in component tMap_1 java.lang.NullPointerException" Solutions: 1) Make sure your columns are nullable when you define the schema. 2) The NullPointerException indicate there are some null value on lookup table, so you need to deal with the null value on corresponding columns on expression filed of tmap, for example: !Relational.ISNULL(row13.abc)? row13.xyz:row13.sss:null
Read more

Different Match Models in tMap with example

Unique Match:     (Also called Last match) If several matches are found in the Inner Join, i.e. the last row matching the explicit join as well as the filter will be added to the output flow. First Match: If several matches are found in the Inner Join, i.e. the first row matching the explicit join as well as the filter will be added to the output flow. All Match: If several matches are found in the Inner Join, i.e. rows matching the explicit join as well as the filter, all of them will be added to the output flow Example: deptid   sales 1           1000 2           2000 3           3000 2           5000 3           6000 Outputs: 1. Unique match deptid   sales 1           1000 2           5000 3           6000 2. All matches deptid  ...
Read more