A site about Talend
This reusable and configurable Job, generates the data that is required to populate a Data Warehouse Date Dimension. This is often referred to as a Time Dimension; however, in this series of articles, we will be creating Dimensions for both Date and Time.
You may download LibMakeDimDate and import (Job Designs->Import items) in to a Talend Project of your choice. This download is supported by Talend 5.2.2 and above.
This Job may be added to any other Job (the parent Job), and provides the input for creating a Date Dimension. You may use the output as-is, drop the columns that are not of interest, modify existing values or add additional ones - without the need to modify the library Job itself.
The output from this Job contains many attributes. It does not include attributes for Financial Periods or Public Holidays. These additional attributes will be added in a later version; however, you may easily add these for yourself.
The following parameters (Context Variables) may be overridden, by passing them from a parent Job.
|epoch||01-Jan-1970||The first date to be created|
|daysPastToday||365||The last day created will be today+daysPastToday. This may be a negative number.|
|quarters||"Quarter 1;Quarter 2;Quarter 3;Quarter 4"||A semi-colon (;) delimited list of descriptions.|
|months||"January;February;March;April;May;June;July;August;September;October;November;December"||A semi-colon (;) delimited list of descriptions.|
|shortMonths||"Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec"||A semi-colon (;) delimited list of descriptions.|
|days||"Sunday;Monday;Tuesday;Wednesday;Thursday;Friday;Saturday"||A semi-colon (;) delimited list of descriptions.|
|shortDays||"Sun;Mon;Tue;Wed;Thu;Fri;Sat"||A semi-colon (;) delimited list of descriptions.|
The table shown, below, demonstrates the data generated by this Job. You may drop columns that are not required (from within your own Job), modify values, and add your own. You may modify the textual values by passing your own, as detailed above.
The variable naming conventions break away from the Talend (Java) convention as it is expected that this data will be loaded in to a database and it is, therefore, more helpful to use a naming convention that is more in keeping with what you would expect to see in a database.
DATE_ID is the Primary Key for this Dimension and, as can be seen from the sample data shown below, it has been derived from the Date. Some may argue against this and say that it should simply be an arbitrary number. I would, generally, agree with this; however, I have chosen to do this so that the key may be calculated by data loaders, rather than requiring a look-up. It is also the intention, that this Dimension may be refreshed on a daily basis and that it may contain attributes that are relative to the current day. To achieve this, it is helpful that there is a guarantee that these keys do not mutate.
|QUARTER||Quarter 4||Descriptions may be passed as delimited (;) list.|
|MONTH||November||Descriptions may be passed as delimited (;) list.|
|MONTH_SHORT||Nov||Descriptions may be passed as delimited (;) list.|
|DAY_OF_WEEK||Tuesday||Descriptions may be passed as delimited (;) list.|
|DAY_OF_WEEK_SHORT||Tue||Descriptions may be passed as delimited (;) list.|
|IS_THIS_YEAR||true||Requires daily refresh.|
|IS_LAST_YEAR||false||Requires daily refresh.|
|IS_NEXT_YEAR||false||Requires daily refresh.|
|IS_THIS_QUARTER||true||Requires daily refresh.|
|IS_LAST_QUARTER||false||Requires daily refresh.|
|IS_NEXT_QUARTER||false||Requires daily refresh.|
|IS_THIS_MONTH||true||Requires daily refresh.|
|IS_LAST_MONTH||false||Requires daily refresh.|
|IS_NEXT_MONTH||false||Requires daily refresh.|
|IS_THIS_WEEK||true||Requires daily refresh.|
|IS_LAST_WEEK||false||Requires daily refresh.|
|IS_NEXT_WEEK||false||Requires daily refresh.|
|IS_TODAY||true||Requires daily refresh.|
|IS_YESTERDAY||false||Requires daily refresh.|
|IS_TOMORROW||false||Requires daily refresh.|
This Job is added to a parent Job using the tRunJob Component. Once added, you should hit the button Copy Child Job Schema and then connect this component to another component of your choice. This may, for example, be a t[DB]Output component.comments powered by Disqus