The journey of a thousand miles begins with one step.

—Lao Tzu

In the summer of 2008, a group of employees filed a lawsuit against a multisite health-care system. (I call that organization Ice Station Zebra Medical or ISZM here, although it’s a pseudonym.^* )

The crux of the matter: ISZM had systematically underpaid overtime to thousands of its current and former nurses over the past decade. The employees’ attorneys alleged that ISZM owed their plaintiffs potentially millions of dollars in back pay, interest, and penalties. In the process, ISZM may have violated the Fair Labor Standards Act. Beyond that, the lawyers intimated that the U.S. Department of Labor might be interested in what was going on there.

PAYING NURSES

Odds are that you don’t know a great deal about paying nurses. I doubt that you’ve ever thought about it. Sure, you imagine, they can take sick, vacation, and holiday pay like the rest of us. That’s true, but things quickly diverge. No, the process is not “rocket surgery” and it hardly rivals Google’s almighty search algorithm. Still, paying nurses is far more complicated than paying the average layperson.

With rare exception, hospitals do not compensate their nurses based on fixed, 40-hour “regular” workweeks, occasionally tapping into their paid time off plans. On the contrary, many other factors are at play with these folks. (For this very reason, most large health-care organizations have purchased and deployed proper time-keeping and scheduling software such as Kronos or Ceridian. These applications rely on complex matrices that account for many scenarios, and ultimately get these numbers right.) For now, suffice it to say that nurses typically receive different rates of pay and/or premiums based on:

• Time worked (shift differentials)
• Length of shift (8 versus 12 hours)
• Whether they needed to be available while not at work (on- call pay)
• Whether they “floated” (traveled to different hospitals)
• The specific days on which they worked—that is, holiday, weekend, etc.

Table 10.1 shows fictitious payroll data for a nurse on a single day.

Note that the five records in Table 10.1 reflect only a single nurse’s pay on a single day—and a simplified version at that.^* Multiply this number by 250 workdays per year by thousands of nurses for, say, 10 years. It should be no surprise that payroll tables in large hospital systems can easily reach tens of millions of records.

Even against this backdrop, you might think that responding to the overtime lawsuit would be a simple matter. Couldn’t an ISZM payroll manager or finance director just click a mouse a few times? Couldn’t a system’s standard report easily determine how much employee overtime the organization paid its employees? More generally, didn’t ISZM possess the data to nip this lawsuit in the bud?

Again, you would think so—and you would be spectacularly wrong. Because of myriad system, data, personnel, and organizational issues, ISZM was hamstrung. It could not answer basic data-oriented questions such as:

• Which employees received overtime pay, when, and how much?
• Did ISZM pay overtime to its employees correctly?
• Did different hospitals in the system pay overtime consistently?
• Finally, what was the difference—if any—between the overtime that employees actually received and should have received? And could the hospital prove that to a bunch of attorneys chomping at the bit to go to court?

ENTER THE CONSULTANT

This is where I came in. At that time in my career, I had worked extensively with a number of leading HR, payroll, and timekeeping applications, including several that ISZM used. I had done plenty of “front end” work in the form of system configuration and testing, troubleshooting, and end-user system training. Unlike most functional consultants, however, I was skilled at extracting data from—and manipulating data in—contemporary relational databases (aka the “back end”). I knew how to write complex reports and identify data-related issues.

Even better, I had worked with ISZM’s finance director Terri (another pseudonym) on a previous engagement. We got along well and she held me in high regard. Terri contacted me for help and asked if I was available to assist her in answering these pressing questions—and fast.

It turns out that I could, but it would not be easy.

Over the course of four months, I talked to ISZM’s different payroll personnel. I read arcane pay sections of union contracts that specified in excruciating detail what counted as overtime and what did not. I began to understand ISZM systems by consuming policy documents and different data dictionaries.^* I try to locate entity-relationship diagrams^† of ISZM’s legacy systems. Sadly, in many cases, they didn’t exist.

You might be wondering why I had to go hunting. Most of ISZM’s hospitals had used different systems and pay codes in the past. Because of this, I couldn’t just pull consistent data out of a single database. I knew that I would need to create my own translation or “XLAT” tables to compare apples to oranges. I built week-specific tables because different hospitals abided by different pay and work periods. That is, Hospital A’s pay period might end every Friday while Hospital B’s pay period always ends on Tuesday. Finally, to maximize speed, I created several database views.^‡

I won’t bore you with any more technical details, but my project at ISZM was no picnic and the stakes were high. For instance, a simple date error, transposed value, or mislabeled or missing pay code could mean tens or hundreds of thousands of dollars paid out incorrectly—or not paid out at all. If Terri couldn’t get a handle on the situation, she may have found herself out of a job.

But where to begin?

USER STORIES

After meeting with ISZM personnel and understanding their needs, I developed the following user stories:

• As the head of finance, I need to be able to run summary reports to determine how much money each department owes.
• As the head of finance, I need to be able to run department detail reports to answer executives’ questions about specific employees.
• As the head of finance, I need to be able to run individual summary reports to determine how much money we owe each employee.
• As a payroll manager, I need to be able to run individual detail reports to answer employee questions.
• As a payroll clerk, I need to view employee payroll information by data range to answer employee questions.

For a project of this complexity, I never considered using the Waterfall method. Boiling the ocean was not an option here, and I wasn’t a fan of the technique anyway. Even an experienced system consultant like myself could not sit in a room, work independently, and emerge two or three months later with the answer. A single missed assumption or misinterpreted business rule might render my entire model moot and send me back to the drawing board. Plus, I wanted Terri to know what I was doing and why—at least at a high level. If I was on the wrong track, she was uniquely qualified to tell me so.

In short, I needed to employ Agile methods.

Every week I met with Terri, updating her on my progress and pending issues. Beyond that, I would find time on her schedule if I reached critical points that necessitated her input. Yes, looking at reams of nurse payroll data provided me with valuable insights into ISZM’s pay practices, but I lacked critical institutional knowledge that only Terri and her staff could provide.

By the end of the first month, I had developed an admittedly rudimentary application, shown in Figure 10.1.

In keeping with Agile methods, the first iteration was simple by design. I knew that I had more user stories to tackle, but they would come later. I gave Terri several detailed reports on sample nurses to see if my assumptions were sound. It turns out that they were.

Of course, Terri needed to run payroll reports on groups of employees. She wanted department-level reporting by date range. Confident that I was on right track after version 1.0, I added that functionality to the next iteration. Figure 10.2 shows version 1.1 of the Access application.

As she nodded in approval at the numbers, I felt emboldened to add to the back end new hospitals, departments, data sources, employees, pay codes, and the like. On the front end (i.e., the reporting dashboard), I kept adding reports and data that Terri and the other ISZM users requested. For instance, Terri needed to verify which nurses and departments did not receive overtime in a given time period. I built those reports and added them to version 1.2 of the app, shown in Figure 10.3.

Terri’s stress level started to diminish. She even laughed at a few of my jokes. By involving her throughout the development and analysis process, I was cementing her initial trust. The same cannot be said about another group.

AGILE: THE BETTER WAY

To keep the story simple, I intentionally omitted one key detail of my work at ISZM until now: I wasn’t working alone—not exactly, anyway. When I started, ISZM had concurrently hired a prestigious and pricey consultancy to try to solve the same employee-overtime riddle. (I call that firm Badger here, but it’s another pseudonym.)

Terri’s rationale made complete sense:

• For such a complicated problem, why not separate church and state?
• Why not put two knowledgeable and independent entities on the same project?
• Hiring a technology and audit firm showed the plaintiffs’ attorneys that ISZM was taking the issue seriously.

Badger dutifully sent a team of three full-time consultants to ISZM. A partner also dropped in from time to time. Unlike me, the Badger folks followed the traditional Waterfall method and attempted to boil the ocean.

As the project progressed, I began to sense that Badger’s hired guns were struggling mightily. These consultants were friendly but clearly out of their element. Frequent pop-ins to my office clued me in. When I politely reminded them that we were supposed to be working independently, they demurred. Badger’s project manager even tried to charm me with obvious compliments and promises of future consulting opportunities.

Terri couldn’t believe that a single person (me) was making so much progress while a team of three supposed experts was floundering. Badger’s weekly bill was more than five times higher than mine. By the end of the second month, Terri’s patience was wearing thin.

Ultimately, Badger failed miserably. Its consultants never had a chance to succeed: The consultancy didn’t send the right folks for the job and, to boot, didn’t follow Agile methods. Mismatched personnel who follow a dated game plan rarely get the job done.

RESULTS

Alright, I’ll cut to the chase. It took me nearly four months, but I solved ISZM’s thorny data problem. I created a user-friendly tool that allowed nontechnical users to query remarkably complex data, receive answers to their questions, and export the results to any format they liked. In Scrum terms, I completed each user story. Terri was pleased with my work, and although I no longer do that type of consulting, I can count on her as a reference.

Ultimately, my Microsoft Access application pulled together dozens of data sources and easily used 100 different tables—many of which I needed to build myself given the nature of the project. The final routine took more than three hours to run for the entire hospital system, but anyone could run it. Knowing that Terri and her employees were no database mavens, I made sure that she could easily run reports by employee, department, hospital, and date range with a few keystrokes.

LESSONS

Remember two things from this chapter. First, Agile methods can work on all sorts of data-related projects. That is, they don’t need to fall under the umbrella of proper analytics. Second, small can beat big. A single person with superior system knowledge employing more contemporary methods can accomplish more than an entire team whose members cling to old ways. That is, by embracing the power of small batches and rapid iterations, David can defeat Goliath.

NEXT

To be sure, analyzing messy historical data under the pressure of a possible class-action lawsuit is stressful. Sure, the matter was still private, but the meter is always running and a single mistake can cost millions of dollars. However, solving a largely retrospective data or analytics puzzle in private isn’t the same as dealing with a real-time, racially charged crisis unfolding in the media.

The next chapter shows how one company skillfully handled itself in the midst of such a maelstrom.