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.

                    /wp-content/uploads/2014/02/1_387142.jpg

                       /wp-content/uploads/2014/02/1_387142.jpg

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

                   /wp-content/uploads/2014/02/1_387142.jpg

                      /wp-content/uploads/2014/02/1_387142.jpg

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.

             /wp-content/uploads/2014/02/1_387142.jpg

              /wp-content/uploads/2014/02/1_387142.jpg

          

Source:



                /wp-content/uploads/2014/02/1_387142.jpg

Target:

        

                    /wp-content/uploads/2014/02/1_387142.jpg

           

                       /wp-content/uploads/2014/02/1_387142.jpg

                         /wp-content/uploads/2014/02/1_387142.jpg

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

                       /wp-content/uploads/2014/02/1_387142.jpg


                          /wp-content/uploads/2014/02/1_387142.jpg

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

                           /wp-content/uploads/2014/02/1_387142.jpg

                               /wp-content/uploads/2014/02/1_387142.jpg

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

                   /wp-content/uploads/2014/02/1_387142.jpg

                      /wp-content/uploads/2014/02/1_387142.jpg

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

                   /wp-content/uploads/2014/02/1_387142.jpg

                /wp-content/uploads/2014/02/1_387142.jpg

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply