Transforming data with mapping specifications

Transformation operations change one set of data values into other values when the mapping specifications are generated and run as jobs.

Transform operators are objects in DataStage.

Data transformation is a process by which you select source data through some SQL or application method, convert that data, and map the data to the format that is required by target systems. As a data analyst or programmer, you manipulate data to bring it into compliance with business, domain, and integrity rules as well as with other data within the target environment.

A transformation rule represents instructions to the developer who completes the job that you define in the mapping specification. The transformation rules describe the current state of the information and what needs to be done to it to produce a particular result. A business analyst might add the business rules and collaborate with the developers to turn the business rules into a job.

You can transform data types into your business standards. You can apply consistent representations to data, correct misspellings, and incorporate business or industry standards. You can select from transformation rules that you imported to apply the appropriate standardization for the data set. You can apply transformation rules to transform the source data and produce a result that the end business application might need.

The result of a transformation must be a value whose type fits the type of the target object. A transformation can include the following items:

Converting from one data type to another, resolving inconsistencies that might exist between source and target data.
Converting currencies for monetary calculations.
Reducing redundant or duplicated data.

The transformation operations modify the source values before the values are applied to the data source and target. A transformation can be in the form of functions, join operations, lookup statements, expressions, or annotated business rules. Transformations can be on a single column or on multiple columns.

Transformation example

Example of a transformation on a single column: In a single-column transformation, you might have a source column that contains salary in hundreds of dollars. A matching column on the target system contains a column that is similar, but its values are in thousands of dollars. You transform the type (in this case, the numeric base) of the source column to the type of the target column.
Example of a transformation on multiple columns: A multiple-column transformation is a many-to-one relationship. For example, you might have a first name column and a last name column on the source system. On the target system is a single name column. The transformation concatenates the first name and last name fields in the source to a single name field in the target. The columns that are part of the transformation must be used as a source in your mapping specification.

The following example is of a simple transformation that calculates a different target price for customers who are classified as GOOD:

SOURCE_SYSTEM.PRODUCT.PRICE - SOURCE_SYSTEM.PRODUCT.DISCOUNT = 
      TARGET_SYSTEM.GOOD_CUSTOMER_PRICE

In this example, the PRODUCT.PRICE is reduced by the PRODUCT.DISCOUNT, where the PRODUCT.PRICE is the price of the product on the source.