Query Transformation in SAP BODS
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.
- 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.
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.