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

SQL Transformation

SQL Transformation in SAP BODSSummary:-

SQL transformation is used to perform standard SQL Operations in Data services but it is not supports all SQL operations. It supports only retrieval operations (select statements).Using SQL transformation we can’t manipulates (Insert, Delete, Update) the data.

SQL transformation and lookup function have done same operations. .. Performance of SQL transformation is very good compared to lookup function.

Prerequisite:-

  • SQL transformation supports only tables not files or other sources in SQL text.

Example Scenario:-

  • In this scenario, we have three source tables as shown in figure 1, 2 and 3 but we want 3 source tables’ data information in one target table based on business requirement.
  • In this condition we go for SQL transformation. SQL transformation is used to join the 3 source tables and gets the output in single target as shown in figure 4.
  • NOTE: – You can get same output using lookup function but it will take more time compared to sql transformation
SQL Transformation in SAP BODS 1
Figure 1: Sample Source Data1
SQL Transformation in BODS
Figure 2: Sample Source Data2
SQL Transformation in BODS
Figure 3: Sample Source Data3

 

SQL Transformation in SAP BODS
Figure 4: Sample Target Data

Figures 5, 6 and 7 shows the object hierarchy for validation transformation job, ETL job flow and the way we define the SQL statements respectively.

SQL Transformation in SAP BODS
Figure 5: SQL Transformation Object Hierarchy
SQL Transformation in SAP BODS
Figure 6: SQL Transformation ETL Job Flow

In SQL Transformation:-

Data store:

  • The name of the Data Store that Data Services uses to access the tables referred to in SQL text.

SQL Text:-

  • In SQLtext, select statement is used to get the data from three tables using join condition.

Update Schema:-

  • Click the option updateschema to populate the output schema for the SQL SELECT statement. 
  • You can these options in below figure 7.
SQL Transformation in SAP BODS 5
Figure 7: Defining SQL statements in SQL text for SQL Transformation

That’s it.

Enjoy 🙂

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