Technical Articles
Part 4: SAP S/4HANA migration cockpit – Using SAP HANA Studio to load data to the staging tables
This is the final blog post of a blog series I wrote together with Markus Andres and Alessandro Sabidussi
The first blogs post gave an overview of the SAP S/4HANA migration cockpit, focusing on the migration approach “Transfer Data Using Staging Tables”.
- Part 1: SAP S/4HANA migration cockpit – Migrating data using staging tables and methods for populating the staging tables
- Part 1: Migrate your Data – Migration Cockpit (from SAP S/4HANA 2020, SAP S/4HANA Cloud 2008), Migrate data using staging tables and methods for populating the staging tables with data
The second blog post by Markus covered how to use Data Services to populate the staging tables.
The third blog post by Alessandro presented a second option using HANA Smart Data Integration (SDI), which is an embedded functionality in SAP HANA.
In this final post, we will provide general information and a link to a detailed guide about how to populate the SAP S/4HANA Migration Cockpit staging tables with data by using the SAP HANA Studio.
The SAP HANA Studio gives users the possibility to import data from a local file (.csv, .xls, or .xlsx) into an SAP HANA database by using Data from Local File option.
For the SAP S/4HANA migration cockpit’s approach “Transfer Data Using Staging Tables”, a table schema is created when you select and open a migration object within your migration project. As mentioned in the first blog: “SAP S/4HANA migration cockpit – Migrating data using staging tables and methods for populating the staging tables“, for each source structure of a migration object a separate staging table is generated natively in the SAP HANA database schema.
Structures for Migration Object G/L account and the generated, corresponding staging tables:
FI-G/L Account, Migrate Data from Staging Tables – SAP S/4HANA Cloud 2008 and higher, SAP S/4HANA 2020 and higher
G/L Account, Transfer Data from Staging Tables – SAP S/4HANA Cloud 2005 and lower, SAP S/4HANA (transaction LTMC)
The table schema or staging tables corresponding to the source structures of the migration objects you selected in your migration project using the SAP S/4HANA migration cockpit (staging tables approach) can be seen and accessed in the SAP HANA studio. You find such staging tables under Systems -> Catalog -> <DatabaseUser> ->Tables
Table Definition in SAP HANA Studio – SAP S/4HANA Migration Cockpit, staging tables: G/L Account, General Data
Once you have created your migration project and selected your migration objects, you will then be able to upload data into these tables using the Data from Local File option in the SAP HANA Studio.
Prerequisites:
- In the SAP S/4HANA migration cockpit You have created a migration project that uses the migration approach Migrate Data Using Staging Tables.
- You have selected the relevant migration objects for your migration project.
- 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).
- You have a flat file (.csv, .xls, or .xlsx) with data to upload.
- You have added a System to SAP HANA studio to connect to the SAP HANA repository containing the staging tables.
Note: For more details about prerequisites and other relevant information about Migrate Data Using Staging Tables see: KBA 2733253 – FAQ for SAP S/4HANA migration cockpit – Transfer option: Transfer data from staging tables
Procedure:
For detailed information about how to fill the staging tables using the SAP HANA studio – Data from Local File option, see section 2.4. in the following SAP User Guide:
This guide shows how to load data from flat files to the staging tables by using the Data from Local File option in the SAP HANA Studio.
You find more information about the SAP HANA Studio here:
Tips / Useful Information for creating the .csv file for uploading data to the staging tables
You can use the Export function available in the SAP HANA studio to generate an XML file (table.xml) of the staging tables´ definition. Then, you can use the Excel function “Transpose” to create the .csv file for uploading data into the staging tables using the Data from Local File option in the SAP HANA Studio.
For more information about the “Export” function of the SAP HANA Studio please see:
- SAP HANA Academy – Exporting to CSV
- SAP HANA Administration Guide for SAP HANA Platform – Export Tables and Other Catalog Objects
STEPS
1.- In the SAP HANA Studio: Export the table definition of the staging table(s) you would like to populate with data using the “Export” function. This will create a file (s) called table.xml.
To export the table/object definition, you select the Table(s) for which you would like to export the table definition, right-click on the staging table(s) and then choose “Export”.
Note: You can also type in the (staging) table name you want under “Enter search to find a catalog object” and then click on “Add”.
2.- In Excel: open the table.xml file which contains the fields for the staging table you require.
3.- Copy the technical name fields (Column called “Name3”, usually) from the table.xml file (opened in Excel) until field /1LT/PROCESSED
4.- Create a new file in Excel
5.- Paste the copied fields (from file: table.xml) to the new Excel document using the “Transpose (T)” function.
6.- Save the new file in .csv format
You can then populate this .csv file with the data you require and upload the file to the staging tables using the SAP HANA Studio – Data from File option.
With this, we conclude these blog series.