Confronting Data Integration Nightmares

Back in November 2019, when our engagement with this large MNC for an Oracle HCM Implementation began, little did we know that the world was shortly going to be shut-down owing to a global pandemic!

The fact that our client had offices in multiple countries across the globe, only aggravated the situation.

Following were the 3 major tasks that lay ahead of us then:

  1. Employee data conversion from 5 countries – residing in varying time-zones, formats and languages.
  2. Data migration – from disparate legacy systems into a single unified system.
  3. Oracle HCM Cloud Implementation – where the deadline (go-live date) was non-reschedulable.

Apart from these, a major challenge was the Corona pandemic, which brought with it network and mental outages!

But despite the odds, the Oracle HCM cloud solution was deployed successfully as planned – with the united & conscientious efforts of all stakeholder teams that were involved.

In this post we would like to talk about how we worked through these constraints amicably.

Employee data conversion from 5 countries:

It was kind of given that there were limited opportunities to have sync up calls housing all country stakeholders, unless at least a couple of us burned the midnight oil!

So, after the initial connect calls, we scheduled individual connect calls with each country POCs to sync up on queries and resolve conversion issues.

Working in the IST ourselves presented to be a bit of a blessing in disguise. We soon realized that India had close overlaps in time-zones with the other involved countries in the conversion process.

Our teams internally decided to work with the time-zone gap in mind, as working on opposable clocks meant losing much productive days. Eventually, our team-mates working on China, Turkey & India employee conversions were encouraged to start off their day early, while teamies working on US & Mexico were suggested to wake up late to facilitate working a little late.

Data Migration & Data Cleansing

Like any other data migration project, the first requirement was to migrate all the pertinent employee data from the client’s legacy systems into a single format for load into Oracle HCM Cloud.

Following were few of the conditions that we had to work around:

  • Each of the 5 countries had their own legacy systems.
  • Data consisted of many non-English texts.
  • Employment changes or historical employee data was logged differently in each legacy system.
  • Employee IDs were assigned as per different methodologies in each country.
  • Source datasets were marred with data issues.

As this was the primary source of truth for the rest of the process, we installed the following foolproof practices to overcome the aforementioned issues:

Normalizing

There are numerous tools that could be employed for such a project, in present day. However, considering the vastness of the scope and the number of iterations that were projected to be needed – we decided that it was best to work with the age-old, robust MS SQL databases.

Data Mapping

This was one of the most critical components for conversion progression.

As already mentioned, the data source was in different formats with differences in column headers – names, count, order, etc. Even many mandatory values were unreadable in other foreign language.

So, we created some mapping tables for comparing source file values and destination format requirements for the following:

  • Column header comparisons
  • Mandatory data missing
  • Oracle List-of-Values
  • Global Work-structures
image showing the data conversion process: Automated scripting, batching, mappings, normalizing.
Batching

We setup some hybrid-automated batch processes. These were designed such that each time a new source dataset was received – the structural issues could be triaged & called out immediately.

This process was enabled by verifying the state of the respective mapping tables via these manually triggered scripts. It aided greatly in getting quick turn-arounds before the actual conversion process.

Automated Scripts

Once all the above items were in place and had done their respective jobs, the SPs were triggered to proceed with the actual conversion process. This was the part where all the heavy-lifting actually happened. Here we covered:

  • Creation of employee’s complete employment history : Some countries provided the historical data along with the present data, while for other countries these were provided in two separate files.
    We considered the National ID #s (as those would definitely be unique for each employee) to populate the complete employment history.
  • Assigning unique Employee IDs : Employee IDs were handled differently in each region’s source data. In some cases:
    • A new employee ID was created each time an employee was Rehired or Transferred,
    • While in other regions only cases of Rehire called for an employment number change, or vice versa
    • Some regions had different employees with the same Employee ID, but in different legal entities of that country.
      Our scripts, after populating the history for an employee with their respective National IDs, assigned each employee a unique ID as per a predetermined numbering format.
  • Handling source system logging issues : This was probably the most difficult and excruciating process for us. Neither did the client know about where or how such data issues had happened, nor did we understand that those were actually issues until we had spent a lot of wasted hours trying to decipher ‘what went wrong!’.
    The irony is that eventually, it kind of became a mandate for us to suggest to the client on how the data might have had been logged on their legacy system and how they should correct it!
  • Combining multiple lines of change records into a single change record : Yes, pivoting on the date is what we thought first for this action too. But apparently, nothing is as easy as it may seem.
    There were cases where we saw multiple changes on the same date, but which needed to be displayed as separate change records in Oracle.
    The procedures were eventually made to self-learn using ML techniques and correct such issues.

Oracle HCM Cloud Implementation Deadline & Coronavirus pandemic

Data migration was a part of the bigger process of the Oracle Cloud HCM implementation process, but the critical thing was that it was timebound!

When it comes to HR platforms deployments, we have always seen that the timelines are never-ever negotiable.

This is mostly because the source system loggings would have been stalled owing to the new platform migration plans. Any subsequent delays in that deployment could mean loss of data, a lot of manual process and further complications that cannot be penned down in an article.

The baseline was that the implementation needed to go-live on the date as planned. Period.

Following were the high-level processes that we put in place to ensure on-time delivery:

We split our teams to work in different time-zones based on the region of employee data they were processing

As already mentioned, the end goal was to not waste a productive day and try to sync up at the earliest with our respective counterparts – working as owners in their respective country of origin.

Further there were 2 scrum calls setup daily with the client at the US start and end of day, to ensure business continuity.

Multiple rounds of pre-production conversions were planned

There was large data variance in source data each time it was exported out to us by client region.

We realized that we would find ourselves in a better position to understand all the type of issues that we should expect,  only if there were multiple rounds of conversion iteration.

As a result, we worked out 3 rounds of E2E conversion runs before the actual production.

Proper documentations were maintained

Needless to say that when a project has around 5-10 decision-makers in the process, it’s mandatory to document every discussion and decisions.

As a result, we decided to keep a track of all of the following for process-continuity:

  • Rules that were incorporated into the SQL SP’s
  • Mapping fields with their respective values
  • Issues observed in data along with their respective solutions/workarounds
  • Change requests that came about as the project progressed
There’s no wealth without Health

A couple of weeks before the Government of India enforced a lockdown due to COVID-19 outspread, we @Zinemind actually collectively decided to start working from our respective homes. Because, hey, who better to set a good example for the rest of the world than technology people like us!

We all are connected to each other in ways better than ever before, so we figured it was best to get to the safety of our own homes before concentrating on the general good we can bring about to others. Peace.

Did you go through some similar experiences in your project cycles? Or,

Is there some other interesting way you observed your company handle project release cycles during the coronavirus pandemic?

We would love to hear you! Please feel free to leave your thoughts in the comment section below!

Thanks for reading.

WE WOULD LOVE TO HEAR FROM YOU