Managing Dates with Data Integrator Transforms

Objective

After completing this lesson, you will be able to manage dates with integrator transforms.

Loading Time Dimensions

SAP Data Integrator transforms help you load time dimension tables using the Date Generation transform.

This transform generates the primary key values for a time dimension target.

After generating the dates, you can use functions in a Query transform to populate additional fields, such as day_of_week.

Data flow diagram showing a Date_Generation transform connected to a Query transform and a Template_Table target.

Configuring the Date Generation Transform

When configuring the Date Generation transform, specify the following options:

Start date: The first date in the sequence (for example, 1997.01.01 or a variable).
End date: The last date in the sequence (for example, 2000.12.31 or a variable).
Increment: The time interval between generated records, such as Daily (a variable can also be used).

The image depicts a software interface for date generation, showing a schema output section with a field named DI_GENERATED_DATE described as an automatically generated date, and a configuration panel below where users can set the start date, end date, increment type, and join rank, with both start and end dates set to November 30, 2006, the increment set to Daily, and the join rank set to 0, along with a Cache checkbox.

Defining the Query Transform

In the Query transform, you can create target columns and map them to specific time dimension values using various functions. Common examples include:

The julian function: Creates a unique date ID based on the number of days since the start of the Julian calendar.
The month function: Extracts the month number (1–12) from the date.
The quarter function: Extracts the quarter number (1–4) from the date.
The to_char function: Converts a specific part of the date into a string format.
The day_in_week function: Extracts the day of the week as a number (1–7).
The isweekend function: Returns a boolean value (1 or 0) to indicate if the date falls on a weekend.

Loading Time Dimensions using the Date Generation Transform

Follow this simulation to learn how to create and provision a time dimension.

ExerciseStart Exercise

Maintaining Effective Dates

In a slowly changing dimension, you often have valid-from and valid-to columns to support queries based on effective dates.

To maintain these columns, the valid-to date must be updated each time a new value is loaded for the same dimension key.

If your source data only contains changes with their valid-from dates, the Effective_Date transform can calculate the valid-to date automatically.

Example of source data showing changes with valid-from dates and calculated valid-to dates.

Caution

The Effective_Date transform only calculates effective-to dates for the rows flowing through it. Updating existing rows in the target table — for example, closing out the previous record's valid-to date — requires a separate step, such as a History_Preserving transform.

The Effective_Date transform editor has four properties to configure.

Effective date column
The input column containing the date from which a row's data is valid. If the column is named EFFDT, SAP Data Services selects it automatically.
Effective sequence column
Used only when duplicate effective dates can occur for the same key. It defines the processing order of those rows; only the row with the highest sequence number is kept.
Effective to column
The output column added by the transform to store the calculated valid-to date.
Default effective to date value
The fallback date assigned when no later row exists for the same key (typically a far-future sentinel such as 9999.12.31). As a best practice, you can use a variable here for easier maintenance.

The image depicts a software interface titled Effective_Date - Transform Editor, showing a data transformation setup where fields from the EMPLOYEECHANGES schema are mapped to the Effective_Date schema. Key fields such as PERSONNEL_NUMBER, BEGINDATE, CHANGE_ID, EMPLOYEE_STATUS, ORIG_HIRE_DATE, TERMINATION_DATE, EVENT_TYPE, and REASON_CODE are displayed in both schemas. The lower section of the interface allows the user to configure the effective date column, sequence column, and effective to column, with dropdown menus for selection and a default effective to date value set to 2006.11.30. The interface is designed for managing and transforming employee change data, focusing on effective date configurations.

The input dataset must define at least one primary key, and can only contain rows flagged as NORMAL.

Summary

In this lesson, you learned how to manage dates in time dimensions and slowly changing dimensions.

Combine the Date Generation and Query transforms to create and populate time dimension tables with calculated attributes.
Use the Date Generation transform to create primary key values for time dimension tables by defining the start date, end date, and increment interval.
Use the Query transform to populate time dimension fields using functions such as julian, month, quarter, to_char, day_in_week, and isweekend.
Use the Effective Date transform to automatically calculate valid-to dates for slowly changing dimensions based on effective date and sequence columns.

Next lesson