Mark Norton 3 August 2014 11:58:20 a.m.Message to Novopay!!
Audit and remediation of catastrophic failure in core applications is causing headlines on both sides of the Tasman, with payroll a particularly popular subject. Yesterday’s generation of systems failed to manage the underlying business complexity as it changed over time. So why would we think that using these same approaches to audit and remediate their own failure is going to achieve a better outcome today?
Perhaps it is time to change the approach. In fact, a change in how we think about systems and complexity completely.
This blog introduces two recent IDIOM projects that expose the real complexity involved in calculating and maintaining the entire life-time history for complex entities. The first entity is a pension account, with some account lifetimes exceeding 30 years; the second is an employee payroll account limited to the last 10 years of transactions, but extending to 40 years of payroll data in its calculations.
In both cases the underlying business entities (pension account, employee) have survived millions of events throughout decades of change. Equally importantly, they were transformed through multiple systems replacements and dozens of changes in their event handling processes. The result: loss of data integrity, and an inability to recover it from within the system itself.
IDIOM was requested to assist using its tools and approaches to recalculate the correct state of the entities from scratch (i.e. over decades), and to generate any entries required for remediation to reset the current state. For the sake of clarity:
· to recreate every version of every process throughout the history of these entities (we can’t say systems – the entities survived multiple systems);
· to reprocess up to 30+ years of data to arrive at the correct state as at today;
· to difference the calculated and actual data and generate remediating transactions.
This ‘time-spanning’ view of processes is not easily achieved in traditional systems. One underlying reason is the relational database - how do you version a relational database? Answer: you don’t – you migrate the data from version to version. In which case, how do you keep alive the full complexity of multiple, concurrent generations of event processing versions? Answer: you can’t!
The IDIOM toolset is able to acquire and absorb the full extent of entity data for the life of each entity; to refactor it into a ‘whole of life’ data schema (complete with accommodation of even such subtle changes as precision over time); to re-apply the full lifetime series of events to the entity and recalculate all prior and current event outcomes; and to match these recalculated outcomes with actuals to calculate the required remediation (if any). The examples follow:
Pension (Defined Benefit) Scheme
The aim of this project was to build a decision model to:
- Initially, test the changes made to the legacy system to support the changes made to the Trust Deed of a Defined Benefits scheme
- Eventually, replace the benefit calculation component of the legacy system that supports the Trust Deed of the Defined Benefits scheme.
- Retrieved the required base source data from the legacy system (e.g. all working hours and salary history for all members for the lifetime of their membership in the Defined Benefits scheme – sometimes in excess of 30 years)
- Calculated all intermediate components that make up the benefit calculations (approx. 40)
- Calculated all the specific benefit calculations (approx. 30).
- ~100,000 members in the Defined Benefits scheme, with total payouts due of circa $30billion
- Poor source data quality where minimal verification was undertaken at source data entry, requiring management of data quality issues in the model when retrieving base source data (e.g. an employment terminated with no record of that employment ever commencing)
- A data migration from a prior system to the current legacy system that created a number of data integrity issues
- Calculations based on a Trust Deed that has been modified and added to over time, resulting in inconsistent definition of some apparently similar benefits that therefore need to be modelled differently to meet the interpretation of the exact wording of the Trust Deed
- Only 1 person with a strong (but still incomplete) level of knowledge of the calculations and supporting data required
- Working from 4 documents (combined total of approx. 600 pages) that provided only a high level view of approx. 75% of the calculations. These documents provided approximately 20-25% of the detailed information required, and in some cases provided conflicting information
- Recalculating all member accounts “from scratch” – that is, from the commencement of their membership of the scheme, which for long standing members is in excess of 30 years – whereas the legacy system just applies an update from the last review (a period measured in months)
- A long standing member can have 30 to 40 separate periods of employment service that need to be worked through for the “from scratch” calculation, as a new employment service period commences if the member changes role, employer, payroll, service fraction (part time work), leave without pay, temporary incapacity, permanent disability, deferral from the scheme, leaving the scheme, re-joining the scheme, etc.
- Reconciling model benefit calculations for members with the legacy system benefit calculations, and identifying potential issues with the legacy system calculations – compounded by the legacy system not adjusting for past “errors” as it always works from the last review
- Incorporating many historical changes in the scheme over the past 30 plus years – e.g. prior to a particular date a calculation is undertaken in a certain manner with a certain set of rates applicable, which is then changed a few years later (with potentially a different set of applicable rates), and changed again at a further later date. Some benefit calculations have up to 4 or 5 of these historical changes in calculation method that need to be catered for
- Incorporating regulatory changes that the scheme had to support over the past 30 plus years – e.g. changes in required or permitted employer and employee contribution rates and the flexibility of those, such as ability to voluntarily reduce contribution levels in certain circumstances
- Incorporating the acquisition and merging in of members from other schemes over the past 30 plus years – where in most cases members of those schemes have the rights to maintain the benefit structures and rules inherited from those schemes
- Special treatment for periods of employment service commencing or ending during a leap year
- Special treatment for periods of leave without pay, temporary incapacity, and permanent disability during the employment service of a member – each of these three treated differently to each other, and in some cases differently for the same item in different circumstances
- The indexation by either daily or quarterly CPI of certain calculation components and benefit calculations in some different circumstances – e.g. just for a certain period, or from the date of a certain event forward or backward in time, or for a certain event only when other specific conditions apply
- The splitting of many component calculations into “Pre Scheme Date” and “Post Scheme Date” components based on different formulas and sub components that apply differently to all underlying factors (e.g. employment service) prior to the Scheme Date change, and post the Scheme date change
- The modification of many benefit calculations to utilise both the new “Pre Scheme Date” and “Post Scheme Date” components, multiplied by other components that differ, based on whether they are “Pre” or ‘Post”
- Projecting certain benefits forward to a member’s retirement birthday as well as current or historical calculations
- In some calculations managing and dividing base data (e.g. employment service) into separate periods based on up to 4 variable dates (e.g. Scheme Date, Calculation “as at” Date, service threshold date, retirement birthday) with different calculation rules applying for employment service in each “Pre” or “Post” period for each potential date period
- Management and alignment of rounding precision that changed over time, with calculations often multiplying 6 figure components based on salary and using small percentages (~ 1%) as a part of a calculation that multiplies 5 or 6 numbers
- Several calculations having 5 or 6 high level components, with each of those components having 4 or 5 levels of sub calculations, with each of these sub calculations also having up to 5 or 6 components, giving rise to individual formulas that include more than 100 separate calculations
- Each member to be processed as above in less than 2 seconds.
Payroll - Termination Reprocessing
The aim of this project was to build a model to:
- Reprocess all termination payments for people terminated between 1/4/2004 and 27/10/2011 to verify that they had been paid in accordance with the NZ Holidays Act 2003
- Retrieved the required base source data from the Payroll System Database (e.g. Employment Details, Allowances, Payments Made, Leave Taken, LWOP Days etc.)
- Construct an intermediate data structure suitable for the analysis
- Calculate what the payments should have been and compare these with payments made
- Produce a report with references to the individual inputs and the intermediate calculated structures to provide a detailed audit trail to support remediation payments (or the lack thereof).
- ~10,000 members terminated in the period, with total remediation pay outs of several $$million
- Poor discipline and procedures in the underlying payroll system resulted in varying data quality, including inconsistent and irregular data (e.g. leave taken following termination)
- Data structures and database keys changing over time as systems migrated
- Running different Calendars for different parts of the country
- Data to be analysed reached as far back as 40 years
- All nuances in the Holidays Act over the full term including definitions of Base Rate, Ordinary Rate and Average Weekly Earnings
- Creating an intermediate data structure representing each day the person was employed, to be marked with the amount the employee was paid that day in aggregate, or if leave was taken
- Calculated TOIL and Special Care Cash Ups
- Calculated Annual Leave, Long Service Leave, Shift Workers Leave and Statutory Holiday pay outs
- Compared the Calculated Amount to the Amount Paid
- The Amount Paid was not uniformly found and needed to be located from different places depending on time period
- Tuning and parallel processing took this down to four hours in the final run, using data extracted from an Oracle Database and processed by six parallel run-time processes
IDIOM is a member of the NZ Government 'Open Door to Innovation' program.
- Comments