Skip to Content

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.

Date Generation Transform

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.

Date Generation Input values

5) Create 5 output columns (Date, Day, Week, Month, Year) as per below screen shot.

Date Generation table values

6) Now the design part is done, execute the TEST job, the values in the target table(T1_TEST) output will be as follows:

Date Generation output

 Where date is getting incremented weekly and the date ends on 2010.03.31.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Former Member

    I am new to Data Services. This article helps me to understand the Date generation transform.

    In real time scenarios where it is exactly being used

    Thank you.

    (0) 

Leave a Reply