SAP Data Services – Query Transform

Schema Out:

  • The figure below shows the schema Out or the output table for the query transform.

6

  • The options available for Schema Out upon right click are:
    • Cut: Used to Cut an output Column/Columns
    • Copy: Used to Copy an output Column/Columns
    • Paste: Used to paste a Cut or Copied output column
    • Delete: Used to delete a specific output Column/Columns
    • Find: Used to find a column
    • Make Current: Makes the selected schema, or the schema of the selected element, the current schema.
    • New Output Column: Used to create a new output column.
    • New output Schema: Used to create a new output schema.
    • New Function Call: Used to generate a new function call.
    • Modify Function Call: Used to modify a function call in use.
    • Unnest: Used to Unnest the nested schemas.
    • Nest with Sub-schemas: Used to nest a schema with its sub- schemas
    • Unnest with Sub-schemas: Used to unnest a schema from its sub- schemas
    • Primary key: Used to assign or reverse primary key settings on output columns. Primary key columns are flagged by a key icon.
    • Optional: Toggles to make a schema optional.
    • Generate DTD: Generates a DTD format that corresponds to the structure of the selected schema (either NRDM or relational). Generates all data types as varchar.
    • Generate XML Schema: Generates an XML Schema that corresponds to the structure of the selected schema (either NRDM or relational). All data types match those of the selected schema.
    • Map to Output: Creates a simple mapping from the input schema area to the output schema area.
    • Create File Format: Creates a file format from a relational table schema. All data types match those of the original table schema.
    • Create HDFS File Format: Creates a file format from a HDFS schema. All data types match those of the original HDFS file schema.
    • Properties: Displays the properties of the selected element. We cannot modify the properties here.
HDFS – HADOOP Distributed File System

Query Transform Properties:

  • The below image shows the properties available in a Query Transform.

7

  • The different tabs present in query transform are:
    • Mapping
    • SELECT
    • FROM
    • WHERE
    • GROUP BY
    • ORDER BY
    • ADVANCED
    • Find
  • Mapping:
    • Mapping tab is used to provide complex column mappings.
    • To enable the editor, drag and drop input schemas or at least a single column into the output schema.

8

  • We can straight away write the SQL required for output column, in the blank space provided, also the SELECT through ORDER BY tabs to provide additional parameters for the current schema (similar to SQL SELECT statement clauses).
  • The functions and the smart editor are also used to build expressions for an output column.
  • Let’s look at a small example as to how to use the functions, Click on Functions, which opens the below pop up.

9

  • We can select the type of the function we need, like AVG from Aggregate Functions here and click Next.
  • Provide the name of the source column on which we want to apply the function and click on finish.

Please proceed to next page to continue reading…

 

Leave a Reply

Your email address will not be published. Required fields are marked *

three × two =