By the time a company gets to midsize, there will be a pool of people to perform ad hoc, automatable tasks.  Most of these tasks involve manually generating reports for someone else. To someone who writes software, this can seem infuriatingly inefficient and wasteful.  A sign of tech debt and bad software! 

Counterintuitively, it is extremely difficult to automate reports and actually save the company time or money.

Imagine Alice, the CEO, has Bob, in accounting, make a weekly report by hand pulling numbers from 3 different systems and plugging the data into a spreadsheet.  Alice doesn’t care how Bob generates the report, so Carol, a developer, decides to help Bob by automating the report.

To keep the numbers simple, Bob earns 75k/year (~$36/hr) and Carol earns 150k/yr (~$72/hr).  Let’s say it takes Bob 1 hour/week to generate the report, making it cost $1,872/year.  

All the data is in databases, and Bob is doing specific steps in a specific order.  He’s miserable because making the report is dull, error prone, repetitive and endless.  Worst of all, the report is meaningless to Bob! He’s generating it for Alice, because he’s capable, understands what the numbers mean, and has access.  Carol is Bob’s friend and wants to help!

Carol’s time is more valuable than Bob’s, so she can only spend 3 days automating the report for the company to break even.  That includes testing, formatting, and deployment.

Let’s assume Carol is able to automate the report on time.  The report shows up in Alice’s inbox every week, same as before, but Bob is able to work on other things.  Break out the champagne!

Except, Alice needs changes to the report twice a year.  When Bob was generating the report she could just tell him and it would take maybe 30 minutes to change the process because Bob works in accounting and understands all of the numbers.  There was no lag between the ask and the update, Bob would make the updated report next time.

Now that Carol has automated the report, Alice will have to talk to Bob, and Bob will have to talk to Carol.  Alice can’t ask Carol directly because Carol doesn’t work in accounting and doesn’t know what the numbers mean.  Additionally, Carol doesn’t spend her days working on reports so she’ll have to get up to speed, talk to Bob and make the change.  Let’s say it takes one full 8hr work day.  

As the CEO, Alice’s changes are important, but probably not important enough to pull Carol out of her current work.  It usually takes 2 report cycles to get to the top of her list.

Now the automated report costs $1,152/yr to maintain, and it’s not the exact report that the CEO wants 4 weeks, or 7% of the time.  4 out of date reports easily outweighs the $720 in savings and Alice will probably have Bob manually generate the report when the code is out of date.  Finally, the sunk cost of building the software will result in Carol having to maintain the software going forward, even though the software generates no real value.

The conflict between “can be automated” and “can’t be done profitably” often results in DBAs become a report generation team.  It’s also the impetus behind business intelligence tools; find a way to get developer style automation, without using a developer.

I’ll go over a developer’s “should I automate this task” framework next time.

This is a true story of how an inappropriate software design put a SaaS company’s reputation on the hook for their client’s bugs.  All names have been changed.

Once upon a time I consulted for Chicago Freight Brokers, a freight brokerage that was struggling with the performance of their in house systems.  CFB had an internal service, Express, that talked to 5 different transportation SaaS companies, added “special sauce” business value, and helped the brokers match trucks with loads.  Express was an MVP that was being crushed under the weight of its own success.

One of SaaS companies Express integrated with was ChicagoTMS.  ChicagoTMS, was a Transportation Management Service that built loads of freight and spoke to CFB’s accounting and insurance systems.  When a broker hit save on ChicagoTMS’s website, they would push the data into Express using a Webhook. One day I got asked to jump on a call, “ChicagoTMS is unbearably slow, it takes 30 seconds to save a load!  And they say it’s our fault! Can you talk to them?”

I jumped onto the call expecting that something had been lost in translation, after all, how could CFB’s scaling issues have any impact on ChicagoTMS’s web performance.

An hour later, I understood.  ChicagoTMS’s save operation looked like this:

The Webhook was called Synchronously as part of the save process, meaning users had to wait for the Webhook to complete successfully before they could continue their work.  CFB’s scaling problems were causing ChicagoTMS’s performance problems!

Fortunately, the culprit was a missing database index and the save time quickly dropped from 30 seconds to 2 seconds.  Unfortunately for ChicagoTMS, their design made them responsible for their client’s performance issues. ChicagoTMS’s reputation took a hit from CFB’s simple mistake.

A Better Design

Webhooks are convenient, but they are not a reliable or guaranteed delivery mechanism.  

A better design would have been for ChicagoTMS to make their Webhooks asynchronous.  This would have kept ChicagoTMS’s services fast and responsive, regardless of whether CFB made mistakes, did a lot of processing of the message, or was overwhelmed by a spike in load.

Asynchronous Webhooks would have given the users a much better experience and saved ChicagoTMS a lot of repetitional damage with their clients.

TAKE Your Reputation oFF the Hook!

Webhook performance will always be outside your control.  When you call a client synchronously you put your reputation in the client’s hands.  Your users won’t understand or care who exactly is responsible, if it’s your website, you get the blame.  

Go asynchronous and take your reputation off the hook!

As you gain visibility into system problems, you'll often experience push back like this:

I refactored the code from untested and untestable, to testable with 40% test coverage. The senior architect is refusing to merge because the test coverage is to low.

Me: I instrumented the save process. Saving takes between 10-25s, with the average around 16s!
Other Developer: That's crazy! Wait! How much time does the metric system add?
Me: About 500ms.
OD: That's over a 3% increase! You have to turn the metrics off!

Me: Good news, I can make the system 15% faster and support jobs about 6x our current max. Bad news, it uses short lived DB table, which will increase disk usage by up to 1GB/client.
Different Developer: You need to find another way, we can't have that much additional disk usage.
Me: What about all those orphaned short lived tables lingering about due to bugs and errors? Would cleaning those up get us enough space?
DD: I don't know, we don't have any visibility into the magnitude of that problem. You're going to have to find another solution.

I used to believe that this kind of push back was intentional sabotage. That the people responsible for creating the problems were threatened by exposure.

It took me many years to learn that most of the time, it's not sabotage, it's drowning people sinking the lifeboat in an attempt to save themselves.

When you add visibility to a system, the numbers are always bad. That's why you're putting in the effort to add visibility to an existing system. When the initial steps towards fixing your problems make that number worse, that's when the fear of drowning sets in.

I don't have a solution for dealing with your colleagues' reactions. Just remember, they aren't trying to sabotage you, they're drowning, and you're manning the lifeboat.

If you've got similar stories of metrics bringing pushback, I'd love to hear from you!