In this blog post I'd like to share the insights of a customer project where we added data reconciliation after the SAP S/4HANA Migration activities.
The blog post explains the following:
The SAP S/4HANA Migration is mostly performed with the following means of data migration tools:
SAP S/4HANA Migration and its full capabilities is explained here (help.sap.com): SAP S/4HANA Migration Cockpit.
The available migration objects can be found here: Migration Objects
In our project we mostly used the Template approach (SAP S/4HANA Migration Template Approach) which is from my point of view for the customer the simplest way of moving data from source to target. This assumes however that the data load is not too big and that the customer is mostly in charge of the data creation. In the case of larger data sets, the preferred approach or tool is rather the SAP S/4HANA Migration Direct Transfer Approach (explained for instance here), SAP Data Services (RDM Content for SAP S/4HANA Migration using SAP Data Services) or using the SLT migration tool for large-scale datasets.
Reconciliation of SAP S/4HANA Migration content can be done via different means and tools:
If you move objects from a legacy ERP system (e.g. SAP ECC 6.0) to a new SAP S/4HANA ERP system, the objects of choice to be moved can be found here: Migration Objects
E.g. you pick the data object Profit center and you move the data with the template approach. The way you move the data is that the customer prepares a template file that look as follows:
The different Excel sheets within the XML file are to be filled or serve as additional information. For the data object Profit center the sheets "Profit center master record" and optionally "Company code assignment" can be filled. Then the filled sheet can be used within the SAP S/4HANA Migration Cockpit (transaction LTMC) to import and directly create the Profit centers in the SAP S/4HANA system - necessary roles & authorizations need to be put in place before-hand.
In the explanation of the object (available online as e.g. here: Profit center Object Description) the manual recon is described as some simple post-processing steps:
For simple data objects like Profit center this might be enough (under the assumption that there are not many of the objects to be migrated) and can be done manually, however an automated approach helps very much and is sometimes required by audit partners to have an audit trail that is not so much (human) error-prone.
The approach I built up can be shown in the following picture:
The 3 components involved were
(A) SAP Data Services installation [left side]
(B) Source system (SAP ECC 6.0) [upper right side]
(C) Target system (SAP S/4HANA) [lower right side]
A reconciliation process was then performed as follows:
On the example of Profit center (SAP table CEPC) I show how the data services jobs have been designed. The job built up for the automatic reconciliation in Data Services uses the following flows (Work / Data):
The reconciliation was executed using 3 steps - (1) data extraction, (2) data comparison, (3) results loading).
The data extraction part is easy and you can already model in some mappings if required at the stage of data extraction:
After the data extraction follows the data comparison stage. Initially in this stage the data at hand needs at least one of the following:
This is done in the Work Flow step WF_FilterMap_Staging_Par_CEPC (meaning that a filtering and mapping is to be applied within the Staging database [local temporary storage where Data Services can work on] in parallel mode on the source and target data sets for table CEPC):
The appraoch is here as follows: based on a primary key or user-defined key you get the data rows that
Within the result table STG_RECON_CEPC table there are apart from the standard fields of the table CEPC the following additional fields:
With this information the full reconciliation is possible. Scenarios 1:n, n:1 or n:m are unfortunately in this kind of Data Services reconciliation job not so easy possible and therefore the reconciliation is limited to 1:1 source and target comparison.
The DIFF_RESULT is calculated in a 2-stage calculation by first comparing each value using e.g. a formula ifthenelse(sort_src.TYPE <> sort_trg.TYPE, 'X', '') to get the DIFF signs and then combining all DIFF signs with the formula join_equal.DIFF_TYPE||join_equal.DIFF_BPKIND||.... The same applies to the DIFF_FIELD and DIFF_VALUE, just that the formulas are more complicated.
After the comparison the data is loaded via an ODBC connection into the target table inside the SAP S/4HANA system:
For the results display I developed multiple ABAP Web Dynpro applications for the functional end-users.
The entry-point to the reconciliation is the following results overview screen:
This screen shows the aggregated statistics for the comparison of the data (here on the example of table BUT000).
As you can see, the 4 categories EQUAL, DIFFERENCE, TARGET_DATA and SOURCE_DATA have to add up to 100% - in this case no source data records show up due to the customer's decision to ignore these lines (carve out migration scenario). The attribute DIFF_FIELD shows the various combination of fields that lead to a certain amount of differences. From these statistics you can deduce that the business partner import program (a custom import program due to a separate 3rd party system) leads to differences in about 3.3% of the data records due to a differing language for the technical field LANGU_CORR and BU_LANGU. Based on this information you'd like to see more details of data and hence by clicking on the table name in the corresponding row, the detail application (Search / Result screen) opens with the details:
The differences are displayed with cell-coloring and a tooltip on the cell unveils the source value of the difference. The baseline data is the target-data in the case of EQUAL or DIFFERENCE and hence the difference value for the source is shown through the tooltip:
In a productive migration scenario, these reconciliation screens can support the functional data owners to prove to auditors that the migration was indeed successful. In test migrations, these reconciliation tools support the error finding and help to get to a quicker error resolution.
The implementation of the Web Dynpro screens is fully generic and hence a reconciliation detail screen for BUT000 or LFA1 only differs in the call parameters - the rest is handled automatically in the implementation and search parameters, columns and data are extracted dynamically based on the provided table. The ABAP development consists of multiple Web Dynpro applications and multiple generic or specific Web Dynpro Feeder and Helper classes.
This blog post should just serve as a high-level overview and does not show the full details of the SAP Data Services job implementation or the ABAP implementation. The complete implementation (E2E) was used successfully in a SAP S/4HANA Migration project and helped the customer to accelerate and automatize the reconciliation of the migration content.
Let me know what you think about this custom reconciliation solution in the comments below. Any suggestions and inputs are welcome. Thank you.
The blog post explains the following:
- SAP S/4HANA Migration related data objects and how to do manual recon
- Architecture for End-2-End (E2E) automated reconciliation using
- SAP Data Services
- SAP ECC 6.0 Source system
- SAP S/4HANA Target system
- ABAP Web Dynpro screens
- SAP Data Services processes required for automatic reconcilation
- ABAP Web Dynpro screens for results display
The SAP S/4HANA Migration is mostly performed with the following means of data migration tools:
- SAP S/4HANA Migration Cockpit
- SAP Data Services
SAP S/4HANA Migration and its full capabilities is explained here (help.sap.com): SAP S/4HANA Migration Cockpit.
The available migration objects can be found here: Migration Objects
In our project we mostly used the Template approach (SAP S/4HANA Migration Template Approach) which is from my point of view for the customer the simplest way of moving data from source to target. This assumes however that the data load is not too big and that the customer is mostly in charge of the data creation. In the case of larger data sets, the preferred approach or tool is rather the SAP S/4HANA Migration Direct Transfer Approach (explained for instance here), SAP Data Services (RDM Content for SAP S/4HANA Migration using SAP Data Services) or using the SLT migration tool for large-scale datasets.
Reconciliation of SAP S/4HANA Migration content can be done via different means and tools:
- Standard jobs provided by the RDM Content with SAP Data Services (here)
- LTVF (Landscape Transformation Validation Framework) as a service provided by the (formerly called) SLO team
- Custom-built reconciliation as shown in this blog post here!
SAP S/4HANA Migration related data objects and how to do manual recon
If you move objects from a legacy ERP system (e.g. SAP ECC 6.0) to a new SAP S/4HANA ERP system, the objects of choice to be moved can be found here: Migration Objects
E.g. you pick the data object Profit center and you move the data with the template approach. The way you move the data is that the customer prepares a template file that look as follows:
Template Snapshot
The different Excel sheets within the XML file are to be filled or serve as additional information. For the data object Profit center the sheets "Profit center master record" and optionally "Company code assignment" can be filled. Then the filled sheet can be used within the SAP S/4HANA Migration Cockpit (transaction LTMC) to import and directly create the Profit centers in the SAP S/4HANA system - necessary roles & authorizations need to be put in place before-hand.
In the explanation of the object (available online as e.g. here: Profit center Object Description) the manual recon is described as some simple post-processing steps:
Post-Processing Steps
For simple data objects like Profit center this might be enough (under the assumption that there are not many of the objects to be migrated) and can be done manually, however an automated approach helps very much and is sometimes required by audit partners to have an audit trail that is not so much (human) error-prone.
Architecture for End-2-End (E2E) automated reconciliation using SAP Data Services, SAP ERP Source & Target System and ABAP Web Dynpro
The approach I built up can be shown in the following picture:
Recon Architecture
The 3 components involved were
(A) SAP Data Services installation [left side]
(B) Source system (SAP ECC 6.0) [upper right side]
(C) Target system (SAP S/4HANA) [lower right side]
A reconciliation process was then performed as follows:
- A migration user (black user-icon overview) triggers the reconciliation process in SAP Data Services after the migration has been completed.
- The SAP Data Services job extracts the relevant source data.
- The SAP Data Services job extracts the relevant target data.
- The SAP Data Services job compares the two data sets.
- The SAP Data Services job uploads the results into a custom table on the SAP S/4HANA system for end-user inspection.
- A business user (violet user-icon in the overview) then checks the results via SAP GUI or Web Dynpro (this solution was built as an example here) or other means.
SAP Data Services Processes required for automatic reconcilation
On the example of Profit center (SAP table CEPC) I show how the data services jobs have been designed. The job built up for the automatic reconciliation in Data Services uses the following flows (Work / Data):
3 Steps of DS Recon
The reconciliation was executed using 3 steps - (1) data extraction, (2) data comparison, (3) results loading).
The data extraction part is easy and you can already model in some mappings if required at the stage of data extraction:
Data Extraction
After the data extraction follows the data comparison stage. Initially in this stage the data at hand needs at least one of the following:
- data mapping (if e.g. company codes differ and need to be changed)
- data lookup (if e.g. number ranges are different or if the customer used a dedicated field to keep the legacy information in form of an immutable id)
- data joining (e.g. by means of inner/left join with other data tables as e.g. in the case of LFA1 and LFB1 for BUKRS information)
- data filtering (e.g. only valid data sets that do not have a blocking signal should be compared)
This is done in the Work Flow step WF_FilterMap_Staging_Par_CEPC (meaning that a filtering and mapping is to be applied within the Staging database [local temporary storage where Data Services can work on] in parallel mode on the source and target data sets for table CEPC):
Data Validation
Reconciliation Step
The appraoch is here as follows: based on a primary key or user-defined key you get the data rows that
- match up => gives comparable results with differences or no differences (equality)
- appear only in the source (source rows) => rows that do not appear in the target after the migration
- appear only in the target (target rows) => rows that do not appear in the source but are existing in the target after the migration (or already before the migration)
Within the result table STG_RECON_CEPC table there are apart from the standard fields of the table CEPC the following additional fields:
- SID - the identifier for the system (e.g. SRC or TRG but actual SID information)
- DIFF_RESULT - one of DIFFERENCE, EQUAL, SOURCE_DATA, TARGET_DATA
- DIFF_FIELD - if there's a DIFFERENCE the list of technical field names
- DIFF_VALUE - if there's a DIFFERENCE the list of the actual source data cell content (corresponding to the technical field name listed in DIFF_FIELD above)
With this information the full reconciliation is possible. Scenarios 1:n, n:1 or n:m are unfortunately in this kind of Data Services reconciliation job not so easy possible and therefore the reconciliation is limited to 1:1 source and target comparison.
The DIFF_RESULT is calculated in a 2-stage calculation by first comparing each value using e.g. a formula ifthenelse(sort_src.TYPE <> sort_trg.TYPE, 'X', '') to get the DIFF signs and then combining all DIFF signs with the formula join_equal.DIFF_TYPE||join_equal.DIFF_BPKIND||.... The same applies to the DIFF_FIELD and DIFF_VALUE, just that the formulas are more complicated.
After the comparison the data is loaded via an ODBC connection into the target table inside the SAP S/4HANA system:
Results Loading
ABAP Web Dynpro screens for results display
For the results display I developed multiple ABAP Web Dynpro applications for the functional end-users.
The entry-point to the reconciliation is the following results overview screen:
Results Overview
This screen shows the aggregated statistics for the comparison of the data (here on the example of table BUT000).
As you can see, the 4 categories EQUAL, DIFFERENCE, TARGET_DATA and SOURCE_DATA have to add up to 100% - in this case no source data records show up due to the customer's decision to ignore these lines (carve out migration scenario). The attribute DIFF_FIELD shows the various combination of fields that lead to a certain amount of differences. From these statistics you can deduce that the business partner import program (a custom import program due to a separate 3rd party system) leads to differences in about 3.3% of the data records due to a differing language for the technical field LANGU_CORR and BU_LANGU. Based on this information you'd like to see more details of data and hence by clicking on the table name in the corresponding row, the detail application (Search / Result screen) opens with the details:
Detail Screen BUT000
The differences are displayed with cell-coloring and a tooltip on the cell unveils the source value of the difference. The baseline data is the target-data in the case of EQUAL or DIFFERENCE and hence the difference value for the source is shown through the tooltip:
Detail screen: highlighting and tooltip information
In a productive migration scenario, these reconciliation screens can support the functional data owners to prove to auditors that the migration was indeed successful. In test migrations, these reconciliation tools support the error finding and help to get to a quicker error resolution.
The implementation of the Web Dynpro screens is fully generic and hence a reconciliation detail screen for BUT000 or LFA1 only differs in the call parameters - the rest is handled automatically in the implementation and search parameters, columns and data are extracted dynamically based on the provided table. The ABAP development consists of multiple Web Dynpro applications and multiple generic or specific Web Dynpro Feeder and Helper classes.
Final Notes
This blog post should just serve as a high-level overview and does not show the full details of the SAP Data Services job implementation or the ABAP implementation. The complete implementation (E2E) was used successfully in a SAP S/4HANA Migration project and helped the customer to accelerate and automatize the reconciliation of the migration content.
Let me know what you think about this custom reconciliation solution in the comments below. Any suggestions and inputs are welcome. Thank you.
- SAP Managed Tags:
3 Comments
