HR Confidential Data Warehousing for You — Using Excel, Step 2 — Import your .csv

Refer to yesterday’s blog on how to get this process started by generating a .csv file out of your HRIS.

Step 2:  Link Excel to your .csv file.  The steps are like this:

• First, Open Excel, new blank workbook.  Top of screen click DATA, now right under DATA click Connections, Workbook Connections window opens.  Click Add, then Browse for More button in lower left hand corner.  Find your file in the file manager and click on it.  Click Open.
• “Text Import Wizard” dialogue box opens.  File type “Delimited” should already be chosen, so skip it.  Click the box beside “My Data Has Headers” if yours does (which I hope it does or more work is in store).  Click Next.
• “Text Import Wizard Step 2 of 3” dialogue box opens.  Your “Data preview” at bottom should show your fields separated by vertical lines, and each field should be consistent with the column header.  If not, check the box beside the proper Delimiter at the top of the screen.
• Click Finish, then Close the last Workbook Connections dialogue box if your file name is shown properly there.

Second, it’s time to link your connection to a data model. Your workbook still looks blank.

• You should still be on the DATA tab.  If not, click DATA.
• Click Existing Connections.  The “Existing Connections” dialogue box opens, and click on your filename, then Open.
• “Import Data” dialogue box appears.  Click the box beside “Add this data to the Data Model“.  Now go up and click the bullet beside “Only Create Connection“.  Click OK.  Excel should now work a bit, will tell you “Loading Data Model” on the ribbon at the bottom of the screen.  Yep, your workbook still looks blank, but you have a data model loaded.

Third, create your display device.  I prefer Pivot Tables because of their flexibility, and also their ability to show the same data entirely differently for various managers or analyses.

• Click INSERT, Pivot Table in left corner of toolbar.  Click the radio button beside “Use an external data source” and then click the “Choose Connection” button.
• Under “Connections in this Workbook“, pick the item that has a “can” looking icon instead of an Excel icon (the “can” is Microsoft’s icon for data source, in this case it’s your data model).  Click Open.
• Back to the “Create Pivot Table” box, click OK.

What you should be seeing at this point is the structure for creation of a pivot table.  A sure sign that this is working is to look into the “Pivot Table Fields” box at the right side of your screen.  If you see your file name there and a list of fields below it that are the fields you intended to get out of your HRIS, this thing is working!!

There’s one more step before we forget:  Let’s make the automatic update of data happen.

Fourth and final for today:

• Click DATA, then Connections, and the “Workbook Connections” dialogue box appears.  You should see the names of your HRIS file and the name of your Data Model.
• Click the name of the HRIS file, then “Properties“.  Under “Refresh Control” click the box beside “Refresh data when opening the file“.
• Repeat the last step for your Data Model also.
• By setting up this connection to automatically refresh every time you open the file, you’ve established the method for your data to be current.  What you need to do on the HRIS end is to have your report run automatically every day or week or however often you’d like, and then overwrite the file name that you used for your original .csv file.  Most HRIS systems can do this; if not, ask your IT representative or the vendor how to set up automatic batch files.  (Or surf the web; it’s out there somewhere.)

Today’s blog is not about how to use Pivot Tables.  But as a quick starter, if you go back to your pivot table tab, you can click on a field and see what happens.  I recommend choosing an organizational field, like Unit, or Department, or Location – something like that.  Your organization units should display down the left side of your screen.  Then click on the unique employee identifier, like the ID number or Social Security Number.  What happens?  You just have gotten a quick count by organization unit.

Building the pivot table reports to show what you want will take some effort.  (Another blog, another day.)  But once they are built and working properly, dumping external data into them is a breeze. (You’ve already set it up to dump automatically.) Plus we can copy your functioning pivot table, make some modifications that make it look entirely different for another client, and then every time you open the workbook that one will update too!

Now, unfortunately, IF your data didn’t have headers, that creates problems for this whole thing because Pivot Tables need headers. Two reasonably decent options:  1) go back to your HRIS and figure out how to make it print headers into your report.  2) When establishing the connection under the Second step above, you can skip the “Add this data to the data model” part and tell it to put the data in the “Existing Worksheet” (radio button).  Tell it to start in $A$2 instead of $A$1, which will give you a blank line above your data.  Now you are stuck looking at all of your raw data, which the data model avoids, but we can make this work.  Type your data headers on top of the columns (don’t miss any). Select the entire worksheet (click the triangle to the left of “A” and above “1”) and click INSERT.   You can now continue with the Pivot Table as above; you won’t have to use an external connection because it will use the data you’ve just highlighted.

Over the past two blogs on this subject, we’ve learned how to output from the HRIS into a .csv file, then how to link your .csv file into a data model in Excel.

Upcoming post:  We will form this mass of data into some meaningful reports using pivot tables.  They are incredibly powerful and once you learn them you’ll want to use them again and again!!  I’ll even share an example file or two with you.

One more housekeeping item:  We said this is helpful because it helps keep your confidential data in your hands.  Now that you’ve linked your spreadsheet to your .csv file, this data can be accessed by just about anyone who knows how to build a pivot table.  So put a password on your Excel file.  Use the File – Save As option,  when file name appears, you’ll see a Tools button beside the Save button.   Click Tools, and General Options, and a password option appears.  Save your file with your new password.

My one and only disclaimer on this:  I’m a reasonably strong Excel user, not an expert.  If for some reason this doesn’t work on your machine, comment me back and we’ll work it out together.  I’m using Excel 2013 on a Windows 8 machine and I’m pretty sure the Data Model doesn’t exist prior to Excel 2013.  There are ways to do similar data importing and updating in prior versions but that will take some work.    

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.