SAP S/4HANA Reconciliation with SAP Data Services (E2E)

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:

◉ 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

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:

1. A migration user (black user-icon overview) triggers the reconciliation process in SAP Data Services after the migration has been completed.

2. The SAP Data Services job extracts the relevant source data.

3. The SAP Data Services job extracts the relevant target data.

4. The SAP Data Services job compares the two data sets.

5. The SAP Data Services job uploads the results into a custom table on the SAP S/4HANA system for end-user inspection.

6. 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

The actual data comparison then is done in the compare stage inside the Data Flow DF_Compare_Staging_CEPC:

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.