Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Fusion PayRoll
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

Introduction

Every ERP Implementation project needs to have a Data Reconciliation activity once the Data Migration is completed ( can be done phase wise too, but unless we reconcile the data we are not sure about both the volume (data records loaded) as well as the quality (correctness)  of data. While it was comparatively easy when we used to Transfer data from one On-Premise System to another (assuming both the system allows us to create custom database objects) it at times might be challenging when you are moving data from:

 

1.      On-Premise Source System To a Cloud Target System

2.      Cloud Source System To Another Cloud Target System

 

I have seen some instances where the implementation teams take a Data Extract from the Cloud Target System and loads the Data into a Custom Database Object ( at On-Premise Source System) and then performs data reconciliation. While this approach works fine I personally am reluctant to use this approach. Some of the reasons being:

 

1.    Organizations move from On-Premise System to Cloud System primarily to standardize there process and get rid of Custom Objects (both Database components as well as UI Items). Keeping this goal in mind if we have to create additional (I am assuming one would suffice but there could be need of more than one too) database objects at the source system then the entire purpose is lost. One has to do additional maintenance of this new object.

2.   At all points one would need to keep going back to the source system to perform reconciliation when this activity should ideally be performed at the Target Application.

3.   Imagine if there are multiple source systems then in that case one would need to create additional database objects for each source system

4.   The moment you decide to decommission your Legacy Systems you are doing away with this reconciliation feature too (unless you decide to extract and store the generated comparison reports)

5.   And add to all this what if your source system is also on Cloud  (No Option to create custom database objects).

 

Well does this means that we are left with no option and we can’t perform Data Reconciliation.

 

At first thought it might appears so, but that’s not the case,we have a workaround, and that is creating the same report in Target Application.

 

Let’s see how to do that.

 

Worked Out Example

In this example we would be using two sets of static data (excel spreadsheet one comprising data extracted from the source system (say SourceSystemPersonData.xls) and the other one from the target application (say TargetSystemPersonData.xls)). We might have used actual database tables from the target application instead of using a Excel but that would have involved a little bit more complication and chances are there that we might lose the focus too. If required we may take up that activity in next article, but for now let’s restrict ourselves to this scope ( static excel spreadsheet data ).

We are only considering three data fields for comparison which are:

 

1.      Person Number (PersonNumber)

2.      Person Name (PersonName)

3.      Person Date of Birth (PersonDOB)

 

We would be performing three scenarios here which are:

 

1.      Verify whether the data in Person Name field for both Source System and Target System is correct

2.      Verify whether the data in Person Date of Birth field for both Source System and Target System is correct

3.      Verify whether every Person Record (PersonNumber) present in Source System has got loaded into the Target System or not.

 

In order to perform all these checks we need to carry out certain steps which are as follows:

 

1.      Create a Custom Data Model and load both the SourceSystemPersonData.xls and TargetSystemPersonData.xls as two separate Data Sets

2.      Create a Data Link between the two Data Sets

3.      Create Expression Variables

4.      Create a Report Template and apply conditional formatting to easily track the mismatching data / records ( using colour coding)

5.      Create a Report using the Custom Data Model and the Template and Verify Results

 

Create Custom Data Model

 

We would need to create a custom data model making use of the SourceSystemPersonData.xls and TargetSystemPersonData.xls

 

A snapshot of SourceSystemPersonData.xls appears as below:

 

A snapshot of TargetSystemPersonData.xls appears as below:

 

 

The SourceSystemPersonData (Data Set) is created using SourceSystemPersonData.xls as the data source (screenshot below):

 

 

Similarly, TargetSystemPersonData (Data Set) is created using TargetSystemPersonData.xls as Data Source (Screenshot below):

 

 

Create a Data Link Between The Data Sets

 

Now that we have the Data Sets created we would need to create a Data Link between the two Data Sets (named as G_1 for SourceSystemData and G_2 for TargetSystemData)

 

Create Expression Variables

 

In this step we would need to create expression variables. If you are not sure what an Expression variable is or how to use it please feel free to read thisarticle hopefully this would get you started.

We would create three expression variables. Details in table below:

*Name

Data Type

*Expression

TargetSystemPersonName

String

IF(G_1.SourceSystemPersonNumber==G_2.TargetSystemPersonNumber,G_2.TargetSystemPersonName,'')

TargetSystemPersonDOB

Date

IF(G_1.SourceSystemPersonNumber==G_2.TargetSystemPersonNumber,G_2.TargetSystemPersonDOB,'')

TargetSystemPersonNumber

Double

G_2.TargetSystemPersonNumber

 

Once created the Data Model would appear as below:

 

 

Create Report Template and Apply Conditional Formatting

In this step we would create a BI Publisher Template (XPT) and map the contents of G_1. We would be using the following data fields:

 

1.      SourceSystemPersonNumber (Prompt changed to PersonNumber)

2.      SourceSystemPersonName

3.      TargetSystemPersonName

4.      SourceSystemPersonDOB

5.      TargetSystemPersonDOB

 

Also we would apply conditional formatting on PersonNumber , TargetSystemPersonName and TargetSystemPersonDOB fields.

Attribute

Conditional Logic

Result

SourceSystemPersonNumber

SourceSystemPersonNumber is not equal to TargetSystemPersonNumber

Background Colour as RED.

TargetSystemPersonName

TargetSystemPersonName is not equal to SourceSystemPersonName

Background Colour as RED.

TargetSystemPersonDOB

TargetSystemPersonDOB is not equal to SourceSystemPersonDOB

Background Colour as RED.

 

Once all the setup is done we can run the report and verify the results.

 


Ashish Harbhajanka

Add comment


Security code
Refresh

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner