Integration design and Data migration with staging and with staging tables

Hi buddies,

In this topic i will talk about integration design between SAP and None-SAP via staging tables. I would like to tell you first, this topic not only talk about SAP Process Orchestration but also tell you general concepts when you decide build integration landscape and consult to your customers.

What is staging table ?

Staging tables are tables defined in legacy or SAP system to storage:

• Configuration setting
• Mapping data
• Raw data
• Data conversion rules and transformation historical

In the general conceptual, staging tables can be created in same production databases or separate databases.

In picture below, messages from source systems come to legacy staging tables first then transformation tools or legacy system background jobs will execute data mapping logic and send to physical tables.

Why should we use staging tables ?

• From we start implement integration for organization there are also existed systems, production data is working well we don’t want to do any mistake with production data.
• Flexibility for data conversion, data conversion happened in middle-ware tools or transformation scripts so changing mapping logic can be adapted with less impact with live-system.
• Each receiver system had different data storage logic and objects concepts mapping logic and transform data needed to provide enough data for current system run properly.
• Improve legacy system performance, with a complex integration landscapes one legacy system can send / receive data from multiple source systems. In / Out data stored in staging tables and will be processing base on data priorities what was scheduled make sure data consistence.
• Distribution data and adaption. For outbound scenarios, one source system can distribute data to many system and databases. Staging tables stored data and make sure that result of data conversion are uniformity.

How do we design staging tables and transformation steps ?

Design staging tables for None-SAP system:

As i mentioned above, there are three kind of staging tables you need to consider when start design. Tables store one time setup information, tables store categories data and tables store transactions data.

Step 1: Design staging tables for categories or master data. What object’s information do we need to define ? We need to clear about data flow of interfaces.

Example, we need send sales order from our POS system to SAP CAR to create sales per receipt on CAR, so the first of all we need master data(Stores list, Cashier list, Material list, Sales Price list, etc…). Create tables and fields make sure all require information are defined.

Step 2: One time setup data, just refer to example above. In the sales per receipt in SAP CAR need to know Transaction Type, Item Type, Tender Type, Discount Type etc… those are one time setup categories.

Step 03: Transaction data staging tables.

Step 04: Transformation data log tables

Step 05: Make data collect program, here you can make stand alone application or make database SQL scripts to collect data from servers, database and input into transaction staging tables. Program / Script can run as scheduler immediately up to your requirements.

Step 06: Make data transformation program / scripts

Conclusion, there are ways to design integration landscape, depend on your resources and capabilities you can make staging tables or API or Database directly. In long term of integration design staging tables need to consider.

Thank you and let me know your opinion by comment article below

Cuong Dang