After creating a data flow and adding a source object, you can incorporate transforms to manipulate your data.
The Query transform is the most frequently used transform in SAP Data Services.
As a member of the Platform transform category, the Query transform is essential to most data flows. Consequently, it is conveniently located in the tool palette alongside other standard objects.
The Query transform allows you to select, filter, and reformat data as it moves from the source to the target. The following figure illustrates a Query transform applying a filter to the data stream.
Query Transform Operations
- Filter data extracted from sources
- Join data from multiple sources
- Map columns from input to output schemas
- Apply transformations and functions to the data
- Perform data nesting and unnesting
- Add new columns, nested schemas, and function results to the output schema
- Assign primary keys to output columns
You define these operations using the Transform Editor, a graphical interface for configuring transform properties. The workspace includes the following areas:
- Input schema
- Output schema
- Options
Input and Output Schemas
The following figure displays the input and output schema areas.
The input schema area shows the structure of the source data set, while the output schema area shows the structure of the target data set, including any applied functions.
To move data from the source to the target, you must establish a relationship between the input and output schemas by mapping each input column to its corresponding output column.
Map Input Columns to Output Columns
Perform one of the following actions in the transform editor to map input columns to output columns:
- Drag a single column from the input schema area to the output schema area.
- Drag an input column over an existing output column, release the mouse button, and select Remap Column from the menu.
- Select multiple input columns using Ctrl+click or Shift+click and drag them to the output schema for automatic mapping.
- Select an output column and manually define the mapping in the Mapping tab of the options area. You can either type the column name or drag it from the input schema pane.
- Select an output column and manually delete its mapping in the Mapping tab.
Options Area
The Options area is located below the input and output schema areas in the Query Transform Editor.
Options Area Tabs
| Tab | Description |
|---|---|
| Mapping | Define how the selected output column is calculated or derived. |
| Select | Filter the output to include only distinct rows, removing duplicates. |
| From | Identify the input schemas used to populate the current output schema. |
| Outer Join | Configure inner and outer tables for joins that require outer join logic. |
| Where | Define conditions to filter which rows are included in the output. |
| Group By | Identify columns used to aggregate or group the output data. |
| Order By | Define the columns used to sort the resulting data set. |
| Advanced | Configure separate subflows to handle resource-intensive query operations. |
| Find | Search for specific items within the input or output schemas. |
Query Transform Editor for Joins
Joins are configured in the From tab of the Transform Editor, as shown in the following figure.
Note
While joins can be defined in the WHERE tab, this method is restrictive. It is recommended to use the join features available in the From tab instead.
Summary
In this lesson, you have learned how to use the Query transform that:
- Acts as the most frequently used Platform for selecting, filtering, and reformatting data.
- Allows functions to filter data, join multiple sources, map columns, apply functions, and add new columns.
- Establishes relationships between input and output schemas by dragging columns or using the Mapping tab.
- Configures data operations and filtering through the Mapping, Where, Group By, and From tabs.
- Manages data joins in the From tab rather than the Where tab for optimal flexibility and functionality.