Welcome to my blog and here I will be sharing full roadmap of SAP data migration using ‘Migrate your Data – Migration Cockpit’ staging table approach & SAP BODS as ETL Platform to load those staging tables.
As you know after new releases, we need to use Migrate Your Data Fiori App instead of LTMC In SAP S/4HANA 2020 and later versions.
So, what is the process now & how it is different from LTMC ? How can I load staging tables with ETL method so that I don’t have to fill those templates manually? How can we export projects from lower to higher environments?
Let’s Go !!
To start the process, first we need to create a project in Migrate Your Data Fiori App
1. Create Project in Migration Cockpit
- GO to Tcode – /n/ui2/flp or /o/ui2/flp or /ui2/flp
- Then you will land in separate browser link which will open SAP Fiori UI page. Now type ‘Migrate Your Data’ in search box present at top right corner & you will see your app appearing as shown below
- Once you click on it, it takes you to Migrate your Data app. Then click on create option & choose ‘Migrate Data Using Staging Tables’
- Give project name & choose database connection.
N.B: In Database Connection, we have 2 options
Local SAP S/4HANA database
- System will generate Staging Tables in the internal schema of the SAP S/4HANA system.
- Use this option if you only want to use XML template files to fill the Staging Tables with data
Remote SAP HANA database
- System will generate Staging Tables in a remote SAP HANA DB Schema (Here DS_HANA_DS5)
- Prerequisite: need to have valid database connection configured
- Select this option if you want to fill the staging table with data by using SAP BODS.
- Here I am choosing Remote SAP HANA database, Click on ‘Step 2’ to proceed further & provide Development Package details. After that, click ‘Step3′
N.B : You might think, why we need this development package and who will create this?
With SAP S/4HANA 2021 release, SAP has bought Transport Concept into Migration Tools. Transport Functionality is currently available for Migrate Data Using Staging Tables only for the On-Premises model. This functionality is used to transport project between systems. We will discuss this further in 3rd point ”Export Projects From Lower To Higher Environments”
You can create this package using SE80 or you can take help from ABAP developer to create this on your behalf.
- Once done, it’s time to add data object in project. For example, I am adding CUSTOMER data object.
- Type data object name in search box. Then select checkbox for desired data object among search results & click right arrow to move you object from left to right.
- Then click review
- Now a popup will come if there are any predecessor data object present for your object (it depends on your data object). If you want to add then click on ‘add’ option otherwise you can click ‘do not add’ if you want to proceed only with your selected data object.
- After that click on ‘Create Project’ & your project will be created successfully
- You can also see your staging tables created automatically once you click on your project ‘ZTEST’
.
⇒ Now project is created and I want to add data in these staging tables using BODS. How to do that?
Lets do it !
2. Load Data In Staging Tables Using BODS
- First create a Datastore for that Remote SAP HANA database which was selected while creating the project
- When a project is created in Migration cockpit, all the related staging tables for all structures are created automatically & stored in Remote/Local database. It depends which connection you are choosing, as mentioned earlier. So once this datastore is configured we can import (Import by Name) those staging tables in BODS & use them as target to load the data. Not going much further on transformation part in BODS as it will be project specific.
Lets assume this project is created in DEV and now we need to move it in QA. So the question is :
→ How we can move the project from one environment to another?
→ What about the staging tables? In DEV staging table name was /1LT/DSDS5000075 (in aove screenshot), will it remain same in QA? The answer is NO & here I will introduce you with the concept of Synonym creation
3. Export Projects From Lower To Higher Environments
Here I will talk about two ways to move your project from one environment to another & one approach for the scenario when we want to use project in different client of same environment.
Lets start with : Move your project from one environment to another
a. SAP S4/HANA 2021 Transport Functionality
The Transport Functionality in the migration cockpit is designed to transport content from one system to another & it is available with SAP S4/HANA 2021 onwards for the approach ‘Migrate data using staging tables’. Steps for this are mentioned below
- As I mentioned earlier, while creating the project, it should be created under a certain custom package to avail this functionality (as shown below). How to create this package?
- Go to SE80 > From dropdown menu, select package > Give package name in box below > click on display or create object
- Once package is created, assign it to a transport request.
- Now open Migration Object Modeler (Tcode : LTMOM). Choose your project and add it with same transport request
- Once project is added to TR then proceed with standard SAP transport which will export the project to desired environment.
- After transport, project will be visible in new environment & you will be asked to give new database connections details when opening the project for first time. It will be the DB in which you would like to recreate /1LT/* staging tables in new environment.
You can refer SAP S/4HANA Migration Cockpit Transport concept | SAP Blogs by Ruthvik Chowdary to get more clarity.
b. Export / Import Project (Migrate Data Using Staging Tables)
This is bit different than transport functionality & here you need to export & import your Project manually.
- Go to SE38 > Give Program name ‘/LTB/MC_PROJ_Q2P’ > Execute
- Choose ‘Export Project’ > Give MT_ID / Mass Transfer ID > Execute
- A zip file will be downloaded for your project
Now to import the project in new environment
- Go to SE38 in new environment > Give Program name ‘/LTB/MC_PROJ_Q2P’ > Execute
- Choose ‘Import Project’ > Give Database name where you want to create staging tables > Execute > Choose file which was downloaded from earlier environment
c. Move project in different client of same environment
Lets assume we are in DEV/200 & I need to move my project in DEV/400 due to some reasons.
Keep in mind, transport functionality in migration cockpit does not support transports between clients in the same system. For that follow below steps:
♦ Logon to the client, where you want to use the project
♦ Go to the Migration Object Modeler (transaction LTMOM). If you want to use the same mapping values, please download them beforehand. (You can use the download mapping functionality in the Fiori App Migrate Data Migration Cockpit)
♦ Select your project in the Object Browser
♦ Choose Go to > Use Project in Different Client
The project will not be copied to the other client, rather it will be moved to the other client and therefore not available anymore in the old client.
Congratulations !! Now you have successfully transported your project.
But wait !! If you remember, in BODS job we used /1LT/DSDS5000075 as target but we should always keep in mind, once we are transporting the project to different environment these staging tables are recreated in the new environment with new name. For example, /1LT/DSDS5000075 will be replaced with /1LT/DSDS5000666 for same structure.
So, the previously set target in BODS job is invalid now.
To solve this problem, we need to use synonyms for these staging tables in BODS job.
4. Synonym Concept in Data Migration for renaming staging tables
Synonym are basically alias for tables, so it’s just another name you can use to refer to the table in SQL statements. Every synonym has a name that is unique within the entire database system and differs from all the other table names. When transporting SAP S/4HANA migration cockpit projects from the lower to higher environment, the generated names of the staging tables will be different in the different environments. Consequently, you have to adjust the data flows in your ETL tool and update the names of the staging tables. You can avoid these adjustments if you define synonyms for the staging tables and use the synonyms in the data flows
→ There are two ways which can be used to create Synonyms.
a. Create Synonym using script
The staging table names are stored in table /1LT/DS_MAPPING in the same schema of the staging tables. Users can use table /1LT/DS_MAPPING to recreate the synonyms. This means that even though the names of the staging tables will change, the target used for BODS will not change as synonym name will remain same across all systems (DEV,QA,PROD).
Below store procedure allows to create synonyms for all staging tables present in table /1LT/DS_MAPPING under same mass transfer id. Now synonym name can differ according to project requirement (few examples are added in script). I am using Synonym naming convention as : S_<Project_Name>__<object>__<structure>
♦ Follow below steps to create procedure:
- Open SAP HANA Studio & login > Right click on connection > Open SQL Console
- Paste below script in SQL console and execute it.
/*************************************************************************************
Created By : Soyel Rana
Creation Date : 04/03/2023
Procedure_name : create_staging_synonyms
Description : This procedure generates synonyms for data migration staging tables created in Data Migration Cockpit in one go.
*************************************************************************************/
CREATE PROCEDURE create_staging_synonyms
(
IN SYS_ID NVARCHAR(8),
IN MT_ID NVARCHAR(3),
IN PROJ NVARCHAR(24)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
DECLARE CNT INTEGER;
DECLARE SYNONYM_NAME NVARCHAR(60);
DECLARE STAGING_TAB NVARCHAR(30);
DECLARE CURSOR c_cursor ( SYSID NVARCHAR(8), MTID NVARCHAR(3)) FOR
select *
from "/1LT/DS_MAPPING"
where SYS_ID = :SYSID
and MT_ID = :MTID;
FOR wa AS c_cursor(:SYS_ID, :MT_ID)
DO
STAGING_TAB = :wa.STAGING_TAB;
-- synonym name with Project Name & without SID
SYNONYM_NAME = 'S_' || :PROJ || '_' || SUBSTRING(:wa.COBJ_IDENT, 1, length(:wa.COBJ_IDENT)-4) || '_' || :wa.STRUCT_IDENT;
-- synonym name with SID
-- SYNONYM_NAME = 'S_' || :wa.SYS_ID || '__' || :wa.COBJ_IDENT || '__' || :wa.STRUCT_IDENT;
-- synonym name without SID
-- SYNONYM_NAME = 'S_' | :wa.COBJ_IDENT || '__' || :wa.STRUCT_IDENT;
-- drop synonym if already exists with another staging table
SELECT count(*) into CNT FROM SYNONYMS WHERE SYNONYM_NAME = :SYNONYM_NAME;
if CNT > 0 then
SELECT OBJECT_NAME into STAGING_TAB FROM SYNONYMS WHERE SYNONYM_NAME = :SYNONYM_NAME;
if STAGING_TAB <> :wa.STAGING_TAB then
EXEC 'DROP SYNONYM "' || :SYNONYM_NAME || '"';
STAGING_TAB = :wa.STAGING_TAB;
EXEC 'CREATE SYNONYM "' || SYNONYM_NAME || '" for "' || STAGING_TAB || '"';
end if;
else
EXEC 'CREATE SYNONYM "' || SYNONYM_NAME || '" for "' || STAGING_TAB || '"';
end if;
END FOR;
END;- After successful execution you will be able to see this procedure created in system
- Now call procedure with System , Mass transfer ID, Project as input in the environment where you are supposed to run BODS job
CALL create_staging_synonyms(SYS_ID => 'DS5', MT_ID => 'M0R', PROJ => 'R1');- After execution synonyms will be created in system
- You can display all synonyms for your system and project like this
SELECT * FROM SYNONYMS WHERE SYNONYM_NAME LIKE 'S_%<Project>%';
b. Create Synonym using SAP GUI
If you are using Local SAP S/4HANA database to create staging tables then you can also create synonyms directly from SAP GUI
- Go to SE37 > Execute function module ‘DB_EXECUTE_SQL’
- write below statement to create synonym and execute
CREATE SYNONYM "S_<Project_Name>_<Object>_<Structure>" FOR "/1LT/DSDS5000000"
- After successful run you will get below screen and synonym will be created
You can import these synonyms directly in BODS and use them as target instead of actual /1LT/* staging tables. As a result BODS dataflow will remain same across all environments.
→ In case you have created a Synonym by mistake & want to delete it, you can use
DROP SYNONYM [ IF EXISTS ] [ schema. ] synonym_name
If you have anything to add on top of it, kindly share feedback or thoughts in comments. Your contribution will be highly appreciated.
For further knowledge in SAP Data Services & Migration Cockpit you can check :
