Description:
Date_Generation transform is used for creating time dimension tables. This transform generates a column which holds the date values based on the start & end dates provided as an input to the transform by considering the increment provided to it.
Input/Output Data:
Before implementing Date_Generation transform, please find below the input & output values of Date_Generation transform.
I) Options for input data:
- Start date: Provide a start date value in the format yyyy.mm.dd. It also accepts variables, instead of passing static values one can pass values through variables.
- End date: Provide an end date value in the format yyyy.mm.dd. It also accepts variables, instead of passing static values one can pass values through variables.
- Increment: User can specify date intervals between start date and end date. Date can be incremented daily, weekly or monthly.
- Join rank: While constructing the joins, sources will be joined based on their ranks.
- Cache: The dataset will be cached in the memory to be used in later transform.
II) Output data from Date_Generation transform: The return type of this transform is a data set with a single column named DI_GENERATED_DATE containing the date sequence.
Example of Date_Generation Transform:
Follow below mentioned steps to try out the usage of Date_Generation Transform:
1) Create a job with name say TEST; then place a data Ffow on it, name it e.g. Test_DF.
2) Now go to the Local Object Library -> Transform -> Data Integrator -> Date_Generation Transform.
3) Drag and drop the Date_Generation transform on the dataflow, if you want rename it.Then place a query transform to extract the data of Date_Generation transform into a test table say T1_TEST as per below screen shot.
4) Double click on the Date_Generation transform and provide the input values to it, as per below screen shot.
Start date: 2011.01.01
End Date: 2012.03.31
Increment: Weekly, for join and Cache, leave the default setting.
5) Create 5 output columns (Date, Day, Week, Month, Year) as per below screen shot.
6) Now the design part is done, execute the TEST job, the values in the target table(T1_TEST) output will be as follows:
Where date is getting incremented weekly and the date ends on 2010.03.31.