Developer Transformation Guide > Web Service Consumer Transformation > Filter Optimizations
  

Filter Optimizations

Filter optimization increases performance by reducing the number of rows that pass through the mapping. The Data Integration Service can apply the early selection optimization or push-into optimization.
When the Data Integration Service applies a filter optimization method, it moves a filter as close to the source as possible in a mapping. If the Data Integration Service cannot move a filter before a transformation in a mapping, it might be able to push the filter logic into a transformation.

Enabling Early Selection Optimization with the Web Service Consumer Transformation

Enable early selection optimization for the Web Service Consumer transformation if the transformation does not have side effects and it does not treat faults as errors.
  1. 1. Open the Web Service Consumer transformation Advanced Properties view.
  2. 2. Clear Treat Fault as Error.
  3. 3. Clear Has Side Effects.

Push-Into Optimization with the Web Service Consumer Transformation

With push-into optimization, the Web Service Consumer transformation receives the filter value in a filter port. The filter port is an unconnected input port that you identify as a filter port when you configure push-into optimization. The filter port has a default value that ensures that the web service returns all rows if the end-user query contains no filter. The filter port is not a pass-through port.
Note: The filter field must be part of the root group in the web service request.
The filter field cannot be a pass-through port. When you configure a filter port, the default value of the port changes to the value of the filter condition, so the pass-though output port value changes. A filter based on the output pass-through port returns unexpected results.
You can push multiple filter expressions to the Web Service Consumer transformation. Each filter condition must be the following format:
<Field> = <Constant>
The filter conditions must be joined by AND. You cannot join the conditions with an OR.

Push-Into Optimization with Web Service Consumer Transformation Example

An SQL data service returns orders for all customers or it returns orders for a specific customer based on the SQL query it receives from the user.
The data service contains a logical data object with the following components:
Customer table
An Oracle database table that contains customer information.
Web Service Consumer transformation
A transformation that calls a web service to retrieve the latest orders for customers. The Web Service Consumer transformation has input ports for customerID and orderNum. The transformation has pass-through ports that contain customer data that it receives from the Customer table. The orderNum port is the filter port and is not connected. orderNum has the default value "*". When the web service receives this value in the web service request, it returns all orders.
Orders virtual table
A virtual table that receives the customer and order data from the web service. The end-user queries this table. Orders contains a customer column, orderID column, and customer and order data.
The end-user passes the following SQL query to the SQL data service:
SELECT * from OrdersID where customer = 23 and orderID = 56
The Data Integration Service splits the query to optimize the mapping. The Data Integration Service uses early selection optimization and moves the filter logic, customer = 23, to the Customer table read. The Data Integration Service uses push-into optimization and pushes the filter logic, orderID = 56, into the Web Service Consumer transformation filter port. The Web Service Consumer transformation retrieves ordersID 56 for customer 23.

Enabling Push-Into Optimization with the Web Service Consumer Transformation

Enable push-into optimization for the Web Service Consumer transformation if the transformation does not have side effects and it does not treat faults as errors.
  1. 1. Open the Web Service Consumer transformation Advanced Properties view.
  2. 2. Clear Treat Fault as Error.
  3. 3. Clear Has Side Effects.
  4. 4. Click the Open button in the Push-Into Optimization property.
  5. 5. Choose the filter port name in the Optimized Input dialog box.
    6. You can choose multiple filter ports.
  6. 6. Click the Output column.
  7. 7. For each filter port, choose the output port that contains the filtered column in the web service response.
  8. 8. Enter a default value for each filter port.
    9. Note: You cannot configure a default value for a Web Service Consumer port unless it is a filter port.