Aggregator transformation
The best practices of using Aggregator transformation are –
Ø Always sort the inputs before passing it to the Aggregator, one way of sorting data could be applying an ORDER BY clause in the Source Qualifier on the GROUP BY columns of Aggregator.
Ø In Aggregator transformation the GROUP BY ports should be placed in the same order as required for the aggregation.
Ø While summarize an Amount field, the input port should always be defaulted to zero (set the default value as zero). A NULL value added with other values will result NULL.
Ø If Amount filed has a precision of 2 (2 decimal places), it should be defaulted as 0.00, defaulted as 0, will result an integer value truncating the decimal places.
Expression transformation
Ø To write an expression in an output/ variable port use functions and ports by pointing and clicking instead of typing the names of ports, functions or transformations to avoid typo mistakes.
Ø For all Output ports delete the Default Value “ERROR ('transformation error')”
Lookup transformation
Ø While calling an unconnected Lookup transformation in an expression instead of writing the expression point and click the lookup transformation from the function pane.
Ø The data type should match for ports in each side of lookup condition. To avoid any error in this, copy and paste the lookup ports to create the input ports.
Ø If there is a Lookup SQL override statement, server automatically puts the ORDER BY clause after the Lookup SQL override statement based on the order of the lookup ports. Hence putting any ORDER BY clause in Lookup SQL override creates an error. The work around is – Use “—“(comment-out sign) at the end of the Lookup SQL override statement and then put the specific ORDER BY clause.
Ø Only Required fields from Lookup to another transformation must be marked as Output port.
Ø Remove all unnecessary fields from lookup.
Ø All input ports must be prefixed with the keyword IN.
Ø Location Information of the Lookup Object must be $Target at mapping level.
Ø If there are multiple calls to only one lookup in the mapping, use unconnected lookup.
E.g. IN_Loan_Number
Source Qualifier transformation
Ø Try to filter as much records as possible at the Source Qualifier Level.
Ø All Sources under one folder must point to same database using the same connect string.
Ø Links further only those ports, which are required in the mapping.
Ø All ports names should be Initcap.
The best practices of using Aggregator transformation are –
Ø Always sort the inputs before passing it to the Aggregator, one way of sorting data could be applying an ORDER BY clause in the Source Qualifier on the GROUP BY columns of Aggregator.
Ø In Aggregator transformation the GROUP BY ports should be placed in the same order as required for the aggregation.
Ø While summarize an Amount field, the input port should always be defaulted to zero (set the default value as zero). A NULL value added with other values will result NULL.
Ø If Amount filed has a precision of 2 (2 decimal places), it should be defaulted as 0.00, defaulted as 0, will result an integer value truncating the decimal places.
Expression transformation
Ø To write an expression in an output/ variable port use functions and ports by pointing and clicking instead of typing the names of ports, functions or transformations to avoid typo mistakes.
Ø For all Output ports delete the Default Value “ERROR ('transformation error')”
Lookup transformation
Ø While calling an unconnected Lookup transformation in an expression instead of writing the expression point and click the lookup transformation from the function pane.
Ø The data type should match for ports in each side of lookup condition. To avoid any error in this, copy and paste the lookup ports to create the input ports.
Ø If there is a Lookup SQL override statement, server automatically puts the ORDER BY clause after the Lookup SQL override statement based on the order of the lookup ports. Hence putting any ORDER BY clause in Lookup SQL override creates an error. The work around is – Use “—“(comment-out sign) at the end of the Lookup SQL override statement and then put the specific ORDER BY clause.
Ø Only Required fields from Lookup to another transformation must be marked as Output port.
Ø Remove all unnecessary fields from lookup.
Ø All input ports must be prefixed with the keyword IN.
Ø Location Information of the Lookup Object must be $Target at mapping level.
Ø If there are multiple calls to only one lookup in the mapping, use unconnected lookup.
E.g. IN_Loan_Number
Source Qualifier transformation
Ø Try to filter as much records as possible at the Source Qualifier Level.
Ø All Sources under one folder must point to same database using the same connect string.
Ø Links further only those ports, which are required in the mapping.
Ø All ports names should be Initcap.
No comments:
Post a Comment