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

Summary:-

Query Transformation Query transform is used to retrieve a data set based on the input schema that satisfies conditions that we specify. A query transform is similar to a SQL SELECT statement. The Query transform is used to perform the following operations: –

  • Maps column from input Schema to output Schema.
  • Perform transformations and functions on the source data.
  • Assign Primary Keys to output Schema columns.
  • Add New Output columns, Nested Schemas, and Function Calls to the output Schema.
  • Perform Data Nesting and Unnesting with Sub Schemas of the Output Schema. Also assign Make Current Schema.
  • Generate distinct result set output for the input Schema.
  • Join data from Multiple Input Source Schemas. Equi Join as well as Outer Join is supported.
  • Filter input Source Data.
  • Performs Aggregation based on input column groups.
  • Generate sorted dataset based on source input column order.
  • Also we can generate DTD, XML Schema or File Format based on the Input or Output Schema.
  • All these options are shown below figures 1, 2.
Query Transformation BODS
Figure 1: Options in Query transformation – a
Query Transform in BODS
Figure 2: Options in Query transformation – B

Example Scenario:-

  • In this scenario, we have data in flat file as shown in figure 3 but we want to load data into data base table using data services ETL tool.
  • In this case we have to use query transformation. This is the basic transformation.
  • Query transformation is used to maps columns from input schema to output schema and perform transformations and functions on the source data based on requirement.
Query Transformation in SAP BODS
Figure 3: Sample Source Data1
Query Transformation in BODS
Figure 4: Sample Target Data

Figures 5,6 and 7 shows  the object hierarchy for Query transformation job, ETL job flow and the way we define the Mapping rules respectively.

Query Transform in BODS 1
Figure 5: Query Transformation Object Hierarchy
Query Transformation in SAP BODS 1
Figure 6: Query Transformation job ETL Flow
Query Transformation in SAP BODS 2
Figure 7: Defining Mapping rules in Query Transformation.

That’s it.

Enjoy 🙂

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