TRANSFORMATION 2

1. Variable Initialization
   

 

Numeric ports : 0

String ports : Empty string

Date/time ports: 01/01/1753

 

1. System Default values for ports
   

 

All transformations use default values that determine how the IS handles input null values and output transformation errors.

 

Input, Output, Input/Output ports are created with a system default value that we can override with a user-defined default value.

 

Input port: The system default value for input port is NULL.

 

Output port: The system default value for output transformation errors is ERROR, It appears as ERROR('Transformation error). If a transformation error occurs, the IS skips the row. The IS notes all input rows skipped by the ERROR function in the session log file.

 

Variable ports do not support default values. The IS initializes variable ports according to the data type.

 

1. User defined default values
   

 

We can override the system default values with user-defined default values.

 

Input ports: We can create user-defined default values for input ports if we do not want the IS to treat null values as NULL.

 

Output ports: We can enter user-defined default values for output ports if we do not want the IS to skip the row or if we want the IS to write a specific message with the skipped row to the session log file.

 

Note:

 

The IS ignores user-defined default values for unconnected transformations. For example, if we call a lookup or stored procedure transformation through an expression, the IS ignores any user-defined default values and uses the system default value only.

 

à SQ, Rank, Transaction control transformations does not support input default value for input and input/output ports.

 

à Aggregator, Filter, Router, SQL, SQ(n/a), Transaction Control (n/a) , US (n/a) does not support output default value for output ports.

 

à We can enter any constant expression as a default value. A constant expression is any expression that uses transformation functions (except aggregate functions) to write constant expressions. We can not use values from input or output ports.

 

à We can use ERROR or ABORT functions for input and output port default values, and input values for input/output ports. The IS skips the row when it encounters the ERROR function. It aborts the session when it encounters the ABORT function.

 

1. Transformation Error
   

 

When a transformation error occurs and we didn't override the default value, the IS performs…

à Increases the transformation error count by 1

à Skips the row and writes the error and input row to the session log file or row error log. The IS does not write the row to the reject file.

 

Note: Reusable transformation instances do not inherit changes to property settings, only modifications to ports, expressions, and the name of the transformation.

 

1. Aggregator Transformation
   

   
    

   Aggregator transformation performs aggregate calculations such as averages and sums.
   

   
    

   We can use conditional clauses to filter rows in aggregate transformations , providing more flexibility than SQL language.
   

   
    

   Eg: SUM(COMM, COMM>0)
   

   
    

   We can also use non-aggregate functions in the aggregate expression.
   

   
    

   IIF(MAX(QTY)>0,MAX(QTY),0)
   

   
    

   We can include one aggregate function nested within another aggregate function.
   

   
    

   Eg: MAX(COUNT(EMPNO))
   

   
    

   
    

   Incremental aggregation:
   

   
    

   When the IS performs incremental aggregation, it passes new source data through the mapping and uses historical cache data to perform new aggregation calculation incrementally.
   

   
    

   Aggregate cache:
   

   
    

   The IS stores data in the cache until it completes aggregate calculations. It stores group values in an index cache and row data in the data cache.
   

   Sorted Input:
   

   
    

   We can improve aggregator transformation performance by using sorted input option. When we use sorted input , the IS assumes all data is sorted by group and it performs aggregate calculations as it reads for a group.
   

   
    

   If we use sorted input and do not sort data correctly, the session fails.
   

   
    

   Do not use sorted input
   

   
    

   à if the aggregate expression uses nested aggregate functions.
   

   
    

   à The session uses incremental aggregation.
   

   
    

   à Source data is data driven.
   

   
    

   When any of these conditions are true, the IS processes the transformation as if you do not use sorted input.
   

   
    

   
    

   Data must be sorted by the aggregator group by ports , in the order they appear in the aggregator.