Working with Staging Tables in S/4HANA Migration Cockpit (OnPremise)
In the first version of the S/4HANA Migration Cockpit, the Data Sources have always been excel spreadsheets.
see blog https://blogs.sap.com/2017/02/28/getting-started-with-the-s4hana-migration-cockpit-onpremise/ for an introduction to the S/4HANA Migration Cockpit
Now you can also use Database Tables as a source for your Migration Project. This are so called ‘Staging Tables’, so you extract the data from the source system into these staging tables and import the data from there with the S/4HANA Migration Cockpit.
The Advantages are:
- faster overall process (export/import), less clicks
- Performance
- Use Database Tools to Extract and Transform
Attention: This Feature is available from minimum Release S/4HANA 1709 FPS1
how does it work?
First we need to create a Database Connection. This can be done by /NDBCO or /NDBACOCKPIT
Enter the Connection Parameters to the Staging Database, the User is also the Owner of the used DB-Schema.
The Staging tables can exist in a remote database or in the target S/4HANA database (but in a separate Schema)
Example:
Now we can create a new Migration Project in Transaction /NLTMC:
select Staging Tables here and the Database Connection.
when opening a Migration Object, in this case ‘Customer’, the Database Tables are created automatically based on the import structure description of the Migration Object
you can also check these structures (or even enhance them) with the Migration Object Builder, check https://blogs.sap.com/2017/06/20/discover-the-s4hana-migration-cockpit-migration-object-modeler-onpremise-nltmom/
now we can fill the tables with any database tool.
show structure with SE11:
show structure with hana studio:
if you select one structure you can switch the processing status and verify the records:
here you can see the records:
Activities:
Start Transfer: all the steps including mapping are performance the same way this works for the excel import.
Delete Records: you can delete the import records from the migration cockpit
Restart Transfer: resets the migration status, so you can delete or update the table records. this is the case if you see this message:
Start Synchronization: synchronize structure with migration object builder (in case of new fields have been added etc), no data will be lost.
link to sap help:
Specific Notes/Restrictions:
2587257 FPS01 S/4HANA 1709 FPS01 – Migration – Correction for Migration Cockpit – Staging Scenario
2608495 – SAP S/4HANA Migration Cockpit: Errors using staging functionality w/ pre-delivered data migration objects in on-premise release 1709 FPS1
The following known pre-delivered migration objects will run on errors and are not released to be used with transfer option Staging table migration projects:
- SIF_LSTAR_CREATE – Activity type
- SIF_PRCTR_MASTER – Profit center
- SIF_KOSTL_MASTER – Cost center
- SIF_WORK_CNTR – Work center
- SIF_MATERIAL – Material
- SIF_MATERIAL_EXTEND – Material – extend existing record by new org. levels
- SIF_INVENTORYBAL – Material inventory balance
It is currently planned to solve the issues on these migration objects with SAP S/4HANA 1709 FPS2
In case you want to export data from an existing R/3 system from abap, this could look like this:
i used rfc-function modules to dynamically define the structures based on the target S/4-system (where the migration cockpit is), and ADBC-SQL to insert the records, as the structures don’t exists on the source-systems data dictionary. of course you could transport these structures to the source systems data dictionary, but at the moment this would be a manual process.
REPORT Z_EXPORT_CUST_STAGING.
data: lv_filename type string.
data: gt_data like table of TAB512.
data: begin of wa_strucid,
COBJ_IDENT(20),
STRUCT_IDENT(20),
STAGING_TAB(30),
IN_USE(1),
end of wa_strucid.
data: gt_strucid like table of wa_strucid.
TYPE-POOLS : abap.
FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,
<dyn_wa>,
<dyn_field>.
DATA: dy_table TYPE REF TO data,
dy_line TYPE REF TO data,
xfc TYPE lvc_s_fcat,
ifc TYPE lvc_t_fcat.
FIELD-SYMBOLS: <dyn_db_table> TYPE STANDARD TABLE,
<dyn_db_wa>,
<dyn_db_field>.
DATA: dy_db_table TYPE REF TO data,
dy_db_line TYPE REF TO data.
field-symbols: <source> type any, <target> type any.
parameters: p_ident(20) default 'Z_CUSTOMER_007'.
parameters: p_dbcon like dbcon-CON_NAME.
parameters: p_rfcdes like rfcdes-rfcdest default 'S4V_MASTER'.
* KEY
data: ls_kna1 type kna1.
select-options: s_kunnr for ls_kna1-kunnr.
start-of-selection.
perform create_structures using 'S_CUST_GEN' 'KNA1'.
* append strukturen?
select * from KNA1 into TABLE <dyn_db_table>
where
KUNNR in s_kunnr and
loevm = ' '.
perform transfer_selected_data using 'KNA1'.
perform transfer using 'S_CUST_GEN'.
form transfer_selected_data using dbtabname.
LOOP AT <dyn_db_table> ASSIGNING <dyn_db_wa>.
clear <dyn_wa>.
move-corresponding <dyn_db_wa> to <dyn_wa>.
perform mapping_rules_after using dbtabname <dyn_db_wa> <dyn_wa>.
append <dyn_wa> to <dyn_table>.
endloop.
endform.
form create_structures using tabname dbtabname.
* DDIF_FIELDINFO_GET "RFC Destination
data: lt_dfies like table of dfies.
data: ls_dfies like dfies.
data: lv_tabname type DDOBJNAME.
data: OPTIONS like table of RFC_DB_OPT.
data: FIELDS like table of RFC_DB_FLD.
refresh ifc.
if gt_strucid is initial.
CALL FUNCTION 'RFC_READ_TABLE'
destination p_rfcdes
EXPORTING
QUERY_TABLE = 'DMC_STAG_STRUCID'
* DELIMITER = ' '
* NO_DATA = ' '
* ROWSKIPS = 0
* ROWCOUNT = 0
TABLES
OPTIONS = options
FIELDS = fields
DATA = gt_data
EXCEPTIONS
TABLE_NOT_AVAILABLE = 1
TABLE_WITHOUT_DATA = 2
OPTION_NOT_VALID = 3
FIELD_NOT_VALID = 4
NOT_AUTHORIZED = 5
DATA_BUFFER_EXCEEDED = 6
OTHERS = 7
.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.
refresh gt_strucid.
loop at gt_data into wa_strucid.
append wa_strucid to gt_strucid.
endloop.
endif.
read table gt_strucid into wa_strucid with key cobj_ident = p_ident struct_ident = tabname.
if sy-subrc <> '0'. exit. endif.
lv_tabname = wa_strucid-staging_tab.
CALL FUNCTION 'DDIF_FIELDINFO_GET'
destination p_rfcdes
EXPORTING
TABNAME = lv_tabname
* FIELDNAME = ' '
* LANGU = SY-LANGU
* LFIELDNAME = ' '
* ALL_TYPES = ' '
* GROUP_NAMES = ' '
* UCLEN =
* DO_NOT_WRITE = ' '
* IMPORTING
* X030L_WA =
* DDOBJTYPE =
* DFIES_WA =
* LINES_DESCR =
TABLES
DFIES_TAB = lt_dfies
* FIXED_VALUES =
EXCEPTIONS
NOT_FOUND = 1
INTERNAL_ERROR = 2
OTHERS = 3
.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.
loop at lt_dfies into ls_dfies.
CLEAR xfc.
move-corresponding ls_dfies to xfc.
apPEND xfc TO ifc.
endloop.
perform create_dynamic_itab.
CREATE DATA dy_db_table TYPE TABLE OF (dbtabname).
ASSIGN dy_db_table->* TO <dyn_db_table>.
CREATE DATA dy_db_line LIKE LINE OF <dyn_db_table>.
ASSIGN dy_db_line->* TO <dyn_db_wa>.
endform.
*&---------------------------------------------------------------------*
*& Form create_dynamic_itab
*&---------------------------------------------------------------------*
FORM create_dynamic_itab.
* Create dynamic internal table and assign to FS
CALL METHOD cl_alv_table_create=>create_dynamic_table
EXPORTING
it_fieldcatalog = ifc
i_length_in_byte = 'X' "added by Paul Robert Oct 28, 2009 17:04
IMPORTING
ep_table = dy_table.
ASSIGN dy_table->* TO <dyn_table>.
* Create dynamic work area and assign to FS
CREATE DATA dy_line LIKE LINE OF <dyn_table>.
ASSIGN dy_line->* TO <dyn_wa>.
ENDFORM. "create_dynamic_itab
form transfer using table .
data: lv_dbtabname(50).
read table gt_strucid into wa_strucid with key cobj_ident = p_ident struct_ident = table.
if sy-subrc <> '0'. exit. endif.
lv_dbtabname = wa_strucid-staging_tab.
data: sqlerr_ref type ref to cx_sql_exception.
data: msgtext type string.
try.
DATA:
l_stmt TYPE string,
l_prepstmt_ref TYPE REF TO cl_sql_statement,
l_dref TYPE REF TO data.
data:
p_con_ref TYPE REF TO cl_sql_connection.
p_con_ref = cl_sql_connection=>get_connection( p_dbcon ).
* create the statement string
CONCATENATE
'insert into "' lv_dbtabname '" values (?' into l_stmt.
loop at ifc into xfc.
if sy-tabix > 1.
concatenate l_stmt ',?' into l_stmt.
endif.
endloop.
concatenate l_stmt ')' into l_stmt.
* create a prepared statement object
l_prepstmt_ref = p_con_ref->prepare_statement( statement = l_stmt ).
* bind input variables
GET REFERENCE OF <dyn_table> INTO l_dref.
l_prepstmt_ref->set_param_table( l_dref ).
l_prepstmt_ref->execute_update( ).
CATCH cx_sql_exception INTO sqlerr_ref.
* cl_db6_con=>handle_exception( sql_stmt = sql_stmt
* sqlerr_ref = sqlerr_ref ).
msgtext = sqlerr_ref->sql_message.
write: / msgtext.
ENDTRY.
endform.
form mapping_rules_after using dbtabname db_wa wa.
case dbtabname.
when 'KNA1'.
perform map_field using 'NAMORG1' 'NAME1' wa db_wa .
perform map_field using 'NAMORG2' 'NAME2' wa db_wa .
perform map_field using 'STREET' 'STRAS' wa db_wa .
perform map_field using 'POST_CODE1' 'PSTLZ' wa db_wa .
perform map_field using 'CITY1' 'ORT01' wa db_wa .
perform map_field using 'COUNTRY' 'LAND1' wa db_wa .
perform map_field using 'REGION' 'REGIO' wa db_wa.
perform map_field using 'STR_SUPPL1' 'NAME3' wa db_wa .
perform map_field using 'PO_BOX' 'PFACH' wa db_wa .
perform map_field using 'LANGU_CORR' 'SPRAS' wa db_wa .
perform map_field using 'TELNR_LONG' 'TELF1' wa db_wa.
perform map_field using 'FAXNR_LONG' 'TELFX' wa db_wa.
perform map_field using 'SUPPLIER' 'LIFNR' wa db_wa.
perform map_field using 'REGION' 'REGIO' wa db_wa .
assign component 'BU_GROUP' of structure wa to <source>.
if sy-subrc = '0'.
<source> = '0001'.
endif.
endcase.
endform.
form map_field using target_field source_field wa db_wa .
field-symbols: <fieldvalue>, <source>.
assign component target_field of structure wa to <fieldvalue>.
if sy-subrc = '0'.
assign component source_field of structure db_wa to <source>.
if sy-subrc = '0'.
<fieldvalue> = <Source>.
endif.
endif.
endform.
I love blogs like this with the screens and the code. Could you do me a favor? I'm lazy and like to copy the code easily. Could you cut and paste the code into a code sample.
Thank you!
Michelle
thanks for the feedback, i updated this!
Amazing quick response!
Thank you for the update,
Michelle
Hi Jörg,
The SAP Note 2608495 was updated today with some additional information to NUMC fields in data migration objects. If you fill values of NUMC fields directly on data base level via HANA Studio (CSV upload) you will have to take care that leading zeroes are added to that values in the file. The ABAP DB layer will not add this when reading that data from the database. The same for initial values. Date, Time, NUMC, Decimal fields must be filled with the initial value if they are empty and the table definition in HANA shows a DEFAULT value.If load the data via CSV upload you have to take care of this as you are acting directly on the database level and not via the ABAP DB layer.
BR
Frank
Nice Blog 🙂
Hey Jörg,
Very Nice Thanks. Is there a limitation on the version of Oracle?
Pete
Hi Pete,
the staging concept in SAP S/4HANA Migration Cockpit is only valid for HANA Databases.
regards, sybille
Actually we are moving to HANA, but we have an older version of Oracle that we want to migrate from, will we have a problem connecting to an older version?
There is no direct connection to Oracle.
Staging tables are in HANA database - how you fill them is up to you - there is no automatic connection.
Hi,
Is it possible to create staging table in primary database instead of creating in secondary database? I am asking this question for learning perspective. I know in real time, only secondary database should be use.
Thanks
Hi Vipin,
Theoretically it would be possible to use the primary database - but this is for security reasons not advisable. SAP S/4HANA Migration Cockpit will only indicate database connections that were created via DBCO.
Regards, sybille
Hi Jörg Knaus
I found your blog really helpfull, thanks for it, do you tried to create a supplier and supplier with the same code using the cockpit, because it keeps showing me error, but I dont know what I did wrong.
Hi Jörg,
I was wondering about the naming conventions for the HANA-Staging tables created automatically by Migration Cockpit.
Usually we have a 3-tier target environment: DEV-QAS-PRD and I understand that we cannot export and import the MC project from DEV to QAS and from QAS to PRD if staging tables are in place.
So I am wondering if the automatically generated HANA staging tables have the same name from all 3 different environments (generated from MC in DEV + QAS+ PRD).
What is the exact naming convention? let's say for bank master. Is there always the <SYD> included?
So if I fill the HANA staging table for bank master from the source system (only one source) do I require 3 different DS jobs because I first fill the staging table for migration into S/4 HANA DEV and another DS job to fill staging table for migration into S/4 HANA QAS and a third job to fill staging table for migration into S/4 HANA PRD?
Would be easier and re-usable the DS job if the HANA staging tables for the same objects have the same name for DEV/QAS/PRD load.
What would the the correct approach?
Thanks for early reply!
Thomas
Hello Thomas
As the staging table gets generated in each system, the name of the table will differ to ensure uniqueness.
The staging tables are generated in namespace /1LT/DS<sys-id>. As a consequence you have to adjust the data extraction program accordingly for every test run.
We are aware that this may present risks to the project team in case someone forgets to adjust the extraction program. Therefore we are going to investigate if there is a possibility to manually specify the name of the staging tables in customer namespace.
best regards,
sybille
Hi Sybille,
Did not find in SE11 any tables in namespace /1LT/STAG. Only with /1LT/DSS.
Examples:
/1LT/DSS4B000000 Z_KOSTL_MASTER_00B:S_CSKS
/1LT/DSS4B000001 Z_PRCTR_MASTER_00B:S_CEPC
/1LT/DSS4B000002 Z_PRCTR_MASTER_00B:S_CEPC_BUKR
/1LT/DSS4B000003 Z_BANK_MASTER_00B:S_BNKA
/1LT/DSS4B000004 Z_LSTAR_CREATE_00B:S_CSLA
/1LT/DSS4B000005 Z_AP_OPEN_ITEM_00B:S_BSIK
/1LT/DSS4B000006 Z_BANK_MASTER_00H:S_BNKA
/1LT/DSS4B000007 Z_KOSTL_MASTER_00H:S_CSKS
/1LT/DSS4B000008 Z_PRCTR_MASTER_00H:S_CEPC
/1LT/DSS4B000009 Z_PRCTR_MASTER_00H:S_CEPC_BUKR
So the staging table name nor description does not contain the <SYD> like DEV, QAS or PRD?
It is just a correlative number in the table name and we have no idea to which environment the staging table refers?
Is there a way to see which staging tables belong to Migration Cockpit in DEV in QAS and in PRD system?
Sorry for bothering....
Thomas
Hi Sybille,
I found the DMC-Tables with the staging table names. However there is no SYD-field /MANDT-field - or at least I did not find it.
So my question is:
If we have in DEV-, QAS- and PRD the same Migration cockpit project name, Structure and Description (export + import of project from DEV to QAS and PRD): how can we distinguish the 3 different staging tables that we have to populate with SAP Data Services, Linked Server or SDI?
I guess that the 3 staging tables for the same project and object in the 3 environments just have a different number (/1LT/DSSXX000010 in DEV , /1LT/DSSXX000030 in QAS , /1LT/DSSXX000060 in PRD) .
I can implement a custom function in SAP DS to point to the right staging table, but which are the selection field for the select of the DMC-Tables?
Any hint or suggestion is highly appreciated.
Thanks,
Thomas
Hi,
staging tables naming convention is:
/LT1/DS<sys-id><consecutive number>.
When you export a project from DEV to QAS - first the staging table names are imported from DEV - then you need to do a synchronisation (system displays automatically this hint) -> new staging tables are beeing generated with naming convention above.
that means that there is no direct link between DEV - QAS staging tables and as numbers are consecutive - you will not find easily this comparison.
We are aware of this issue and investigating in further improvement (maybe a mapping table).
sybille
Hi Jörg,
Morning, How are you? I need your expertise and suggestion, I activate MC Staging model for Fixed Assets( Inc Balances), staging tables are generated and Cumulative values staging table is not allowing decimal places to any depreciation amount fields, its rounding all our depreciation values when I load them into the staging. The Fields using for amounts in staging are generated with DEC data type but decimals places as 0.
I checked in Source structure ( LTMOM) and also downloaded file content, they both allowing decimal places.
What would be the correct approach and appreciate for early reply 🙂
Hi Jörg,
Thank you for the detailed blogs that you had created about the migration cockpit. I have some issue when creating a new project using the staging tables.
As a preparation I had created the database connection via transaction DBCON. The connection works also, I tested overthere. But when I am opening the search help for the Database connection, it is empty. This is strange to me because I have 2 connections and none of them are available.
Anyhow, I know the database connection OSA is there, so I select this one. But then I get the following message. At the begining of your blog, you have mentioned that the user should have 2 roles: SAP_CA_DMC_MC_USER and SAP_CA_DMC_MC_DEVELOPER.
I have both roles. Can you indicate which authorization I am missing?
Thanks in Advance.
Chu
Hi,
I had found the solution thanks to SAP. The indicated that I need to maintain this table ( ) via DMC_C_WL_DBCO_OP. After I had done this, the database connection is on the dropdown list.
And I can start testing the migration cockpit with staging tables.
Regards,
chu
I had the same issue and resolution. Thanks for sharing!
What happens to the staging tables after the migration is done? Do they stay in the database for ever?
If they do retain, is there anything wrong with using the Migration cockpit's staging capability for on-going loading of new data ?
We need a staging area to load new data (eg. rates), allow business users to run some validations, then publish to live database. This all has to happen without transports (ie. in prod) once a month.
I know migration cockpit is not meant for this, but do you see anything wrong with ongoing migration versus initial?
Thanks
Dear Jörg,
After the successful upgrade to 1809 (on-premise), I am working now on additional fields for existent source structures in migration object SIF_CUSTOMER_2 - General Data.
New fields needs a synchronization within the Cockpit functionality, this is quiet clear.
But what to do if the error message "Error during synchronization, inconsistency remains" appears.
The new field is assigned within the customer view, it is at the end of the table, settings are visible, the database staging table is active and includs the field at the end before processing status, the migration object could be generatet in LTMOM. But the synchronization within LTMC fails.
Any hint or recommendation is hihly apreciated.
Regards, Helmut
2 ideas:
field type of your custom fields same to standard fields or possible problem when generating staging?
any chance to delete and re-create the staging tables from scratch?
regards
joerg
Hi Helmut,
Did you solve this issue?
I have the same problem and I saw that this errors happens because the constraints of the Staging Tables are deleted from the view SYS.CONSTRAINTS when I add new fields in the Staging Table.
For exemple: When I created staging table /1LT/DSSC0000206 (S_BNKA) 3 contraints are created for this table.
After that I added one field (TEST) in structure S_BNKA and clicked on menu: Migration Object -> Synchronize Structure
When I opened again the view SYS.CONSTRAINTS, the constraints was deleted.
When I open the Migration Object Bank, show the message "Inconsistency found; synchronize staging tables with the migration object" and when I execute "Start Synchronization", show the message: "Error during synchronization; inconsistency still exists".
I have debugged the code of the button "Start Synchronization" and I saw that exist a comparation with constraints. As the record of contrains was deleted, there is an inconsistency in the table, but I don´t know why this contstraints doesn't create the constrains again.
Regards,
Willian K.
Hello,
Are you able to resolve this problem? We are facing the same issue. I have debugged and looks like it is not refreshing the SYSTEM schema.
Any clue will be very helpful
Thanks.
We have understood the issue, so keeping a comment here, just to help anyone who is facing this issue.
While configuring DBCO connection BASIS team has not maintained Connection Info correctly, which led to this issue. After correcting this details, it has started working as expected.
Thanks.
Kuntal
Hello Nandi.
Can you give more details on how did you solve the problem in the DBCO configuration?. I am having the same error.
Hi Sybille/Jorg
We are using DMC Staging tables for a 1809 S/4 HANA implementation. Question I have is why are the generated HANA Staging tables created as NOT NULL. The client has many fields with NULL values and when the data is loaded to staging tables, it throws a SQL error due to the NOT NULL requirement for every field.
This problem is not there for File based DMC, since it allows you to not enter empty /null values.
Can you please advise any remedy for this big limitation with Staging tables since all customers have many non mandatory fields with NULL values. Please see the below generated Bank Data Staging table.
Regards
Gopi
Hi Gopi,
Did you resolve above issue to load data with empty values in CSV using SQL IMPORT?
Thanks,
Roy