I’d like to recommend some ways HR can better manipulate payroll and HRIS data in a confidential manner, and keep it in your control and current. Sometimes as HR professionals we don’t know how to do this sort of thing and it’s hard to ask for IT support with the subject being confidential data. So what we do is we figure out a basic system using existing reports, sometimes keying it in to a separate spreadsheet for the current project (like yearly incentive or Merit increase processing); then in six weeks’ time it’s useless data that is already outdated by people changes in the business. Time to learn one process that can be re-used and updated in a flash, with a single set of skills that you can use with an entirely different HRIS system in the future.
I’m going to propose to you a way to change this scenario for the better — we will learn how to develop a mini-data-warehouse that feeds off your current HRIS and updates automatically to current data when you open it. It’s going to take some learning and a little work. It’s too much for one blog, so we’re going to talk about it in a series: first, we’ll learn how to export data from an HRIS database in a manner useful for Excel; second, we will learn how to import the data into Excel and set up an automatic data feed that will update every time you open your spreadsheet; third, we will show how to create some simple tables or Pivot Tables that can present your data in a meaningful way; and fourth, we’ll demonstrate how to create multiple reports / analyses from the same set of data so you don’t have to go into your HRIS’ report generator every time, and you can create a quick HR dashboard of meaningful information ready for your viewing. We’ll look at one more advanced subject following — the idea of knitting together dissimilar data sources into this same dashboard to enhance your reporting (e.g., mix payroll and HR data from different databases into your Excel dashboard).
Sound daunting? It should, but I believe we can step through the key essential functions to do these things and help you sweep away the unnecessary stuff. I’m sure there are more technical and better ways to do this, but I’m trying to teach HR folks how to create your own and keep current, confidential data in your control and presented in the manner you or your chief clients want to see it.
I. Seek the .csv data output option in your HRIS report generator. Although many different systems produce a variety of different reporting options, almost all will have some option that allows you to output report data into a .csv (comma separated variable) format. Go into your HRIS report generator, generate a report with bunches, just bunches of employee data with employee identifiers (whatever you use, SSN, payroll ID, clock number, whatever, so you know who’s who and who additional data like clock punches should belong to), and tell your HRIS reporter to output report data in .csv. Why csv? Because nearly everybody’s system has the option, it skips all the report generator stuff about formatting, etc., and it takes up minimal space in the file so Excel can import it quicker than many other file formats — and when you’re dealing with a bunch of data, fast is good. I like fast.
Practical tip/trick #1: “comma separated variable” is just the name the industry uses for the file type. It means that for every field (piece of data) you have within an employee record (think ID, Last Name, First Name, start date, etc. all collected together) that it’s separated from the next field by a comma. But you can use other field separators and still meet .csv standards, and I recommend that you use a TAB. First reason for this is that in many payroll systems, annual salary has a comma in it, which means that halfway through “52,334” the system reads that as two fields, which screws up everything after that comma. Or employee name “Jones, Edwin R.” in your mind is one name, but in .csv it’s two fields. Tabs, on the other hand, aren’t that normally used in employee data files. Second reason is that there are options within Excel that make using the Tab really easy, and I like easy.
II. When in doubt, dump it. For most HR applications, I don’t recommend exporting the weekly payroll stuff, because you can have hundreds of records per employee (and usefulness of how much a guy paid in local taxes last month is pretty low). However, payroll header information (like name, address, work location, base rate of pay, YTD overtime and pay, etc.) might be useful, even if you don’t know that you need it for your current report : when creating a data warehouse, export extra stuff anyway. Later in this project you will be looking at your data saying, “wow, now that I can get this far with the data I’ve got, if I only had these other data points I could really make a dynamite report” — and it’s not too much for the system or your spreadsheet to take extra stuff at this time. We will set up your data feed as an external data source in Excel so you won’t even see the extra stuff or know that it’s there.
This is also an attempt to focus your knowledge base toward Excel rather than five or six different HRIS report generators (heavens, I so wish there were a standard for this!! In one of my recent HRIS systems, we had FIVE different report generators to learn in the same package!!!).
III. Save the export file someplace convenient and confidential. You’ll need it for the next step. Give it a standard name that you will remember. If it’s truly confidential, I tend to store it on my computer’s desktop, because with most employer’s servers the user account backup does not include the desktop. IT folks will tell you, “but then your file never gets backed up”, but I don’t care because when we set it up to run automatically out of HRIS I’ll get new data anyway.
I’m a big fan for allowing management to make strategic decisions based on real data and current information rather than perception and opinion. This is the first step in helping us get there.
Later post, we’ll import the .csv information into Excel and set up the automatic data updating routines, and give you a bit of a teaser for what you might be able to do with it.
HR Strategy and Tactics 