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.
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:
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):
On-Premise:
Cloud:
The general process for migrating data to SAP S/4HANA using staging tables is as follows:
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:
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.
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.
Important Notes:
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: [12000]: user-defined error: "DBUSER"."/1LT/DSXXX000312FRI": line 2 col 142 (at pos 279): [12000] (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😞
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”.
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:
https://help.sap.com/viewer/29193bf0ebdd4583930b2176cb993268/latest/en-US/d5feccf64c9a41b2b95c908268...
https://help.sap.com/viewer/9961ea9091534d44a01ab44024b174d9/latest/en-US/1df405e3332d4de999af648845...
Coming back to the question stated at the beginning of this blog:
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:
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
-------------------------------------------------------------------------------------------------------------------------------------
- On-Premise:
- Cloud:
Blog posts
Central SAP Notes
-- Transitioning to SAP S/4HANA Links and Information:
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.
Key Benefits:
- 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.
Migration Approaches:
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:
Motivation
- 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
Technology
- 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
Benefits
- 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
On-Premise:
- 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
Cloud:
- 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 Objec...
- 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.
Important Notes:
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: [12000]: user-defined error: "DBUSER"."/1LT/DSXXX000312FRI": line 2 col 142 (at pos 279): [12000] (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
https://help.sap.com/viewer/29193bf0ebdd4583930b2176cb993268/latest/en-US/d5feccf64c9a41b2b95c908268...
- For Cloud
https://help.sap.com/viewer/9961ea9091534d44a01ab44024b174d9/latest/en-US/1df405e3332d4de999af648845...
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:
- On-Premise:
- 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 Migrati...
- Transferring Data to SAP S/4HANA Using Staging Tables
- Cloud:
- 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
SAP Communities:
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
Blog posts
- 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
Newsletters:
- 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:
- SAP Managed Tags:
10 Comments
