Implementing effective date transformation in BODS

Effective date:

Effective Date transformation is a ready made 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 data set. Logic behind the ‘Effective-to’ value depends on the ‘Sequence column’ provided.
• Default date is assigned to the latest or active record for which validity cannot be defined.
• Input data set must contain Effective Date field.
• 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.

Steps :-

• Right click on Project -> New -> Data Flow

• Enter Data flow name and Description.
• Click on Finish.

• Drag and drop Row Generation to the Data flow editor.

• Provide Row number starts from and Row count and Join rank.

• Drag and drop Row generation to  Data Flow editor.

• Provide Start date and End date.

• Drag and Drop Query Transform to data flow editor.

• Join Row Generation to Query transform .

• Drag and drop Row generation Input schema Row id to Query output schema.

• Join Date generation and Query transform.

• Drag and drop Date generation input DI_GENERATED_DATE to Query output schema.

• Click on new field to add fields.

• Add fields CUSTOMER_ID, START_DATE and END_DATE to Query Transform.

• Drag and drop Effective date to Data Flow editor.

• Join Query Transform to Effective Date.

• Select Effective date.
• Click on Properties.
• Provide Effective date column , Effective sequence column and effective to column and default effective to date value.

• Drag and drop Template Table.

• Click on Data store value search help.
• Select the Target Data store.

• Provide Table owner and Table name.

• Join Effective Date to Template table.

• Click on save.

• Click on validate.

• Pop-up will appear " Validation results is successful ".

• Click on Execute.

• Select Job Server and click Finish.

• Job execution is success.

• Go to SAP-HANA Studio.
• Expand system -> Expand Catalog .

• Expand Tables -> Click on Template table.

• Right click on Template table -> click on open data preview.