4 Flares Twitter 0 Facebook 4 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 4 Flares ×

Data Transfer Transformation:

Data Transfer Transformation in SAP BODSSummary: 

              Data transfer transformation is used extensively to optimize the performance of the job by pushing down operations to the Database level or Operating system level or Cache level.

  • Data transfer transformation insures the operations for ‘push down’. This is performed by the underlying database instead of Data services engine if the transfer type is a database table.
  • Data transfer transformation creates a temporary Database table that is used as staging table for operation, thus insuring the push down.
  • By pushing down operations to the source database, Data services reduce the no. of rows and operations that the engine must retrieve and process, which improves the performance.
  • A Data transfer transform automatically divides the data flow into sub data flows and executes serially.

Prerequisite: 

  • It the transfer type is database table, the sources must be SQL tables. Otherwise the ‘Optimized SQL’ will not be generated through which we ensure whether operations are pushed to database server.
Data Transfer Transformation in BODS
Figure 1: Data Transfer Transformation Object Hierarchy

In this example scenario,

  • In Data flow DF_Import_Sources Source files are imported to SQL tables.
  •  Imported tables are sources in actual Data transfer data flow DF_Imperio_DataTransfer.
  • Operations cannot be pushed down to database server because both sources are from different schemas (data stores).
  • To push down operations to database server, the sources (or source and target in other scenarios) should be of same schema.
  • Data transfer transform is placed after Source 1 and a temporary table is created in it which is from same schema that of Source 2 as shown in figure 2.
Data Transfer Transformation in SAP BODS 1
Figure 2: Data Transform Transformation ETL Job Flow
  • Thus the two sources will be from same data store and operations are pushed down to database server automatically.
  • Data transfer thus divides the data flow into two sub data flows.
Data Transfer Transformation in SAP BODS 2
Figure 3: Query Transformation Mapping Rules
  • Where condition is used to ‘join’ the two source tables as shown in figure 3.
  • ‘Where’ operation cannot be pushed down to data base server if sources are of different schemas.
  • To push down this operation to source data base Data Transfer transform is used  before the Query as in Figure 2.
  • In Data Transfer transform editor properties, Check the ‘Enable transfer’ option to activate the transfer type as shown in figure 4.
  • Table’ transfer type is selected and ‘Push_Down_Table’ table is provided which is a temporary table.
  • The schema (data store) of temporary table should be same that of second source IMPERIO_DATATRANSFER_SOURCE2 because the operations are pushed down to database server automatically if both the sources are of same schema.
Data Transfer Transformation in SAP BODS 3
Figure 4: Data Transfer Transformation Defining Rules

NOTE:

  • Table created or selected inside the Data Transfer transform may or may not already be present in the data base and the table (or data) is temporary in the database till the job got executed.
  • If transfer type is ‘File’, Root directory and File name have to be provided which acts as a temporary file and the operations are pushed down to the operating system level.
  • The temporary file can be seen at the root directory only during the job gets executed which ensures the push down.
Data Transfer Transformation in BODS 2
Figure 5: Data Transfer Transformation job Optimized SQL
  • Optimized SQL ensures the push down of operations if transfer type is database.
  • From figure 5 it is clear that operations are performed by Data base instead of Data services engine.
  • If in case there is no data transfer, the ‘where’ operation is performed by the job server itself i.e. ‘where’ clause is not generated in optimized SQL.

NOTE:

  • To view optimized SQL, go to ‘Validation’ menu option and select ‘Display optimized SQL’.
Data Transfer Transformation in SAP BODS 4
Figure 6: Data Transfer Transformation Trace Log
  • If you observe the above log it is clear that Data Transfer transform divides the dataflow into two sub data flows.

Attachments:

Source File: 

  • Use the below files as sources for working out on the transformation.

Click Here to Download Source File 1.

Click Here to Download Source File 2.

.atl File:

  • Import the below .atl file in the Data Services Designer to find the Job Date Transfer transformation.

Click Here to to Download ATL File.

That’s it.

Enjoy 🙂

4 Flares Twitter 0 Facebook 4 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 4 Flares ×