Creating a Transformation Flow in a File Space

Create transformation flows with tables as sources, apply various transformations, and store the resulting dataset into another local table (file).

Prerequisites

To create flows, you must have a scoped role that grants you access to a space with the following privileges:
  • Data Warehouse General (-R------) - To access SAP Datasphere.

  • Data Warehouse Connection (-R------) - To access remote objects.

  • Data Warehouse Data Builder (CRUD----) - To create, edit, and delete flows.

  • Space Files (CRUD----) - To create, read, update, and delete objects in your spaces.

To run and schedule flows, you must, in addition, have the following privileges:
  • Data Warehouse Data Integration (-R------) - To view data integration task logs in the Data Integration Monitor app.

  • Data Warehouse Data Integration (--U-----) - To manually run data integration tasks.

  • Data Warehouse Data Integration (----E---) - To schedule data integration tasks.

The DW Modeler role template, for example, grants the privileges to create and manage flows, and the DW Integrator role template grants the privileges to run them. For more information, see Privileges and Permissions and Standard Roles Delivered with SAP Datasphere.

Introduction

You want to model transformation flows with tables as sources, apply various transformations in a file space dedicated to loading and preparing large quantities of data, and store the resulted dataset into another local table (file).

Procedure

  1. In the side navigation area, click (Data Builder), select a file space (if required) and click New Transformation Flow.

  2. On the New Transformation Flow screen, add a source:

    • One source table: drag and drop an object onto the source operator. See Add a Source to a Graphical View. Note that you can only add local tables (file) that don't have deletion vectors enabled, shared local tables (file), local tables shared from a SAP HANA space, and shared remote tables on a Delta Share runtime.

      Certain data types that are supported in a SAP HANA Space aren't in a file space and require conversion to supported types. See Converting Local Table Data Types from a HANA Space to a File Space.

    • Two or more source tables: click the View Transform node, and then click either the Graphical View Transform or SQL View Transform buttons. See Create a Graphical View in a Transformation Flow on File and Create a SQL View in a Transformation Flow on File.

      You can either use a graphical view transform or a SQL view transform in a transformation flow, not both.

  3. Add a transformation. The View Transform does not support all functions available in a transformation flow created in an SAP HANA space. See List of Functions Supported by a Transformation Flow (in a File Space):

    Supported Transformations Reference

    Join

    See Create a Join in a Graphical View.

    Union

    See Create a Union in a Graphical View.

    Projection/Rename

    See Reorder, Rename, and Exclude Columns in a Graphical View.

    Filter

    See Filter Data in a Graphical View

    When a shared source is connected directly to the filter node in the view transform, the filter is applied before exporting the data to the local table (file) to reduce the size of the file and data movement.

    Calculated Column

    See Create a Calculated Column in a Graphical View.

    Aggregation

    See Aggregate Data in a Graphical View.

  4. [optional] For machine learning, AI, and analytics use cases, you may need to simplify complex star-schema data models by joining tables to create a flattened view. See Creating a Flatten Operator.

  5. After adding a new source, you might encounter duplicate records in your dataset. The Remove Duplicate Records operator allows you to efficiently remove these duplicates from your transformation flow. See Removing Duplicate Records.

  6. [optional] If your source is a shared table with Delta Capture enabled:

    • you can change its load type (All Active Records or Delta Capture) in its settings panel.

    • if the load type is Inital and Delta, delta changes are propagated to the target table, and even to non-delta target table. See Capturing Delta Changes in Your Local Table.

  7. [optional] Add a Python operator to transform incoming data with a Python script and output structured data to the next operator. See Creating a Python Operator.

  8. Add a target table. See Create or Add a Target Table to a Transformation Flow.

  9. [optional] Add incremental aggregations to the target table. It is useful for handling incremental data loads and maintaining aggregated results efficiently. See Creating an Incremental Aggregation on a Target Table in a Transformation Flow on File.

  10. Review the properties of your transformation flow, save, deploy, and run it. See Creating a Transformation Flow.

  11. You can share the target local table (file) to another space, including to a space dedicated to SAP HANA Database (Disk and In-Memory) storage.

  12. More flow analysis options are available in the transformation flow monitor via the Data Integration Monitor, like Simulate Run, Generate a SQL Analyzer Plan File, or Set Priorities and Statement Limits for Spaces or Groups. See Explore Transformation Flows.

  13. [optional] You can download your transformation flow on file Spark driver logs in the Data Integration Monitor in the flow's Details screen. To download this file, you must have the DWC_RUNTIME privilege added to your DW Administrator role or custom role. There are no logs to download if the run fails before the Spark driver gets started. See Monitoring Flows.