0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

Date Generation Transformation:

Date Generation Transformation  in BODSSummary: 

Date Generation Transformation is used to produces a series of dates incremented that you specify in a separate column. 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.

Prerequisite: Before implementing Date_Generation transform, please find below the input & output values of Date_Generation transform.

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.

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 Scenario:-

In this scenario source includes item data (daily basis) regarding Date, No.of Items and Plant in table. In target we need to get weekly or monthly data based on date. We can get this by using Date Generation transformation.

  • Figure1 shows Sample Item data in data base table.
Date No.of items Plant
01.02.2012

10

P1

03.02.2012

4

P1

04.02.2012

26

P1

21.02.2012

16

P1

Figure 1: Initial item Data in Database Table 

  • Figure2 shows Sample target data in table\file. 
Date No.of items Plant
01.02.2012

0

P1

08.02.2012

40

P1

16.02.2012

0

P1

24.02.2012

16

P1

Figure 2: Sample Target Data

  • This Figure3 shows project area, we have to create project, Job, work flow and data flow.
Date Generation Transform in BODS
Figure 3: Job Hierarchy
  • Below figure4 shows ETL flow of our Job
Date Generation Transformation  in SAP BODS
Figure 4: Job ETL Flow
  • Drag DateGeneration,Query transformations in to workarea connect all objects same as figure4.

Note: first we have to load data from file (using Imperio_DateGeneration file)to table(TB_Plant_DATE).

  • Pass Variables “$StartDate” “$EndDate” and “$Increment” into Start date, End date and Increment Fields as of Figure5.
Date Generation Transformation  in SAP BODS 1
Figure 5: under Date Generation transform
  • In this Query Transformation we drag DI_Generate_date from schema in into schema out in figure6.
Date Generation Transformation in SAP BODS
Figure 6: under Query transform
  • Apply Previous_ row _value function for Previous Row Value Column to find the item numbers for weekly or monthly basis this column is used as start date of week or month of our data which is in figure 7.
Date Generation Transform in BODS 1
Figure 7:Previous_ row _value function for Previous Row Value Column (new output column)
  • In second Query Transformation we drag DI_Generate_date from schema in into schema out in figure8.
Date Generation Transform in SAP BODS
Figure 8 : under Second Query transform
  • Shown figure9 Apply Custom function(cf_date) and nvl() function for Num of Items Colum to get summarized item’s in range of the date between start date and end at weekly or monthly basis,nvl() is used to Populate ‘0’ in place of null value and add new output column for plant to populate constant value’P100’.
Date Generation Transform in BODS 2
Figure 9: nvl() function for Num of Items Column
  • Shown figure10 custom function(cf_date) here write SQL satements to get summarized item’s.
Date Generation Transform in SAP BODS 1
Figure 10 : Custom Function for Num of Items Column

Source Data:

  • Source data in TB_Plant_Date table in figure11.
Date Generation Transform in BODS 3
Figure 11: Initial data in TB_Plant_Date table

Target Data:

Populate Values to Increment,StartDate,EndDate Variables at job Execution time shown in figure12.

Date Generation Transform in BODS 4
Figure 12: Global Variable screen at job execution properties
  • Required Weekly Summarized items data in Target table shown in Figure13.
Date Generation Transform in BODS 5
Figure 13: Target data

Attachment:

  • Use the below file as source for working out on the transformation.

Click Here to Download Source Data.

  • Import the below .atl file in the Data Services Designer to find the Job for the above transformation.

Click Here to Download ATL File.

That’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×