SAP BO Data Services Transforms – Basics Part1

SAP BO Data Services Transforms – Basics Part1

Transforms:

• Query                  
• Case
• Merge
• Row_Generation
• Key_Generation
• Date_Generation
• Effective_Date
• Table_Comparison
• Hierarchy flattening
• History_Perserving
• Pivot
• Reverse Pivot
• Map_Operation
• Validation
• SQL
• XML_Map
• Data_Transfer
• 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.

Source:

Target:

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

5. Effective_Date:

• 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