Part 1: SAP S/4HANA migration cockpit – Migrating data using staging tables and methods for populating the staging tables
One frequently asked question (FAQ) regarding the SAP S/4HANA migration cockpit’s migration approach “Transfer Data Using Staging Tables” is: how to load data into the staging tables?
This blog series will provide a few options for populating these staging tables with data.
This first blog post will give an overview of the SAP S/4HANA migration cockpit, focusing on the migration approach “Transfer Data Using Staging Tables”. The next blog posts will provide step-by-step examples on how to load data into the staging tables using SAP Data Services, then SAP HANA smart data integration (SDI) and finally SAP HANA Studio – Data from File option.
First, let’s have a short introduction and overview of the functionality, features, and key benefits of the S/4HANA migration cockpit.
The SAP S/4HANA migration cockpit is a tool designed for customers who have just installed SAP S/4HANA (new implementation scenarios) and want to transfer their business data from SAP or non-SAP software systems. The SAP S/4HANA migration cockpit has become an essential tool for SAP S/4HANA data migration, supporting customers during the transition to SAP S/4HANA. It is part of both SAP S/4HANA and SAP S/4HANA Cloud and can be launched using the “Migrate Your Data” app in the Fiori Launchpad (Manage Your Solution Launchpad) or using transaction LTMC. With the migration cockpit, you can migrate your master data and transactional data to SAP S/4HANA. It uses migration objects to identify and transfer the relevant data and, facilitates the migration process by providing predefined migration content and mapping. The SAP S/4HANA migration cockpit is SAP’s recommended approach for the migration of business data to SAP S/4HANA (on-premise) and SAP S/4HANA Cloud.
The most important functions and features of the migration cockpit are outlined below, as well as its key benefits.
Functions and Features:
- Preconfigured data migration. No developer skills required.
- Step-by-step guidance through the migration process.
- Preconfigured migration objects and rules.
- Automated cross-object value mapping.
- Migration programs are automatically generated.
- Migration object modeler can be used for custom requirements.
- Preconfigured content and mapping for each migration object, for example Bank, Customer, Cost center, Material.
- Predefined file templates and staging tables for each migration object.
- Automated mapping between template and target structure.
- Migration programs are automatically generated – no programming required by the customer.
- Available for SAP S/4HANA and SAP S/4HANA Cloud, included in these licenses.
- Available for Cloud and for On-Premise.
Currently, the SAP S4/HANA migration cockpit uses the following migration approaches:
a) Transfer data using files (xml templates)
b) Transfer data using staging tables
c) Transfer data directly from SAP System (new with SAP S/4HANA 1909)
For the migration approach “Transfer Data Using Staging Tables”, the SAP S/4HANA migration cockpit can automatically create staging tables for each migration object (for example bank) that is relevant for your migration project. Staging tables are database tables and therefore provide greater flexibility than files regarding managing data (for example sorting or searching data). Furthermore, such kind of tables are a more efficient way of transferring large volumes of data as they can handle a greater volume of data without the need of splitting large tables into several portions. If you need to transfer a lot of data to SAP S/4HANA in an automated way, then this approach is the recommended migration approach.
The following section provides information about the motivation, benefits, and technology for this approach:
Below are the motivations, benefits and technology regarding the staging tables approach:
- Amount of records and size of file is limited for XML files
- Large tables need to be split by creating several spreadsheets
- Filling of XML templates with multiple tabs can lead to inconsistencies
- Secondary database connection must be available
- Staging tables: created natively in the schema of HANA database
- Separate staging table for each source structure of a migration object
- Safer, faster, and easier
- Staging tables will be created natively in the schema of the SAP HANA database depending on the selected database connection
- For each source structure of a migration object (for example Customer), a separate staging table will be generated.
- Staging tables must be filled by customer using extraction tools (ETL) from SAP or from a third party.
System Setup for On-Premise
System Setup for Cloud
General Procedure for Transferring Data to SAP S/4HANA Using Staging Tables
Prerequisites, Required Roles, Installation/Setup Activities
Before starting with the creation of a migration project, we recommend you review the following documentation which contains information about required roles, SAP Best Practices Explorer, installation activities (On-Premise only) and, set-up instructions (Cloud only):
- SAP Best Practices Explorer (On-Premise/Cloud) – Data Migration to SAP S/4HANA from Staging (2Q2)
- 2733253 – FAQ for SAP S/4HANA migration cockpit – Transfer option: Transfer data from staging tables
- Installation Guide for S/4HANA (On-Premise only) available under: https://help.sap.com/viewer/p/SAP_S4HANA_ON-PREMISE → <Filter-Version> Product Documentation → Installation Guide → 7. Installation Follow-Up Activities
- Setting Up Data Migration to SAP S/4HANA from Staging (2Q2)
- Test script Data Migration to SAP S/4HANA from Staging (2Q2) (Information about Technical and business roles, manual configuration, preliminary steps), available under: https://rapid.sap.com/bp/scopeitems/2Q2 → Test script
- SAP Help Portal – SAP S/4HANA Cloud
General Process for migrating data
The general process for migrating data to SAP S/4HANA using staging tables is as follows:
1. Create a migration project to transfer data using staging tables. You can do this using transaction LTMC for the SAP S/4HANA migration cockpit for On-Premise Edition or using “Migrate your Data” App for Cloud Editions / Fiori Launchpad.
2. When you create a migration project, you specify a database connection to the source system database.
- On-Premise: Only those connections are displayed here that are whitelisted. These connections must be maintained in table DMC_C_WL_DBCO_OP.
- Cloud: For cloud deployments, this database connection is automatically done through the so-called communication scenario. For more information about the communication scenario, and how to set-up this connection, refer to the following document: Setting Up Data Migration to SAP S/4HANA from Staging (2Q2)
3. Open the migration objects (for example, Bank, Customer, Cost Center and so on) that are relevant for your project.
You can do this by double-clicking the name of the migration object that you want to add to your project.
When you open a migration object, staging tables are automatically created for the migration object. For each source structure of a migration object, a separate staging table will be generated natively in the SAP HANA database schema (for example in the migration project sample below for the migration object G/L account, four different staging tables are created which correspond to the four structures for G/L account: S_GENERAL, S_COMPANY, S_SKA1_TEXT, S_KEYWORDS, that are currently available for this migration object).
Figure: “Migration Object Details” Screen for migration object G/L account
You can view the table definition in the SAP HANA Studio. On the Systems tab, expand the folder Catalog and locate the relevant schema. Expand the schema and expand the folder Tables. Select the staging table and choose “Open Definition” from the context menu.
Fields that are marked as NOT NULL or with an * (Asterix) in the data definition must contain a value. This means they need to be populated with data taking into consideration the leading zeros, default values, and correctness of the values of some data types (for example, DATE, DECIMAL, TIME and so on).
You can find more information about default values and leading zeros in SAP KBA 2733253 and the SAP Help Portal: SAP Help Portal: SAP HANA SQL and System Views Reference for SAP HANA Platform → SQL Reference → Data Types.
If the available migration objects do not meet your requirements, you can enhance or modify migration objects using the Migration Object Modeler. The SAP S/4HANA migration object modeler is part of the SAP S/4HANA migration cockpit, and it is designed to integrate custom objects and enhancements. Note that the Migration Object Modeler is only available for the on-premise and single tenant editions of SAP S/4HANA. You can access the SAP S/4HANA migration object modeler by using transaction LTMOM.
For more information about the SAP S/4HANA migration object modeler, refer to the following resources:
- SAP S/4HANA Migration Object Modeler – SAP Help Portal
- User Guide: SAP S/4HANA Migration Cockpit – Creating a New Migration Object with the Migration Object Modeler
- How-To Guide: SAP S/4HANA Migration Object Modeler
4. Add the data that is relevant for the migration object to the staging tables
You can fill the staging tables with data either using the SAP HANA Studio and SQL Insert statement or the option Import – Data from File. Also, you can fill these tables using SAP tools such as SAP Data Services, SAP HANA smart data integration or other third-party tools.
The next blog posts of these blog series will show you step by step three methods for populating the staging tables using SDI, SAP Data Services, and SAP HANA Studio.
5. Once you have added all the relevant data to the staging tables for the migration object, you can transfer the data for that migration object to SAP S/4HANA or SAP S/4HANA Cloud.
Note: You can improve the performance of the data transfer by increasing the number of data transfer jobs. The field Max. Data Transfer Jobs can be found on the Migration Object Details screen.This function is available as of 1809 release and it is advisable to set this parameter at object definition. For more information see the documentation for the SAP S/4HANA migration cockpit on help.sap.com
You can start the data transfer by clicking “Start Transfer” button available on the “Migration Object Details” screen.
Once you have started the data transfer, the staging tables of the migration objects you selected get locked. The is done using freeze triggers, which prevent changes from being made to the data in the staging tables during the data transfer.
In the SAP HANA database, you can see three triggers for each staging table:
If freeze triggers are set, you will see the message following message on the Migration Object screen in the Migration Cockpit:
“Staging tables of the migration object locked; cannot change data records”
If you are using the SQL Insert Statement in SAP HANA Studio for filling the staging tables and you try to insert data while the freeze triggers are set, you will get an error message such as:
Could not execute ‘insert into “DBUSER”.”/1LT/DSXXX000312″ values(‘CUST14′,’BP02′,”)’ in 5 ms 828 µs .
SAP DBTech JDBC: : user-defined error: “DBUSER”.”/1LT/DSXXX000312FRI”: line 2 col 142 (at pos 279):  (range 3) user-defined error exception: no table change allowed
There are two options for deleting the freeze triggers (that is, to unlock the staging tables):
- Finish the transfer: After the data transfer is finished, the freeze triggers will be dropped automatically.
- Choose the “Restart Transfer” button on the Migration Object Details screen.
When a staging table contains data, you can view the data records on the Staging Table Details screen. However, you cannot edit or add data records here.
If you want to delete one or more data records, you can do that directly from the Staging Table Details screen by selecting the data records you would like to delete and then press the button “Delete Selected Records”.
You can also Delete All Records from that particular staging table by choosing the button “Delete All Records” (regardless of the status: Unprocessed, processed, etc.).
You have then to follow the guided procedure provided within the SAP S/4HANA migration cockpit. Starting with “Validate Data” then “Convert Values” and “Simulate Import” and finally “Execute Import”.
6. When the activity “Transfer Data” is complete, the data has been migrated to SAP S/4HANA.
7. You repeat this process for each migration object that is relevant for your project.
Note: It is not possible to combine staging tables and file approach in one project.
For more information about migrating data to SAP S/4HANA using staging tables, refer to the documentation on the SAP Help Portal:For more information on Transferring Data to SAP S/4HANA Using Staging Tables you can check the following SAP Help Portal Links:
- For On-Premise
- For Cloud
Coming back to the question stated at the beginning of this blog:
How do we fill the staging tables of the SAP S/4HANA migration cockpit?
The staging tables can be populated either manually using ABAP or with the SAP HANA Studio or by using ETL tools from a third party or from SAP (for example SAP Data Services, SAP HANA smart data integration (SDI)). In the image below, you can see some possible solutions to fill the staging tables.
Note: It is important also to consider the following major recent Innovation for the staging tables approach of the SAP S/4HANA migration cockpit, available from 1809 FPS0:
- Mapping table for staging table names – From 1809 FPS0 there is now a mapping table ‘/1LT/DS_MAPPING‘, which is provided in the same schema where the staging tables are generated. This table stores mapping information about the migration object, the source structure and the staging table name. You can use this table to determine the staging table names after you copy a project from a quality system to a production system and then use these names in your scripts or applications that populate the staging tables with data.
In this blog series, we will focus only on the following ETL tools from SAP to load data into the staging tables of the SAP S/4HANA migration cockpit:
The second blog post will focus on how to load data to the staging tables using SAP Data Services.
The third blog post will focus on SAP HANA Smart Data Integration (SDI)
The forth blog post will focus on SAP HANA Studio – Data from File option
SAP S/4HANA Migration Cockpit (On-Premise & Cloud) References, Blog Posts and Useful Links
SAP help portal:
- Data Migration – SAP S/4HANA Migration Cockpit (Landing page S/4 HANA migration cockpit)
- SAP S/4 HANA On-premise
- SAP S/4HANA Migration Cockpit migrations objects for the On-premise – Enterprise – Available Migration Objects
- Transferring Data to SAP S/4HANA Using Staging Tables
- Data Migration – SAP S/4HANA Cloud (Landing page S/4 HANA migration cockpit (Cloud))
- SAP S/4 HANA Cloud
- Migration Objects for SAP S/4HANA Cloud – Available Migration Objects
- Transferring Data to SAP S/4HANA Using Staging Tables
Trainings & Videos:
- Intro Video: SAP S/4HANA Migration Cockpit – Migrate your Data
- Video: SAP HANA Academy – Migration Cockpit
- Video: SAP HANA Academy – Migration Object Modeler
- Open SAP course Data Migration to SAP S/4HANA
- Starter Blog Post for SAP S/4HANA Migration Cockpit
- SAP S/4HANA Migration Cockpit – tips & tricks
- SAP S/4HANA migration cockpit – Creating and using synonyms for renaming staging tables
Relevant SAP Notes/KBAs:
- 2537549 – Collective SAP Note and FAQ for SAP S/4HANA Migration Cockpit (On-Premise)
- 2538700 – Collective SAP Note and FAQ for SAP S/4HANA Migration Cockpit (Cloud)
- 2733253 – FAQ for SAP S/4HANA migration cockpit – Transfer option: Transfer data from staging tables
- 2608495 – SAP S/4HANA Migration Cockpit: Errors using staging functionality w/ pre-delivered data migration objects in on-premise release 1709 FPS1
- 2596411 – SLT / NZDT / S4HANA Migration Cockpit (DMIS2011 SP11-SP15; DMIS2018; S/4HANA 1610, 1709 & 1809) – Note Analyzer
- 2587257 – S/4HANA 1709 FPS01 – Migration – Corrections for Migration Cockpit – Staging Scenario
- 2596400 – Migration objects available in the Migration Cockpit
Central SAP Notes
- 2817159 – Migration Cockpit & SLT on S/4 HANA On-Premise Edition 1909
- 2594002 – Migration Cockpit & SLT on S/4HANA On-Premise Edition 1809
- 2475034 – Migration Cockpit & SLT on S/4HANA On-Premise Edition 1709
- 2376533 – Migration Cockpit & SLT on S/4HANA On-Premise Edition 1610
- SAP S/4HANA Migration Cockpit – Development Newsletter
- SAP S/4HANA Data Migration Content Development Newsletter
Other Useful Links:
- SAP S/4HANA Migration Cockpit – Migrate your Data to SAP S/4HANA
- Migrate Your Data to SAP S/4HANA® Quickly, Safely and Cost-Effectively
- SAP Best Practices Explorer
- Data Migration to SAP S/4HANA from File (BH5)
- Data Migration to SAP S/4HANA from Staging (2Q2)
- SAP Activate Roadmap Viewer
- Mapping Your Journey to SAP S/4HANA® A Practical Guide for Senior IT Leadership
— Transitioning to SAP S/4HANA Links and Information:
Very informative blog. Thanks for sharing.
thanks for this post, very helpful.
a question came to me: when you do this setup and design in DEV environment, once project exported to QA and/or PROD will it use the same DB connection and Staging table?
in the environment I;m using we have DEV, QA, Pre-PROD and PROD environment.
I´m glad this post is helpful.
With regards to the project export nach QA, PROD... and the DB connection: You´ll have to specify the DB connection when you export and import the project. This is because you must create a new project first in the QA or PROD system before you can import content and therefore, you have to specify the DB connection for this new project .
thanks again for your help, very appreciated.
I understand now the logic behind.
a question I’m facing now is that to implement all this the Basis team has no knowledge on this technology, and they asking on how to do the setup for the DB on the Hana system that will hold the Staging tables.
I found many OSS notes about this, but the way on how to do the setup for the DB itself I was not able to find. (2733253 – FAQ for SAP S/4HANA migration cockpit – Transfer option: Transfer data from staging tables)
the sentence that is confusing me is this in bold:
“You have created an SAP HANA database connection between the SAP S/4HANA system and the staging system (a remote system that uses the SAP HANA database).”
please let me know if any OSS NOTE explain this in details and how can we do this setup.
thanks again for your input!
For the benefit of all SAP Community members having similar questions, please post your question here: https://answers.sap.com/questions/ask.html That way, your question is addressed with all related experts within SAP Community and your answered question can be helpful for others in future.
Have a look at our tutorial for asking and answering questions in SAP Community: https://developers.sap.com/tutorials/community-qa.html
Mynyna (SAP Community moderator)
KBA 2733253 has been recently updated and there is now more information about prerequisites, authorizations needed for DB users and other useful information. For example:
You can also find more information about how to add a SAP HANA System (using the SAP HANA Studio) in the SAP HANA Developer Guide, link below.
SAP HANA Developer Guide - Tutorial: Add an SAP HANA System
My query is is there any harm in deleting data in staging tables IN SLT,in my case data pulled from non sap to S4HANA via SLT.
I need delete staging tables in SLT, please suggest best approach, thanks
The SAP Landscape Transformation Replication Server (SLT) is a different and separate SAP product/solution and, it has different functionality than the SAP S/4HANA migration cockpit (topic of this post). SLT might use also staging tables but these might be handled in a different way. You should therefore look for advice of an SLT expert. For this, you could post your question in the SAP community under “Questions” so that SLT experts can clarify your question:
Below you can find more information about SAP Landscape Transformation Replication Server:
I have a question regarding the description of field on uploading template, do you know where's the comment in red box come from(please see attached picture), I also would like to write some comment, but I have no idea how to do it, if you check the original source structure, you can see that the description is very simple, please see green box on the picture.
Could you please give me some suggestion, thank you.
@iliana.olvera, Hi Iliana, could you please give me some hint regarding this issue?
Besides, I have one more question, I need to create a completely new migration object through File/staging, in this object, need to to use a control parameter to define whether system will use internal number range or external number, but I have no idea how to define the control parameter for customized object, could you please also help me, thank you.