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

Summary:

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.

Prerequisite:

  • 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.

NOTE:

  • ‘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.

Example Scenario:

Figure1 shows a sample input dataset which gives an Employee designation details with ‘Start date’ in ‘EFFDT’ field.

Effective Date Transform in BODS
Figure 1: Sample Source Data

‘Effective-to’ value or ‘End date’ is determined using Effective date transform as shown in sample target in Figure2.

Effective Date Transform in SAP BODS
Figure 2: Sample Target DataThe logic behind calculating Effective-to value is:

Effective-to value of nth record = Effective date of (n+1)th record.

Effective Date Transform in SAP BODS 1
Figure 3: Effective Date Transformation Object Hierarchy
Effective Date Transform in SAP BODS 2
Figure 4: Effective Date Transformation job ETL flow
  • In figure 4, Target T1_Imperio_Data_Transfer gives the actual performance of Data Transfer Transform.
Effective Date Transform in BODS 1
Figure 5: Effective Date Transformation Defining rules
  • 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.
Effective Date Transformation in SAP BODS
Figure 6: Query Transformation Mapping rules

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.

Attachments:

Source File: 

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

Click Here to Download Source Data.

.atl File:

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

Click Here to Download ATL Files.

That’s it.

Enjoy 🙂

 

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