Ensuring data survives the trip through cyberspace
Transferring employee information, whether it be pension or other data, between an employer’s HR system and the company’s pension and benefits carrier can be tricky. While most company’s IT staff are aware of these issues, many HR departments are mystified about the interface design process.
File transfers are, in truth, much more complex and generally beyond what would be expected of an HR or HRIS staff member. But a little understanding by HR of the basics can go a long way in answering questions from both IT and the carrier.
The first major step is to understand the data requirements of the carrier. Generally each carrier has a specific format for the data they wish to receive from their clients. Often this format is common to all clients and is designed to smoothly integrate the data into their system. The carrier will provide a listing of the fields of data for each employee and will specify the file format.
First, employers need to understand whether or not the file is designed to be an update or a full refresh. If it’s an update, the file simply adds new information to an already existing employee data file by, for example, adding contributions to a defined contribution pension plan each pay period. A full refresh replaces all employee data with the new values.
The process of transferring data is often referred to as an extract, transform and load (ETL). The data is extracted from the system, transformed if necessary to the required format and loaded onto the carrier’s system.
Extracting may be straightforward if the employer’s system has a good programming and ad hoc query tool. If data is being combined from multiple systems, such as an Oracle HR system and an ADP payroll system, the task becomes more complex. Transformation is the most difficult part of the equation. This generally requires programming or bringing in other values from outside the system. This is best left to the programming staff. The load is the carrier’s problem, but if the employer has done the first two parts properly, this is relatively easy.
XML — the emerging standard
In looking at the interface files themselves, there are three primary types of interface files formats. These are fixed, delimited and a new emerging standard — XML. A fixed file is composed of a number of data fields each strung out behind each other based on the file layout for each field. It might look like this
Firstname Lastname Contribution
John Doe 98.0000000000
Jane Smith 67.0000000000
The number fields may be filled with zeros for the full length of the field. In some cases text has quotation marks around it. The file may also have the field headers on the first row. A delimited file is much smaller as the field length is variable and each piece of data is separated by a comma, tab or some other character to show where one field ends and the next begins.
Firstname,Lastname,Contribution
John,Doe,98.0000000000
Jane,Smith,67.0000000000
The new, and much better, standard is using XML as a file transfer interface. XML is an enhanced version of HTML. HTML is hypertext mark-up language, the standard that allows Internet surfers to view items on a Web browser. XML, or extensible mark-up language, tells about the data in a file rather than telling a system how to display text and graphics. HR-XML is a version of XML specific to the HR field (for more information, visit www.hrreporter.com, click on “search” and enter article # 1662).
In an XML file the information shown above would be displayed as < Firstname>John< Firstname>,Doe,< Contribution>98.000< Contribution> . The receiving XML compliant system would automatically know where to put each piece of data.
Things to sort out
Other items an employer needs to consider are issues such as:
•Field length: How long should each data field be?
•Case: Does the receiving system want upper case, lower case, proper case or any combination?
•Decimal format: How many decimal places need to be included?
•Date formats: What is the format of the date? (1/1/2003 or Jan. 1, 2003?)
•Carriage return: Does each record have to start on a new line or can they be strung together and separated by another symbol?
•Field headers in first line: Do the field headers have to be included (as above) or should they not be present?
•Key employee identifiers: What is the unique employee identifier used by the carrier? Is it the employee number or the carrier’s own ID number?
Employers should also receive error reports which indicate what records were not processed and, ideally, why they were not processed. This will allow the company to correct the data file for the next transfer to the carrier.
John Johnston is a principal in Human Resource Management Solutions, a consulting firm specializing in HRMS strategy, systems selection and change management. He can be reached at (905) 825-4127. His column appears regularly in Canadian HR Reporter’s Guide series. Look for the Guide to HR Technology in the March 10 issue.
File transfers are, in truth, much more complex and generally beyond what would be expected of an HR or HRIS staff member. But a little understanding by HR of the basics can go a long way in answering questions from both IT and the carrier.
The first major step is to understand the data requirements of the carrier. Generally each carrier has a specific format for the data they wish to receive from their clients. Often this format is common to all clients and is designed to smoothly integrate the data into their system. The carrier will provide a listing of the fields of data for each employee and will specify the file format.
First, employers need to understand whether or not the file is designed to be an update or a full refresh. If it’s an update, the file simply adds new information to an already existing employee data file by, for example, adding contributions to a defined contribution pension plan each pay period. A full refresh replaces all employee data with the new values.
The process of transferring data is often referred to as an extract, transform and load (ETL). The data is extracted from the system, transformed if necessary to the required format and loaded onto the carrier’s system.
Extracting may be straightforward if the employer’s system has a good programming and ad hoc query tool. If data is being combined from multiple systems, such as an Oracle HR system and an ADP payroll system, the task becomes more complex. Transformation is the most difficult part of the equation. This generally requires programming or bringing in other values from outside the system. This is best left to the programming staff. The load is the carrier’s problem, but if the employer has done the first two parts properly, this is relatively easy.
XML — the emerging standard
In looking at the interface files themselves, there are three primary types of interface files formats. These are fixed, delimited and a new emerging standard — XML. A fixed file is composed of a number of data fields each strung out behind each other based on the file layout for each field. It might look like this
Firstname Lastname Contribution
John Doe 98.0000000000
Jane Smith 67.0000000000
The number fields may be filled with zeros for the full length of the field. In some cases text has quotation marks around it. The file may also have the field headers on the first row. A delimited file is much smaller as the field length is variable and each piece of data is separated by a comma, tab or some other character to show where one field ends and the next begins.
Firstname,Lastname,Contribution
John,Doe,98.0000000000
Jane,Smith,67.0000000000
The new, and much better, standard is using XML as a file transfer interface. XML is an enhanced version of HTML. HTML is hypertext mark-up language, the standard that allows Internet surfers to view items on a Web browser. XML, or extensible mark-up language, tells about the data in a file rather than telling a system how to display text and graphics. HR-XML is a version of XML specific to the HR field (for more information, visit www.hrreporter.com, click on “search” and enter article # 1662).
In an XML file the information shown above would be displayed as < Firstname>John< Firstname>,
Things to sort out
Other items an employer needs to consider are issues such as:
•Field length: How long should each data field be?
•Case: Does the receiving system want upper case, lower case, proper case or any combination?
•Decimal format: How many decimal places need to be included?
•Date formats: What is the format of the date? (1/1/2003 or Jan. 1, 2003?)
•Carriage return: Does each record have to start on a new line or can they be strung together and separated by another symbol?
•Field headers in first line: Do the field headers have to be included (as above) or should they not be present?
•Key employee identifiers: What is the unique employee identifier used by the carrier? Is it the employee number or the carrier’s own ID number?
Employers should also receive error reports which indicate what records were not processed and, ideally, why they were not processed. This will allow the company to correct the data file for the next transfer to the carrier.
John Johnston is a principal in Human Resource Management Solutions, a consulting firm specializing in HRMS strategy, systems selection and change management. He can be reached at (905) 825-4127. His column appears regularly in Canadian HR Reporter’s Guide series. Look for the Guide to HR Technology in the March 10 issue.
