Integrating Open SQL Schema Tables with SAP Datasphere Space using Dataflow

[Mrinal_Sinha]

Data flows play a crucial role in managing the extraction, transformation, and loading (ETL) of data.Data flows are purpose-built for robust data handling — enabling both complex transformations and the permanent persistence of structured data into target tables.This makes them a powerful tool for building scalable, maintainable, and performance-optimized data pipelines within SAP Datasphere.

In this blog post, we’ll focus on how to integrate Open SQL Schema Tables within Data Flows - a powerful option for handling specific enterprise integration scenarios. 

Why Use Open SQL Schema Tables Instead of Datasphere Local Tables? 

While this blog is centered on how to use Open SQL Schema Tables, it's useful to understand a few real-world use cases where they offer clear advantages over Datasphere Local Tables: 

1. Restricted Access Due to Enterprise Security Protocols 

In some organizations, strict security and infrastructure policies restrict direct access to core systems. For instance: 

• Your company uses MuleSoft as the only approved middleware to access critical financial systems. 
• Direct interaction between SAP and third-party systems is not permitted. 

In such cases, Open SQL Schema Tables act as a bridge: data extracted from third-party systems via MuleSoft APIs can be loaded into Open SQL Schema Tables, which can then be used as a source or target in SAP Datasphere. Datasphere Local Tables cannot be directly exposed to external systems, making Open SQL Tables essential here. 

2. Sharing Forecast Data with External Systems 

Imagine you're working on a forecasting project that involves: 

• Pulling data from S/4HANA using Replication Flows 
• Building logic in Datasphere 
• Storing and sharing weekly forecast snapshots with third-party applications 

You have two options: 

• Option A: Use a Datasphere Local Table as the target, create a wrapper view, and expose it for consumption. 
• Option B: Use an Open SQL Schema Table as the target, which can then be accessed directly by any third-party tool that supports DB connections and user access.

These are just a couple of scenarios—there are many others where Open SQL Schema Tables are the more viable solution. 

How to Use Open SQL Schema Tables in a Data Flow 

Here is a step-by-step guide to integrating Open SQL Schema Tables within your SAP Datasphere Data Flows: 

Step 1: Create a Database User 

• Ensure that the user has appropriate read/write privileges to the database schema associated with your Datasphere space. 

Step 2: Create and Populate the SQL Table 

• Use database explorer to create an Open SQL Schema Table and insert a few records for validation purposes. 

Step 3: Create the Data Flow 

• Design your Data Flow in SAP Datasphere.
• Use the Open SQL Schema Table as the source 
• Use a Datasphere Local Table as the target.
• Perform any required transformations or mappings 

 Step 1: Creating a database user

Please follow the steps mentioned here - Creating a Database User 

Step 2: Creating a table and inserting records in it.

Go to Space Management - Select the right space - Click on edit - Go to Database Access - Select the Database User and then Click on "Open Database Explorer"

It will open another tab, please login in database explorer with your S user. Once you login to Database Explorer , we need to add hana database.

You can find the host details in Space Management - Database Access

Create and Insert Script :

CREATE COLUMN TABLE EMP_ID
(EMP_ID INT PRIMARY KEY,
EMP_NAME NVARCHAR(1000),
EMP_COUNTRY NVARCHAR(500)
);
--------

INSERT INTO EMP_ID VALUES (100,'Paul John','United State');
INSERT INTO EMP_ID VALUES (101,'Mike Hussey','Canada');
INSERT INTO EMP_ID VALUES (102,'William Jack','United Kingdom');
--------

SELECT * FROM EMP_ID;

 

Step 3: Creating a Dataflow with SQL schema table as a source and Datasphere local table as a target.
To build a data flow that reads from an SQL schema table and writes to a Datasphere local table, follow these steps:

1. Navigate to the Correct Space and Folder
• Open your SAP Datasphere Space.
• Go to the desired folder.
• Click the “+” icon and select “Data Flows” from the available options.
2. Choose the Source Table
   • Datasphere Repository – for accessing objects already deployed within Datasphere.
   • Sources – for accessing connections and databases associated with your space.
3. Import the Open SQL Schema Table
• From the list of available tables, locate and select the EMP_ID table (created earlier).
• Drag this table into the Data Flow canvas as a source object.
• When prompted, Import and Deploy the table. This creates a local representation of the SQL schema table within your Datasphere space.

         

       

         4.)Choose a target table for your data flow. You have three options:

• Select from Repository: Use an existing local table already deployed in the Datasphere Repository.
• Select from Sources: Pick a table from a connected source system or database.
• Create a New Table: You can also create a new target table directly within the data flow.

Note:
If you choose to create a new target table within the data flow, you will be required to Create and Deploy the table before executing the data flow.

         

         5.)Map the source columns to their corresponding target columns to define the data transformation logic.

               Choose the appropriate load mode based on your use case:

• APPEND – Adds new records without modifying existing data.
• APPEND + UPSERT – Adds new records and updates existing ones based on key fields.
• DELETE – Removes records from the target based on matched columns.
• TRUNCATE – Clears all data from the target table before loading.

               Once the column mappings and load mode are configured, deploy the Data Flow.

         6.)Then run the dataflow and do the data preview.
        

Once the data flow is run, it can be scheduled or monitored from Data Integration Monitor Tab.