SAP Data Intelligence – Building a Pipeline in Modeler. Transfer Data from a File to Staging Tables on HANA DB Schema for Data Migration
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:
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.
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.
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.
- 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.
- Connection to SAP HANA DB Schema must be created in SAP Data Intelligence Connection Management.
- 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 ‘|’.
In our case I have used a small file with 2 records as shown below.
In the SAP Data Intelligence Modeler, click on the Create Button in the Graphs tab to create a new 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.
Click on Save to save the Graph.
Give a name, description and choose a category. Then click on OK to Save.
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.
Similarly, also in the SAP HANA Client Operator, the SAP HANA DB Schema connection details and table name should be maintained.
Save the graph and you receive a message that Validation is complete, and the graph is good to run.
Click on the Run button to execute the Pipeline. You can see in the Status tab, the Graph is running.
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.
Enter the DB Schema name and Staging Table Name:
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.
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.