SAP BODS: Effective Date Transformation

Effective Date Transform is somehow like a previous row operation: It takes the date of the next row as end date of the current row. One thing to watch out in this transform is, it uses the primary key information to identify the groups. In all the other transforms, there was an extra column list, here it is the primary key.

v Effective Date transformation is a readymade logic that helps to define Time Dependent Dimensions. Time dependent dimensions are the attributes which have a validity that expires.

v Effective Date transformation defines validity of each and every record in a Time dependent table.

v It calculates and provides ‘Effective-to’ (End date) value for an ‘Effective date’ (Start date) in the input dataset. Logic behind the ‘Effective-to’ value depends on the ‘Sequence column’ provided.

v Default date (Ex: 9999.12.31) is assigned to the latest or active record for which validity cannot be defined.

Prerequisite:

v Input dataset must contain Effective Date field.

(Note: If field name is given as ‘EFFDT’, Data services automatically select it as Effective Date column else, we have to select the field manually.)

v The input to the Effective Date transform must define at least one primary key.

v Sequence column must be defined as a primary key which helps from primary constraint error.

NOTE:

v ‘Default date’ is provided in the Effective Date transform. It can be changed to required value manually.

v If Sequence column is not defined, The Effective-to value of a record will be equal to Effective Date value of next record regardless the logic.

EXAMPLE:

Let us take the below table as a source. It contains Effective_date column named as ‘EFFDT’.

Step 1: Create a new Batch job in a Project. Here I name it as Effectivedate_transform_Job

Step 2: Drag and Drop Workflow and name it as WF_Effective_date_Transform, inside a Workflow drag and drop the Dataflow and name it as DF_Effective_date.

Step 3: Drag and Drop the Source table. Here I use EMP_DETAILS_EFFDT table.

Step 4: Drag and Drop the Query Transform and make join between the Source Table and Query Transform.

Step 5: Open a Query Transform and Map the required Columns to a Output. In a source table I’m not set any primary key. So now I'm going to set primary key for two columns named as “EMP_Id” and “DESIGNATION” by right click the column and click Primary Key.

Step 6: Drag and drop the Effective Date Transforms from the Transforms tab into the workspace area and join a Query Transform with this Effective Date Transform.

Step 7: Then open an Effective_Date Transform and provide the required details as shown as below:

Note:

1. As I mentioned above, the Effective date column picks a EFFDT column automatically. If your column name is not EFFDT, you have assign it by manually.

2. To assign column for the Effective sequence column that should be a primary key column.

3. Effective to column is generated automatically

4. Default effective to date value: 9000.12.31 is default value. We can give any value by manually but the format should be correct or otherwise we can pass a variable or parameter with prefix ‘$’ symbol.

Step 8: Now Drag and drop the Template for storing output.

Step 9: And make join between Effective_Date Transform and Template.
Finally, it looks like as shown as below:

Step 10: Now, Validate and Execute the Job

RESULT:

Additionally:

If we want to know how many days that person working in a specific designation, It can be done by using built-in date function JULIAN( ). Lets see in detail:

Step 1: Drag and Drop another Query Transform. Here it named as Query 1 and map the output columns.

Step 2: And add another column by right click in output schema and click New Output Column.