Effective Date Transformation in SAP BODS
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.
- Effective Date transformation defines validity of each and every record in a Time dependent table.
- 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.
- Default date (Ex: 9999.12.31) is assigned to the latest or active record for which validity cannot be defined.
- 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.)
- The input to the Effective Date transform must define at least one primary key.
- Sequence column must be defined as a primary key which helps from primary constraint error.
- ‘Default date’ is provided in the Effective Date transform. It can be changed to required value manually.
- 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.
Figure1 shows a sample input dataset which gives an Employee designation details with ‘Start date’ in ‘EFFDT’ field.
‘Effective-to’ value or ‘End date’ is determined using Effective date transform as shown in sample target in Figure2.
Effective-to value of nth record = Effective date of (n+1)th record.
- In figure 4, Target T1_Imperio_Data_Transfer gives the actual performance of Data Transfer Transform.
- In Figure 5, Schema out consist of an extra field EFFECTIVE_TO_COLUMN generated by Effective Date transform.
- Default effective to date value is 9999.12.31 assigned to latest or active records.
Figure 6 shows the mapping rules in ‘Query_1’ for Target ‘T2_Imperio_Eff_Date’
- Mapping rule for ‘Effective_To_Column’ field helps to get the Effective-to value = a day before the Effective Date value of next record (Note: In Target ‘T1_Imperio_Eff_Date’, Effective-to value of a record = Effective Date value of next record, Which is the actual logic inside the Effective data transform).
- Mapping rule for ‘No_Of_Days’ field helps to find the No. of days between the Start and End dates.
- Use the below file as source for working out on the transformation.
- Import the below .atl file in the Data Services Designer to find the Job for Effective Date transformation.