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:
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.
Step 3: And Provide Column Name here, I named it as No_of_days and its Datatype
and click ok.
Step 4: For this column, we
are going to calculate No_Of_Days between
the EFFDT and Effective
to Column dates by using Julian Function.
Code:
julian(EFFECTIVE_TO_COLUMN)
- julian( Effective_Date.EFFDT )
Note:
JULIAN: Returns the integer Julian
date for the input date.
Syntax: julian( [in] InputDate As datetime ) As int
Step 5: Finally Drag and drop another Template
and join it with the Query Transform.
Step 6: At last Validate and Execute the Job.
Result:
Thanks For Visiting My Blog...
Share your comments...
Leave a Comment