Data spot check: Oh no, could it all be wrong?

A techie stumbles upon the perfect storm of a cranky system and dubious data -- and the troubling results of their union

financial figures magnifying glass tax finance report financial figures bank statement 000001825418

When an unstoppable force (faulty software) meets an immovable object (user error), what happens? Tough to say, but you definitely want to take a second look at your sales reports.

When I first started in IT 20-plus years ago, a lot of our programs were developed in-house and we, the developers, were responsible for our own programs and maintaining data integrity. In my observation, over the last decade competition has increased in the business software side where market forces have turned the emphasis onto price control of the package. As a result, many vendors have decreased if not eliminated their QC departments in an effort to drop development costs.

Then there's another problem: the users. You'd think it would be important to a business to ensure the data on which they base their decisions and initiatives would be correct. But doing so takes resources and often gets pushed lower on the priority list.

Wading through the data

I recently saw what can happen with lack of oversight from both software vendors and users. My job at the company was IT manager, and one of my duties was to run reports for the execs. In the past, I'd noticed discrepancies in the data entered by our company's sales reps and had made recommendations for putting software governance in place for creating new customer IDs.

However, management frowned about anyone dealing with the sales team and said they didn't see any reason to make changes. But the problems with this philosophy came to light once again as I worked to produce a report for end-of-year sales from our main accounting package.

We'd been running the package for years with many modifications made by the vendor's development team during that time. When we first installed the package, we ran it parallel for several months simply to verify the transactions and data. I'd tried to keep an eye on it ever since, but as the demand for more IT services ballooned along with manpower reduction it became next to impossible to keep doing so. Meanwhile, management wouldn't buy into hiring more worker bees.

I was assembling the data from our four major locations to show sales trends for the prior three years. Our package displayed only one year at a time, and the COO wanted to see all three years on one page ranked by top sales for the most recent year.

The data had to be scrubbed as some locations had used multiple IDs for the same customer. The sales team apparently would choose whichever number struck their fancy that day to enter a sale. My mind whirred as I thought someone should have noticed this; then again, I'd often been amazed at the accounting department's lack of data integrity enforcement. I made note to once again bring it to management's attention.

Once I had the data plugged into my structure, I produced a report that I hoped would meet the COO's demands. Before passing it along, I perused it to see if I could visually spot any anomalies. As I scanned down the page I saw a customer that had $0.00 purchases three years prior, $350,345 purchases two years prior, then $0.00 in the last year.

Run a report and check it twice

Thinking it odd, I checked the numbers I'd been given, and it reflected the data in my report. I fired up the accounting software and went to the customer maintenance module where I could view one customer at a time and eight years of sales history. The numbers for the last two years matched the numbers in the report I'd run. But the ones from three years prior was drastically different: Instead of $0.00, this history showed $256,312.

My heart was in my throat. How could this be? I reran the reports that I had been given, and they again matched my report. I could not see the coded back end of the form that delivered the additional sales onto the maintenance form, but because of my experience in development I was pretty sure of what was happening.

I made a quick call to the vendor's tech line, and they flushed the buffers correcting the form. Now everything matched for the report I was creating. But I wondered if I could trust the numbers. Were they really correct? How many unseen buffers or fields had not been flushed in other situations? What other errors lurked beneath the surface, undetected?

We can't do the volume of transactions without software, and we have to trust to some degree. Hopefully, the errors that are missed remain minor.