By Alan McEwen
The poet John Donne famously wrote that “no man is an island.”
The same is true for payroll. Payroll often requires employee-related data from, or passes employee-related results on to, other applications such as time and attendance or the general ledger. Even if payroll is part of an integrated human resources information system (HRIS), it’s often common for employee-related data to be required in other parts of the organization.
Broadly speaking, there are two distinct approaches to passing data between applications: applications that are integrated versus those that are interfaced. Integration typically means employee specific fields, such as name and address, are stored only once within a database and are directly accessed by each application that shares this data. By contrast, interfaces are often employed to move data between applications sourced from different vendors. Interfaces usually involve manual processes to export data from one application, modify it and load it into the other.
When employee-related data is required across multiple applications, the overwhelming need is to ensure all such applications are using the same data. From this perspective, integrated applications are a better practice since integration usually means there is only one copy of this data, shared as needed. However, where integration isn’t an option, interfaces are required in order to ensure that data between employee-related applications is properly synchronized.
All interfaces work by capturing changes in employee data in one application and sharing these changes with other applications. When data is interfaced between applications, users make changes in one application and data in all the rest are updated through an interface. For example, when payroll is interfaced to HR, someone has to decide where employee demographic information will be entered, payroll or HR, and which of these will only be updated through an interface. While not impossible, it’s far more difficult to construct interfaces where employee data can be entered in both places simultaneously, yet reliably shared across both.
The key decision when designing interfaces is what should be the time lag between users changing data in one application and this changed data becoming available across an interface.
Virtually no time lag is experienced if the very act of users changing data is what begins the interface to other applications. Most relational databases support what are termed “triggers.” Triggers are custom routines called by the database server when applications make changes to data stored in the database. For example, a trigger, installed on employee address fields, could be set off by the database server when an employee address is changed.
For this scheme to work there has to be something listening to the trigger when it goes off. The best practice for such listening is termed a “web service.” In a web service, an HTML server, such as open source Apache, stands between the database trigger and any interfaced applications. When the HTML server hears the trigger’s request, the server responds by providing the custom “service,” which updates the employee address in the interfaced applications. In this scenario, there are two custom pieces of code: One, the database trigger used to call the web service and, two, the “service” called by the HTML server, which does the actual update in the interfaced application.
The advantage of such a trigger-web service approach is that, once implemented, it functions automatically, driven by payroll users changing employee data. Since the trigger operates at what is termed the database level, it works no matter how changes are actually made.
For example, the database trigger will work the same, whether employee data is loaded during implementation or modified by users on an application data-entry screen. The alternative to a database level trigger is program code, internal to the payroll application and run when users make changes in an employee data entry screen. The disadvantage of this application level alternative is it must be replicated in each data-entry screen on which users could potentially change employee-related data. For these reasons, the combined trigger-web service approach is a best practice.
The alternative to interfaces tied to user-data entry is to run interface routines on a periodic or scheduled basis — for example, once a day or at the end of each pay period. The frequency on which the interface is run determines the time lag involved.
One common approach to such periodic interfaces works something like this:
Step 1: Changes since the last time the interface was run are exported from payroll.
Step 2: If there are differences between the data formats in payroll and the interfaced application, these are adjusted after the export.
Step 3: The potentially modified data, exported from payroll, is loaded into the interfaced application.
Can you figure out the weakness in this logic? If, for any reason the load in step three fails, and the time stamp used in step one is updated, then whatever payroll changes were included in this attempt to run the interface will be lost forever.
As a result, the following rough logic is a better practice:
Step 1: Do a complete extract of the related data from both payroll and the interfaced application;
Step 2: If necessary, adjust for any formatting differences, so that both extracts have the same format and are in the same logical order (for example, all dates are “2012/08/29”);
Step 3: Using a scripting language such as PHP, load the modified data in both extracts into what are termed “arrays.” An array is a set of data in columns and rows, just like a spreadsheet.
Step 4: Walk through the two arrays in order, looking for differences and create a set of payroll records to be added, modified or deleted from the other extract.
Step 5: Make each change in the interfaced application identified in step four.
The primary advantage of this dual-extract approach is that it is a better guarantee that payroll data stays in sync with any interfaced application. If for any reason such an interface fails, it can simply be run again, as many times as needed.
In fact, the weakness in periodic interfaces that only extract changed records from payroll is shared by user-data entry driven interfaces. Both are vulnerable to payroll getting out of sync with interfaced applications, if for whatever reason the interface routines fail to run successfully.
That’s why, even if trigger-web service interfaces are used to provide immediate access to payroll data, it’s still a best practice to back these up with a periodic interface based on the dual-extract approach.
Alan McEwen is a payroll consultant and freelance writer with over 20 years' experience in all aspects of the industry. He can be reached at firstname.lastname@example.org, (905) 401-4052 or visit www.alanrmcewen.com for more information.