SAP Data Services optimizes data flows using SQL push-down when all sources are in the same datastore or linked databases. However, optimization becomes more complex when you join different source types, such as a database table and a flat file.
The Data Transfer transform addresses this challenge. It enables the push-down of resource-intensive operations to the database server, even when sources are not naturally linked.
This transform works by moving data from a source or a preceding transform into a temporary transfer object. It then reads the data from this object to perform the next operations in the flow.
You can use the Data Transfer transform to offload heavy database operations at any point within a data flow.
Data Transfer Usage
- Combine files or tables from different databases to enable a full SQL push-down operation within your data flow.
- Push down resource-intensive operations, such as joins, GROUP BY, and sorts, to the database level.
The following figure shows the interface used to configure these push-down operations.
Transfer Types
You can choose from different transfer types:
- Table: Uses a database table from an existing datastore as a staging area.
Select this type to enable SQL push-down.
In the options, configure the Table name, Database type, and Array fetch size.
- File: Uses a flat file on disk as a staging area.
Use this type if SQL push-down is not possible but you need to offload intermediate results to disk. This prevents very large data sets from exhausting the Data Services engine memory.
In the options, configure the Root directory and File name.
- Automatic: The optimizer automatically selects the transfer type based on push-down availability.
- If push-down is possible, the optimizer looks for datastores with the Enable automatic data transfer option enabled. It then determines where to store and how to call the temporary table.
- If push-down is not possible, it creates a temporary file in the pageable cache directory specified in the Server Manager.
Data Input and Output Requirements
Before configuring your data sets, review the following input and output requirements to ensure compatibility and proper processing.
Data Input Requirements
- If the input data set uses a Table or File transfer type, rows must be flagged with the NORMAL operation code.
- The input data set cannot contain hierarchical or nested data structures.
Data Output Requirements
Output data sets maintain the same schema and operation codes as the input data sets.
- Output rows are returned in the specified sort or GROUP BY order.
- SAP Data Services automatically splits the data flow into secondary flows and executes them in sequence. These secondary flows follow the naming convention: dataflowname_n
The variable <n> represents the sequence number of the secondary data flow.
Summary
In this lesson, you learned how to use the Data Transfer transform to optimize your data flows.
- The Data Transfer transform enables SQL push-down operations even when data sources are located in different datastores.
- Use this transform to offload resource-intensive operations, such as joins, GROUP BY, and sorts, to the database server.
- There are three transfer types: Table, File, and Automatic. Each stores temporary data differently.
- Input data must use the NORMAL operation code and cannot include hierarchical or nested structures.
- SAP Data Services automatically divides the data flow into sequential secondary flows when this transform is used.