What is Reconciliation?
Reconciliation is the process of comparing the source system data with target system data. Source system can be single or multiple SAP/Non SAP systems.
Why Data Reconciliation?
In the process of ETL (extract, transform, load) the whole nature of the data can be changed as per project/business requirement, i.e when data loads from source system to target it is not necessarly to be same.
Reconciliation comes into picture to compare the source and target data. At times it may also happen that some information/data is lost while transforming the data. Reconciliation process helps to identify such loss of information/data and patch up whenever/wherever required.
There can be variaous reasons for the information/data loss when it moves from source to target. One of the major reasons is loading failures or errors during loading which can bring in partial data. Data load errors can occur due to many reasons
· Inconsistent data from source
· Unclean/ non-profiled data
· Un-handled exceptions
· Constraint violations
· Logical issues/ Inherent flaws in program
· Technical failures like loss of connectivity, loss over network, space issue etc.
Failure due to any such issue can result into potential Information/data loss leading to unreliable data quality for business decision making.
Methods of Reconciliation
· Master Data
· Transaction Data
Master Data
Master data reconciliation is the method of reconciling only the master data between source and target. Master data are generally unchanging or slowly changing in nature and the granularity of the data remains same in both source and target. That is why master data reconciliation is often relatively easy and quicker to implement.
In one business process, “customer”, “products”, “employee” etc. are some good example of master data. Ensuring the total number of customer in the source systems match exactly with the total number of customers in the target system is an example of customer master data reconciliation.
Transaction Data
Transactional data make the very base of BI reports so any mismatch in transactional data can cause direct impact on the reliability of the report and the whole BW/BI system in general. That’s why reconciliation must be in-place in order to detect such a discrepancies. Sales quantity, revenue, tax amount etc. are examples of transactional data.
Transactional data reconciliation is always done in terms of total sum. Also this total sum can be done on either full data or on incremental data set.
Reconciliation Approaches
Reconciliation approaches are usually dependent on system landscape and business processes/logic and cannot be standardized. In this document I talk about two approaches to data reconciliation based on my project experience.
*Refer below links on
“How to.. Reconcile Data Between SAP Source Systems and SAP Netweaver BI”
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/7a5ee147-0501-0010-0a9d-f7abcba36...
“Data Reconciliation”
http://help.sap.com/saphelp_erp60_sp/helpdata/en/43/714ae8b5263457e10000000a422035/content.htm
There can be various approaches to reconcile data between two systems, Below I have mentioned a few of the simples forms
Approach 1:
Simplest form of data reconciliation between two systems is by counting the number of records for that particular day in case of transactional data and overall count in case of master data given there is no additional filter logic .
Some of the common examples of data reconciliation can be the following measures,
· Total count of rows, example
o Total Customer in source and target
o Total number of Products in source and target etc.
· Total count of rows based on a condition, example
o Total number of active customers
o Total number of inactive customers etc.
· Total count of transactional records updated for a particular day (Delta run), example
o Total number of customers who have booked time for a day
Solution:
For Master data we can create a custom program to pull the record count from master data tables in R3 and its corresponding BW info object tables to compare with each other
Example: Number of WBS elements in R3 can be get from PRPS table and its corresponding BW tables are associated with Info Object 0WBS_ELEMT
Master data reconciliations are usually not done as they are FULL loads and there is no loss of data. Depending on project and business requirements certain master data can be DELTA and need reconciliation
In case of Transaction data create a custom program/report on the SAP R3 table RORQSTPRMS and Create a cutom BW Report which pull data from Table RSSTATMANPART to Virtual Provider in BW.
To compare the variances between two systems either do Report to Report Interface (RRI) from BW report to the application table report or manually calculate the variance between two reports using simple MS Excel macros when extracted.
*Refer below links on
Report To Report Interface (RRI)
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/607e63d5-b56f-2c10-718f-8e33a08ba...
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/803cf7c5-2794-2e10-8d99-e0affb6d8...
Approach 2:
Approach 1 limits to high level reconciliation and it might not be applicable in certain business scenarios. It is also technically not feasible everytime to compare the number of records between two or more systems, as data when entered into target system gets cleasened or split into multiple records depending on business logic.
For a detailed level of reconciliation for transactional data we follow approach 2 which compares generic/business critical key metrics within two systems
Some examples measures used for transactional data reconciliation can be
· Sum of total revenue calculated from source and target
· Sum of total product sold calculated from source and target etc.
How do we compare key metrics?
Step 1: Creating ABAP report on the SAP R3 Application tables in Source system with one or many Key Performance Indicators(KPI), which will give details of Generic Key Metrics (Example: Amount, Quantity..) of records for the particular day.
Step 2: Creating custom BW Reports on corresponding info providers(Multi-Provider, Info Cubes and DSO) with similar key metrics as used in ABAP Report
a. Do a Report to Report Interface(RRI) from BW report to the ABAP Report to see if the data tie out exactly between two systems.
b. Auto Schedule the ABAP Report using a Job to generate spool. Compare the spool values with BW report by downloading both the reports and manually calculate the variance between two reports using simple MS Excel macros.
Please note both ABAP Reports and Data Modelling of Info Providers in BW are purely based on business logic and differs from one project to other
Sample Reconciliation - Approach 2(b)
Step by Step Procedure for Hours Reconciliation between SAP R3 and BW
Business Scenario Reconcile Time sheet data from SAP R3 to BW for a month
Custom R3 Recon Job is created to capture hours booked from SAP Standard Table – CATSDB and scheduled
Tcode – SM37 -> Job Name, Date, Time and Status
Based on the business scenario, I have created a variant with below details to be used in the ABAP report. Procedure to check for variants is detailed in below snapshots
Data range 01 Jan 2013 to 30 Jan 2013
CATS Status = 30 (only Approved Time entries)
Once the Job is scheduled and complete a spool is generated with details of time sheet from CATSDB. This spool data is used to compare the values between R3 and BW. Following snapshots show to display spool data and compare with BW Reports
In our case the spool has generated 667 pages, last page of the spool gives us the totals of hours as shown below
BW report based on OCATS_1 Info cube
Automation of Reconciliation Process
Benefits:
§ Processes automated with auto set variants to nullify errors and expedite processes
§ Discrepancies if any will be immediately broadcasted to business post batch completion
§ New BW Recon Reports to have both BW and R3 values which makes easy to interpret
§ New process to open widow for other reconciliations and expand existing ones
§ Technical team to perform research and publish possible reasons well ahead of business hours as new approach to consume less time comparatively
§ This approach requires less BW modeling and ABAP developments
- SAP Managed Tags:
2 Comments
