Create your own confidential mini-data warehouse using Excel

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.

Beware the ‘good idea’ in business management

Sometimes things in business just seem like they are a good idea at the time, and we make tentative arrangements to move forward on something without truly understanding what we’re getting into.   For example, although I’m a HUGE FAN of Lean Manufacturing and Six Sigma initiatives, your own people left unchecked by a good mission and direction can absolutely bury you in additional processes and paperwork that have marginal value to the business — making the net effort actually a cost rather than a benefit.

Our example for today are time and attendance systems, much simpler than tackling Lean or Six Sigma (but stay tuned!).  Time and attendance systems seem like a good idea.  Establish a no-fault attendance policy (points based on absence events), communicate it to all employees, tie it to your payroll system to track absences automatically, and you can spit out a report any time or day that identifies exactly where everyone in your department stands as far as attendance records go.  Sounds like a good idea, right?

Well, it is, but not for everyone.  You have to be aware of what this means.  First, “no-fault” attendance policies means exactly that — no fault — if you’re not where you are supposed to be at the right time, it’s an infraction.  Problem begins when you’re listening to very valid employee or employee rep arguments starting with, “I was in the hospital”, “My house was on fire”, “Automobile wreck on the way to work”.  All good, all valid, and it begins you down the path of identifying lesser point infractions (or “freebies”) for such emergencies, then of course there are zero-point infractions for funeral leave provisions, jury duty, FMLA (complex subject of future post), and other reasons.  You’ve begun down the path of identifying some events more “acceptable” than others, which seems like a good idea at the time, but eventually can lead to a morass of similar-sounding events with or without employee cause that can jack up the number of actual absences significantly without incurring penalty for the employee.  Point is, you need to set a standard of non-erosion of a good policy once you have an acceptable program that works and meets the needs of your business, or you may be worse off than prior to starting it.  One solution I have seen work is to pair up a commitment to not reduce points / add freebies with a long-range plan of reducing points over time:  16 points per year this year, in two years’ time 14 points available, in two more years’ time 12.  Long notice, advance communication, people understand it.

(If you’re not familiar with no-fault attendance policies, in short they work like this:  Employee absence = 1 point.  Absence with call-in less that 1/2 hour prior to start of shift = 2 points.  Absence with no-call = 3 points.  Tardy = 1/4 point < 15 minutes, 1/2 point > 15 minutes.   Other infractions like this; maximum 16 points in a rolling twelve-month period, other provisions to roll off points for excessive overtime or freebies for excused absences, etc.  You pick your own maximum, your own number of points per infraction, and your own progressive discipline warning-notice procedure at various levels in the process.)

The other HUGE watch-out on time and attendance systems is the attendance tracking.  You see, when an employee clocks in or fails to clock in, for the system to work properly it needs to know what the expected outcome should have been.   So if an employee clocks in at 7:15 am, the system needs to know whether or not that was the employee’s expected start time, or whether he’s 15 minutes or an hour and 15 minutes late.  Or whether he was scheduled at all.  Therefore, the schedule should be in the system in advance of the clock punch.

For some companies, this is no big deal.   For others, look out.   In your company, schedules may be set by department, work area, or classification.  You may have rules on how long in advance that schedule will be posted, and you must put that schedule in the system so it knows who is supposed to be there, and when.  Then you have to account for employees who are out on vacation or giving you notice of Jury Duty or Funeral Leave, and you must update them as well as their replacements.  Plus if overtime is involved, this must also be logged into the system as an expectation (if your attendance program applies points for failure to show up for scheduled overtime).  Some companies have an overtime-distribution practice, to equalize among all employees in the classification or department, and first you need to determine how many hours they have previously and then identify who is eligible; canvass the employees if it’s voluntary overtime, and then tell the time and attendance system who is scheduled for what hours.

If you’re thinking that there is a huge advantage in employee fairness and equal treatment, there is: individual supervisors don’t have as easy a time overlooking a disciplinary action for a favored employee.  However, they’ll soon figure out that the way to avoid no-fault points for the favored employee is to simply take him/her off the schedule, and the system will be unaware of an absence, applying no points.   You also may view the benefit of taking attendance tracking off the desk of the supervisors, which is a plus; please remember though that you will add a duty of ensuring that the schedule is maintained up-to-date in the system for every shift.  You’ll probably hire an administrative assistant (or two, or three) to do nothing but schedule and attendance tracking, and they will spend half their time chasing down supervisors who haven’t kept their schedules current in the system.  Good news is, you’ll learn a lot about which supervisors can keep the administrative work in line as well as their supervisory work.   One added point: if you are the senior manager or HR representative, ensure that you have a case review of each employee pending termination under such an automatic system.  Even well-designed automatic systems left unchecked can err from time to time.

IF your scheduling system is simple (including overtime), and IF you can keep a good no-fault attendance system from eroding to worthless over time, and IF you can establish some kind of layered-audit system to have the program tracked over time (for mismanagement by individual supervisors or mis-application of points e.g. FMLA-covered absences, plus termination verification), then a no-fault attendance tracking system might just be a huge benefit in terms of time and effort involved and perceived fairness to employees, and it might just be a good idea for your business.   If your scheduling systems are complex, and you have a strong voice from employees challenging every point application and/or constantly seeking new freebies, be very careful that you fully understand that your perceived benefits might just be outweighed by the cost of the system (including additional headcount to manage it), and it may not be not such a good idea after all.

I have implemented these programs successfully and would do it again.  My point is, evaluate carefully, do your homework, so you know exactly what you’re getting before you implement the “good idea”.