Data_Transfer
This Transformation helps us in transferring the data in an effective mode.
Using this we can push the operands in to the data base (like Group by or Order by on the database table).
Using this we can push the operands in to the data base (like Group by or Order by on the database table).
Example: -Assume we are doing a lookup on the data and a group by on the same date in the same dataflow.
If we are doing that on millions of records, it’s a performance hit. So using the data transfer transformation,
this splits the same data flow and run them as a separate data flows for lookup and as a separate data flows
for the group by/ orders by.
If we are doing that on millions of records, it’s a performance hit. So using the data transfer transformation,
this splits the same data flow and run them as a separate data flows for lookup and as a separate data flows
for the group by/ orders by.
See the below job which does a lookup on the data and also a groupby.Using the DataTransfer transformation,
it will run them as a separate process.
it will run them as a separate process.
In the query_lkp Transform, we are using a lookup on the table Orders.
Using the Data_transfer transformation, we do the following settings.
First we need to select the Enable the transfer by checking the check box.
Transfer Type:- (Table, File, Automatic)
Table, if you select the table, automatically the remaining tabs (options, bulk loader options,
Pre-load commands, post-load commands) will be visible.
And also the table Options will be activated.
Pre-load commands, post-load commands) will be visible.
And also the table Options will be activated.
Select the data store and specify a table name which is not existing under the data store.
This is because, this table will be created at the run time and will be automatically deleted
at the end of the job. File, if you select the transfer type as file, it works same as above.
This is because, this table will be created at the run time and will be automatically deleted
at the end of the job. File, if you select the transfer type as file, it works same as above.
When you select Automatic, it automatically identifies the type of the source you mentioned.
Query-Group by.
Using this query transform we are performing a group by on the field ProductID .
While performing a group by on a field, we need to perform min()/ Max() functions on the remainingfields.
Run the job and observe the trace, how the job is splitting each function in to different dataflows.
