EXPRESSION TRANSFORMATION

EXPRESSION TRANSFORMATION-

• Passive and connected transformation.

Use the Expression transformation to calculate values in a single row before we write to the target. For example, we might need to adjust employee salaries, concatenate first and last names, or convert strings to numbers.

Use the Expression transformation to perform any non-aggregate calculations.

Example: Addition, Subtraction, Multiplication, Division, Concat, Uppercase conversion, lowercase conversion etc.

We can also use the Expression transformation to test conditional statements before we output the results to target tables or other transformations. Example: IF, Then, Decode

There are 3 types of ports in Expression Transformation:

• Input
• Output
• Variable: Used to store any temporary calculation.

Calculating Values :

To use the Expression transformation to calculate values for a single row, we must include the following ports:

• Input or input/output ports for each value used in the calculation: For example: To calculate Total Salary, we need salary and commission.
•  Output port for the expression: We enter one expression for each output port. The return value for the output port needs to match the return value of the expression.

We can enter multiple expressions in a single Expression transformation. We can create any number of output ports in the transformation.

Example: Calculating Total Salary of an Employee

• Import the source table EMP in Shared folder. If it is already there, then don’t  import.
• In shared folder, create the target table Emp_Total_SAL. Keep all ports as in EMP table except Sal and Comm in target table. Add Total_SAL port to store the calculation.

Create the necessary shortcuts in the folder.

 

Creating Mapping:

1. Open folder where we want to create the mapping.
2. Click Tools -> Mapping Designer.
3. Click Mapping -> Create -> Give mapping name. Ex: m_totalsal
4. Drag EMP from source in mapping.
5. Click Transformation -> Create -> Select Expression from list. Give name and click Create. Now click done.
6. Link ports from SQ_EMP to Expression Transformation.
7. Edit Expression Transformation. As we do not want Sal and Comm in target, remove check from output port for both columns.
8. Now create a new port out_Total_SAL. Make it as output port only.
9. Click the small button that appears in the Expression section of the dialog box and enter the expression in the Expression Editor.
10. Enter expression SAL + COMM. You can select SAL and COMM from Ports tab in expression editor.
11. Check the expression syntax by clicking Validate.
12. Click OK -> Click Apply -> Click Ok.
13. Now connect the ports from Expression to target table.
14. Click Mapping -> Validate
15. Repository -> Save

Create Session and Workflow as described earlier. Run the workflow and see the data in target table.

 

As COMM is null, Total_SAL will be null in most cases. Now open your mapping and expression transformation. Select COMM port, In Default Value give 0. Now apply changes. Validate Mapping and Save.

Refresh the session and validate workflow again. Run the workflow and see the result again.

Now use ERROR in Default value of COMM to skip rows where COMM is null.

Syntax: ERROR(‘Any message here’)

Similarly, we can use ABORT function to abort the session if COMM is null.

Syntax: ABORT(‘Any message here’)

Make sure to double click the session after doing any changes in mapping. It will prompt that mapping has changed. Click OK to refresh the mapping. Run workflow after validating and saving the workflow.

Performance tuning :

Expression transformation is used to perform simple calculations and also to do Source lookups.

1. Use operators instead of functions.
2. Minimize the usage of string functions.
3. If we use a complex expression multiple times in the expression transformer, then Make that expression as a variable. Then we need to use only this variable for all computations.

Filter Transformation

Overview:

Transformation type:

1. Active
2. Connected

Use the Filter transformation to filter out rows in a mapping. As an active transformation, the Filter transformation may change the number of rows passed through it. The Filter transformation allows rows that meet the specified filter condition to pass through. It drops rows that do not meet the condition. You can filter data based on one or more conditions.
A filter condition returns TRUE or FALSE for each row that the Integration Service evaluates, depending on whether a row meets the specified condition. For each row that returns TRUE, the Integration Services pass through the transformation. For each row that returns FALSE, the Integration Service drops and writes a message to the session log.
The following mapping passes the rows from a human resources table that contains employee data through a Filter transformation. The filter allows rows through for employees that make salaries of $30,000 or higher.
You cannot concatenate ports from more than one transformation into the Filter transformation. The input ports for the filter must come from a single transformation.

Transformation type:
Active
Connected
150 Chapter 9: Filter Transformation
Tip: Place the Filter transformation as close to the sources in the mapping as possible to maximize session performance. Rather than passing rows you plan to discard through the mapping, you can filter out unwanted data early in the flow of data from sources to targets.
Filter Transformation Components
You can create a Filter transformation in the Transformation Developer or the Mapping Designer. A Filter transformation contains the following tabs:
♦Transformation. Enter the name and description of the transformation. The naming convention for a Filter transformation is FIL_TransformationName. You can also make the transformation reusable.
♦Ports. Create and configure ports.
♦Properties. Configure the filter condition to filter rows. Use the Expression Editor to enter the filter condition. You can also configure the tracing level to determine the amount of transaction detail reported in the session log file.
♦Metadata Extensions. Create a non-reusable metadata extension to extend the metadata of the transformation transformation. Configure the extension name, datatype, precision, and value. You can also promote metadata extensions to reusable extensions if you want to make it available to all transformation transformations.
Configuring Filter Transformation Ports
You can create and modify ports on the Ports tab.
You can configure the following properties on the Ports tab:
♦Port name. Name of the port.
♦Datatype, precision, and scale. Configure the datatype and set the precision and scale for each port.
♦Port type. All ports are input/output ports. The input ports receive data and output ports pass data.
♦Default values and description. Set default value for ports and add description.
Filter Condition
The filter condition is an expression that returns TRUE or FALSE. Enter conditions using the Expression Editor available on the Properties tab.
Any expression that returns a single value can be used as a filter. For example, if you want to filter out rows for employees whose salary is less than $30,000, you enter the following condition:

SALARY > 30000
You can specify multiple components of the condition, using the AND and OR logical operators. If you want to filter out employees who make less than $30,000 and more than $100,000, you enter the following condition:
SALARY > 30000 AND SALARY < 100000
You can also enter a constant for the filter condition. The numeric equivalent of FALSE is zero (0). Any non-zero value is the equivalent of TRUE. For example, the transformation contains a port named NUMBER_OF_UNITS with a numeric datatype. You configure a filter condition to return FALSE if the value of NUMBER_OF_UNITS equals zero. Otherwise, the condition returns TRUE.
You do not need to specify TRUE or FALSE as values in the expression. TRUE and FALSE are implicit return values from any condition you set. If the filter condition evaluates to NULL, the row is treated as FALSE.
Steps to Create a Filter Transformation 151
Note: The filter condition is case sensitive.
Filtering Rows with Null Values
To filter rows containing null values or spaces, use the ISNULL and IS_SPACES functions to test the value of the port. For example, if you want to filter out rows that contain NULL value in the FIRST_NAME port, use the following condition:
IIF(ISNULL(FIRST_NAME),FALSE,TRUE)
This condition states that if the FIRST_NAME port is NULL, the return value is FALSE and the row should be discarded. Otherwise, the row passes through to the next transformation.
Steps to Create a Filter Transformation
Use the following procedure to create a Filter transformation.
To create a Filter transformation:
1.In the Mapping Designer, open a mapping.
2.Click Transformation > Create. Select Filter transformation.
3.Enter a name for the transformation. Click Create and then click Done.
4.Select and drag all the ports from a source qualifier or other transformation to add them to the Filter transformation.
5.Double-click on the title bar and click on Ports tab. You can also manually create ports within the transformation.
6.Click the Properties tab to configure the filter condition and tracing level.
7.In the Value section of the filter condition, open the Expression Editor.
8.Enter the filter condition you want to apply. The default condition returns TRUE.
Use values from one of the input ports in the transformation as part of this condition. However, you can also use values from output ports in other transformations.
9.Enter an expression. Click Validate to verify the syntax of the conditions you entered.
10.Select the tracing level.
11.Add metadata extensions on the Metadata Extensions tab.
Tips
Use the Filter transformation early in the mapping.
To maximize session performance, keep the Filter transformation as close as possible to the sources in the mapping. Rather than passing rows that you plan to discard through the mapping, you can filter out unwanted data early in the flow of data from sources to targets.
Use the Source Qualifier transformation to filter.
The Source Qualifier transformation provides an alternate way to filter rows. Rather than filtering rows from within a mapping, the Source Qualifier transformation filters rows when read from a source. The main difference is that the source qualifier limits the row set extracted from a source, while the Filter transformation