SAP S/4HANA migration cockpit – Creating and using synonyms for renaming staging tables

In this blog, I will briefly explain the use of synonyms for staging tables and will also provide sample code on how to create synonyms for renaming the staging tables of the SAP S/4HANA migration cockpit. These synonyms can be later used in other ETL Tools such as SAP Data Services.

As mentioned in the blog from Markus Andres: “Using SAP Data Services to load data to the staging tables“, you can use SAP Data Services to populate the migration cockpit´s staging tables. An overview of the SAP S/4HANA migration cockpit and further details about the migration approach “Transfer Data Using Staging Tables” are explained in Blog: Part 1: SAP S/4HANA migration cockpit – Migrating data using staging tables and methods for populating the staging tables.

When moving (i.e. import/exporting) the SAP S/4HANA migration cockpit projects from the development environment to a quality 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. Synonyms are references or aliases for a table or other database objects, so it’s just another name you can use to refer to the table in SQL statements. For more details about synonyms you can refer to:

• SAP HANA Developer Guide – Synonyms
• SAP HANA Developer Guide – Create a Synonym
• SAP HANA Academy – Web IDE for HANA: Synonyms – Create Synonym [2.0 SPS 00]

For the SAP S/4 HANA migration cockpit, synonyms can be used to obtain identifying names from the system, project, object and structure instead of having consecutive numbers in the table name. Synonyms have the format S_<sid>__<object>__<structure>, for example S_ABC__Z_CUSTOMER2_US8__S_CUST_GEN.

The names of the staging tables are stored in mapping table /1LT/DS_MAPPING in the 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 destinations used for data export stored procedures or scripts in the extraction tools (for example SAP Data Services) will not change.

The example stored procedure below shows how to create the synonyms for the staging tables. For example, the name of the staging table for source structure S_CUST_SALES_DATA in the staging schema of the development system might be /1LT/DSDEV10001234 and /1LT/DSQAS20004321 in the staging schema of the quality system. In the case below, the SID of the S/4HANA system is used in the synonym. However, you can remove this SID (i.e. <sid> & ABC, sample name which represents this SID) to make the stored procedure system independent and get static synonym names across the landscape (simply comment and uncomment the respective line in the script).

……………………………………………………………………………………………….

Stored procedure for all staging tables of a migration project

Important Notes:

• The procedure and synonyms are created in the current schema using SQL console in SAP HANA Studio.
• Input of the stored procedure is the system id <sid> and the project id <mtid>. There is no output.
• A synonym will be created for each staging table in a migration project.
• If a synonym already exists but is assigned to another table name, it will be dropped and created with the current staging table name.
• The naming convention used for the synonym is: S_<sid>__<object>__<structure>, e.g. S_ABC__Z_CUSTOMER2_US8__S_CUST_GEN
• The scripts and stored procedures are examples that should be adjusted to your requirements.

1. Create procedure

--drop procedure create_staging_synonyms;

CREATE PROCEDURE create_staging_synonyms(

  IN SYS_ID        NVARCHAR(8),

  IN MT_ID         NVARCHAR(3))

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 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;

2. Call procedure for a system and project

In SQL console you can call the procedure as follows e.g. for system QKX and project id US8:

CALL create_staging_synonyms(SYS_ID => 'QKX', MT_ID => 'US8');

3. You can display all synonyms for your system and project like this

Replace <MTID> with the project id and <SID> with the system id:

SELECT * FROM SYNONYMS WHERE SYNONYM_NAME LIKE 'S_<SID>%<MTID>%' order by SYNONYM_NAME;

If you follow the steps above you will get synonyms with static names that are independent from the generated staging table names and you can use these names in your ETL tools.