Push-Into Optimization with Web Service Consumer Transformation Example
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.