Working with Transformations – My Notes

— Transformation Overview — 

Which is the repository object that generates, modifies, or passes data?
Ans:- A Transformation.

Which transformation performs calculations on groups of data?
Ans:- Aggregator Transformation.

What are the actions performed by Active Transformations?
Ans:- Change the number of rows that pass through the transformation, Change the transaction boundary, and Change the row type.

Can we connect multiple active transformations to the same downstream transformation in a mapping?
Ans:- No, Designer does not allow you to connect multiple active transformations because the Integration Service may not be able to concatenate the rows passed by active transformations.

Can we connect an active and a passive transformation to the same downstream transformation in a mapping?
Ans:- No, Designer does not allow you to connect multiple active transformations because the Integration Service may not be able to concatenate the rows passed by active transformations.

Can we connect a Sequence Generator transformation and an active transformation to the same downstream transformation?
Ans:- Yes, Sequence Generator transformation is an exception to the rule. A Sequence Generator transformation does not receive data. It generates unique numeric values. As a result, the Integration Service does not encounter problems concatenating rows passed by a Sequence Generator transformation and an active transformation.

What is Passive Transformation?
Ans:- A passive transformation does not change the number of rows that pass through the transformation, maintains the transaction boundary, and maintains the row type.

What is connected transformation?
Ans:- A connected transformation can be connected to the data flow using input/output ports to any other transformation or source/target.

What is unconnected transformation?
Ans:- An unconnected transformation is not connected to other transformations in the mapping. An unconnected transformation is called within another transformation, and returns a value to that transformation.

What are the different ways to create a transformation?
Ans:- Create it in the Mapping Designer as part of a mapping, in the Mapplet Designer as part of a mapplet, or in the Transformation Developer as a reusable transformation.

The transformations created in mapping designer is re-usable?
Ans:- Yes, We can re-use it ONLY IF you configure them to be reusable.

Can we rename a transformation?
Ans:- Yes, To rename transformations, click the Rename button and enter a descriptive name for the transformation, and click OK.

— Transformation Ports — 

What are the different types of ports in designer?
Ans:- Input, Input/output, Variable & Output ports.

Which port is Receive data and pass it unchanged?
Ans:- Input/output ports.

Set of ports that define a row of incoming or outgoing data is called?
Ans:- A Group.

What is Multi-Group Transformations?
Ans:- A transformation which have multiple input and output GROUPS are called Multi-Group Transformations.

All multi-group transformations are active?
Ans:- Yes, because they might change the number of rows that pass through the transformation.

What is called a blocking transformation?
Ans:- A blocking transformation is a multiple input group transformation that blocks incoming data. Joiner transformation configured for unsorted input is a blocking transformation.

— Expressions — 

What are the transformations that supports expressions?
Ans:- Aggregator, Expression, Filter, Router, Rank, Update Strategy, Transaction Control, Data Masking.

How to add comments within the expression?
Ans:- use — or // comment indicators.

Can we add an Expression to an Input port?
Ans:- Yes, Only in Data Masking transformation, all other transformations, add the expression to an output port.

Can we define the expression string in a parameter file ?
Ans:- Yes, If you have an expression that changes frequently you can define the expression string in a parameter file so that you do not have to update the mappings that use the expression when the expression changes.

When the Integration Service expands the mapping parameters and variables in an expression ?
Ans:- The parameter or variable you create must have IsExprVar set to true. When IsExprVar is true, the Integration Service expands the parameter or variable before it parses the expression. If False, after it parses the expression.

— Local Variables  — 

What is local Variables?
Ans:- Use local variables (Variable ports) in Aggregator, Expression, and Rank transformations to improve performance. You can reference variables in an expression or use them to temporarily store data.

What is the port order in which the Integration Service evaluates variable ports?
Ans:- The Integration Service evaluates ports by dependency. It evaluates the input ports first, the variable ports next, and the output ports last.

What is the guidelines to set initial values for variables?
Ans:- Zero for numeric ports, Empty strings for string ports, 01/01/0001 for Date/Time ports.

— Default Values  — 

What is the system default value for null input ports ?
Ans:- NULL.

What is the system default value for output transformation errors?
Ans:- ERROR. The default value appears in the transformation as ERROR(‘transformation error’).

What will happen if a transformation error occurs?
Ans:- The Integration Service skips the row and write the input rows to log file.

What is the default values for Variable ports ?
Ans:- Variable ports do not support default values. The Integration Service initializes variable ports according to the datatype.

Can we override the system default values with user-defined default values for unconnected transformations?
Ans:- No, The Integration Service ignores any user-defined default value and it applies the system default value.

Can we override the system default values with user-defined default values for a connected transformations?
Ans:- Yes, We can override the system default values with user-defined default values for input, pass- through, and output ports within a connected transformation.

What are the options to enter user-defined default values?
Ans:- Use any constant (numeric or text), including NULL, Constant expression, ERROR (Generate a transformation error), ABORT (Abort the session).

Can we call a stored procedure or lookup table from a default value expression?
Ans:- No.We cannot.

Can we use values from ports within the expression to assign default values?
Ans:- No, We cannot use values from ports within the expression because the Integration Service assigns default values for the entire mapping when it initializes the session.

What is ERROR and ABORT Functions?
Ans:- The Integration Service skips the row when it encounters the ERROR function and write to session log (does not write rows to the reject file). It aborts the session when it encounters the ABORT function. Both can be used for input and output port default values, and input values for input/ output ports.

What would be the result of the expression ERROR(‘Error. DEPT is NULL’)?
Ans:- The Integration Service skips the row when the input value of DEPT_NAME is NULL and write to a log file, t does not write these rows to the reject file.

— Tracing Level — 

What are the tracing Level in Transformations?
Ans:- Normal, Terse, Verbose Initialization, Verbose Data.

What is the default tracing level for every transformation?
Ans:- Normal.

Can we override the tracing levels for individual transformations?
Ans:- Yes, In the session we can override the tracing levels for individual transformations with a single tracing level for all transformations.

Which would be the best tracing level to boost the performance?
Ans:- Terse, writing the minimum of detail to the session log when running a workflow containing the transformation.

When we can use the Verbose tracing level?
Ans:- Use Verbose setting only when you need to debug a transformation that is not behaving as expected.

What is Verbose Initialization & Verbose Data?
Ans:- Verbose Initialization logs additional initialization details, names of index and data files used, and detailed transformation statistics. Verbose Data, In addition to verbose initialization tracing, it writes row data for all rows in a block when it processes a transformation.

— Reusable Transformation — 

How to create a reusable transformation?
Ans:- (1) Design it in the Transformation Developer or (2) Promote an existing non-reusable transformation from the Mapping Designer By checking the Make Reusable option in the Edit Transformations dialog box. If you promote a transformation to reusable status, you cannot demote it. However, you can create a non- reusable instance of it.

Can we use Non-reusable transformations in multiple mappings?
Ans:- No, Non-reusable transformations exist within a single mapping.

Can we create all transformations as a non-reusable or reusable?
Ans:- Yes except External Procedure transformation, which can create only as a reusable transformation.

How to configure a reusable transformation in a mapping?
Ans:- Just add an instance of the reusable transformation to the mapping, The definition of the transformation still exists outside the mapping. The designer stores each reusable transformation as metadata in one of the folder in the Navigator

Do we need to update the same transformation in every mapping that uses it, if there is any changes in the definition of a reusable transformation?
Ans:- No, Since the instance of a reusable transformation is a pointer to that transformation, when you change the transformation in the Transformation Developer, all instances of the transformation inherit the changes. Instances do not inherit changes to property settings, only modifications to ports, expressions, and the name of the transformation.

Sequence Generator transformations can be Non-reusable in mapplets?
Ans:- No, Sequence Generator transformations must be reusable in mapplets. You cannot demote reusable Sequence Generator transformations to non-reusable in a mapplet.

Can we create a non-reusable instance of a reusable transformation?
Ans:- Yes, We can create a non-reusable instance of a reusable transformation within a mapping. Reusable transformations must be made non-reusable within the same folder.

How to see what mappings, mapplets, or shortcuts may be affected by changes you make to a transformation?
Ans:- select the transformation in the workspace or Navigator, right-click, and select View Dependencies.

What are the changes to the reusable transformation can cause the mappings to be invalidated?
Ans:- When you delete a port or multiple ports, change a port datatype, change a port name, or an invalid expression.