SAP BO Data Services Transforms – Basics Part1
- Hierarchy flattening
- Reverse Pivot
- Text Data Processing
1. Query Transform:
- Query Transform is similar to a SQL SELECT statement.
- It can perform the following operations-
- Choose (filter) the data to extract from sources
- Join data from multiple sources
- Map columns from input to output schemas
- Perform transformations and functions on the data
- Add new columns, nested schemas, and function results to the output schema
- Assign primary keys to output columns
- Different functions can be performed using the query transform like LOOKUP, AGGREGATE,CONVERSIONS, etc.
2. Merge Transform:
- Merge Transform combines the rows from two or more sources into a single target
- The output schema will be the same as the input schema source objects
- All the sources should have –
- Same number of columns
- Same data types of columns
- Same column names
- The transform does not strip out duplicate rows
3. Case Transform:
- Case transform is used to route the Input coming from the Source to two or more targets based upon the given condition.
- Data Inputs
- Only one data flow source is allowed.
- Data Outputs
- The output of the Case transform is connected with another object in the workspace. Choose a case label from a pop-up menu. Each label represents a case expression (WHERE clause) created in the Case editor.
4. Row Generation:
- This transform doesn’t need an input
- Generates a column filled with integer values starting at zero and incrementing by one to the end value you specify.
- You can give the starting row no. as per your requirement
- Generates an additional Effective_To_Column based on the primary key’s “effective date”
- For this the Data Input should have an effective date column.
- Effective_Date allow you to indicate changes to information over time. This Can be used to implement SCD Type 3
6. Pivot Transform:
- Pivot transform creates a new row for each value in columns that we identify as a pivot columns
- It can rearrange the data into a more simple and manageable form, with all data in a single column, without losing the category information
7. Reverse Pivot Transform:
- Reverse pivot transform creates a single row of data from several existing rows
- It allows us to combine data from several rows into a single row by creating new columns
- It can rearrange the data into a more searchable form without losing the category information