EDUCAÇÃO E TECNOLOGIA

SAP Data Intelligence – Building a Pipeline in Modeler. Transfer Data from a File to Staging Tables on HANA DB Schema for Data Migration

Hello Everyone,

I hope this post finds you safe and healthy amidst the challenging times the World is going through.

In this blog post you will learn how to create a basic pipeline or graph in Data Intelligence Modeler for transferring data from a file to the staging tables created for data migration in SAP HANA DB Schema.

I recently worked on this SAP Landscape Transformation scenario wherein we are going to perform Data Migration from current DB to SAP S/4 HANA. In order to migrate the data, we will be using the SAP Migration Cockpit (TCODE: LTMC), where we are taking the Staging Tables approach. To fill the Staging Tables and performing the ETL activities we will be using SAP Data Intelligence.

SAP Data Intelligence Overview

SAP Data Intelligence is the data orchestration layer of SAP’s Business Technology Platform. It is a widespread data management solution that transforms distributed data into important data insights, bringing innovation at scale. It empowers the creation of data warehouses from diverse enterprise data, simplifies the management of IoT data streams, and facilitates accessible machine learning. SAP Data Intelligence allows you to power your business applications to become an intelligent enterprise and provides an all-inclusive, unified way to manage, integrate, and process all your enterprise data.

With SAP Data Intelligence you can:

  • Discover and connect to any data, anywhere, anytime from a single enterprise data fabric
  • Transform and supplement data across complex data types and curate a robust, searchable data catalog
  • Implement intelligent data processes by orchestrating complex data flows enriched with scalable, repeatable, production-grade machine learning pipelines

Being new to SAP Data Intelligence, I had to do a lot of study and research on the different components available in SAP Data Intelligence to be able to create this Graph. Some sessions from SAP TechEd 2020 helped me a lot to improve my knowledge about this platform.

SAP Data Intelligence provides two major components for use in SAP Data Intelligence Modeler:

SAP Data Intelligence Operators

SAP Data Intelligence provides built-in operators, that can be directly used in a graph or used as the basis for a custom operator. An event from the environment is a message delivered to the operator through its input ports. The operator can interact with the environment through its output ports.

SAP Data Intelligence Graphs

A graph is a network of operators connected to each other using typed input ports and output ports for data transfer. Users can define and configure the operators in a graph.

This is how the SAP Data Intelligence Modeler looks like:

SAP%20Data%20Intelligence%20Modeler

SAP Data Intelligence Modeler

As you can see in the above figure, there are different Operators available in the Modeler that serve different purposes and can be used in Graphs as per the requirement. Hovering over any operator and clicking on More Details will take you to the documentation of that Operator, where you can find all the information about the Input and Output ports.

Sample%20Graph

Sample Graph- File System

Above figure shows an example of a Graph already available in the Modeler. The button highlighted on the right gives you brief documentation about the Graph. Just besides that, with the button ‘JSON’ you can also see the JSON file for the entire Graph.

Scenario

The actual scenario is migrating the data from Oracle DB to SAP S/4 HANA. I will soon be publishing another Blog post to demonstrate the designing a Graph for this purpose in the Modeler.

In this blog post I am going to show how to create a Graph in SAP Data Intelligence Modeler for transferring data from a file to the staging tables created for data migration in SAP HANA DB Schema.

For this demo we are going to use the Bank Data for updating the staging tables.

Prerequisites:

  • Staging tables should be created in SAP HANA DB Schema.

This will be done using the SAP Migration Cockpit (LTMC) and selecting the Staging Tables approach while creating the Migration Project.

In our case the staging table for Bank Master Data is created with the following target structure.

          Target%20Structure%20for%20Staging%20Table%20-%20Bank%20Master%20Data

Target Structure for Staging Table – Bank Master Data

  • Connection to SAP HANA DB Schema must be created in SAP Data Intelligence Connection Management.

Connection%20Management%20In%20SAP%20Data%20Intelligence

Connection Management In SAP Data Intelligence

  • Input file with the same structure as the target table structure should be uploaded in the Data Lake. The file is in CSV format with Field Delimiter as ‘;’ and Row Delimiter as ‘|’.

Input%20CSV%20File%20Uploaded%20In%20Data%20Lake

Input CSV File Uploaded In Data Lake

In our case I have used a small file with 2 records as shown below.

Data%20Preview%20of%20the%20File%20in%20Metadata%20Explorer

Data Preview of the File in Metadata Explorer

Graph Design

In the SAP Data Intelligence Modeler, click on the Create Button in the Graphs tab to create a new Graph.

Create%20Graph

Create Graph

We are going to use two major Operators in this demo:

  • Read File
  • SAP HANA Client

From the Operators tab, you can simply drag and drop the operator to your Graph Diagram.

Operators%20in%20SAP%20Data%20Intelligence

Operators in SAP Data Intelligence

Click on Save to save the Graph.

Save%20the%20Graph

Save the Graph

Give a name, description and choose a category. Then click on OK to Save.

Graph%20Attributes

Graph Attributes

Select an Operator and click on the below icon to set up the configuration of the operator. In the configuration, the Input and Output ports can be configured, and other necessary parameters can be maintained.

As you see below, I have selected the file from the data lake to be used as an input for the pipeline.

Operator%20Configuration%20-%20Parameters

Operator Configuration – Parameters

Similarly, also in the SAP HANA Client Operator, the SAP HANA DB Schema connection details and table name should be maintained.

SAP%20HANA%20Client%20-%20DB%20Connection%20Configuration

SAP HANA Client – DB Connection Configuration

Save the graph and you receive a message that Validation is complete, and the graph is good to run.

Save%20and%20Validate%20Graph

Save and Validate Graph

Click on the Run button to execute the Pipeline. You can see in the Status tab, the Graph is running.

Graph%20Execution

Graph Execution

I have used the Wiretap operator to preview the data at that particular point in the Graph.

Once the execution is complete, you can see the data is updated in the Staging Table.

TCODE SE16H:

Enter the DB Schema name and Staging Table Name:

Data%20Preview%20-%20HANA%20DB%20Schema

Data Preview – SAP HANA DB Schema

Then Execute:

Staging%20Table%20-%20Bank%20Data%20Updated

Staging Table – Bank Data Updated

As you can see, the data as present in the file is updated in the Staging table.

From here the Migration can be completed using the SAP Migration Cockpit TCODE LTMC.

Conclusion

So, that is a short demo on creating a pipeline using SAP Data Intelligence. In this post you learnt about SAP Data Intelligence, what are the different components  in the SAP Data Intelligence Modeler, what operators are, what a graph is, the configuration of operators and how to create a Graph in SAP Data Intelligence using the Modeler.

This post will definitely help you in creating your first Graph using SAP Data Intelligence.

Watch this space for more upcoming demos.

Thanks.

Stay Safe!

Rajiv Singh