The Story of Al’s Spreadsheet and Absent BrainsI want to share with you the most intelligent thing I’ve ever heard a manager say on the fly. I’ll give some background first. In one way or another, the situation that evoked what she said may seem quite familiar to you. A few years ago Gladys and I were invited to conduct a one-week facilitated session for a national taxation authority. The objective was to reverse-engineer business rules from a very complex spreadsheet. The manager told us everyone was deathly afraid to touch it because no one understood how it worked. You could never tell what impact making a change would have. Used by many of their systems and embedded in various websites, the spreadsheet was mission-critical. She was finally biting the bullet and assembling some of her best staff to re-engineer it. Everyone knew the spreadsheet by its nickname, Al’s Spreadsheet. It dated to the 1970s when it was implemented under the first generation of automated spreadsheets. Notorious in the organization for more than a generation, it was devilishly convoluted. One of these days I might hold a contest for the world’s most complex spreadsheet. I’ve actually told this story many times around the world, and the audiences’ reaction is inevitably the same – perhaps the same as yours reading this. “No, Al’s Spreadsheet couldn’t possibly be the world’s most complex spreadsheet because my company has the world’s worst!” Around the globe there is extensive core operational business knowledge running businesses day-to-day that is highly inaccessible. Just putting your fingers on it, much less revising it, consumes vast amounts of vital resources. We live in a service provider’s dreamscape. It makes you wonder how brittle (read not agile) many companies’ operations really are today. To return to the story, by mid-week we’d achieved only limited success in deciphering the spreadsheet. Progress was painfully slow. Lapsing momentarily into frustration, an idea popped into my head. I blurted out, “Hey, why don’t we just go ask Al what this thing really does?!” I assure you my intention was not to provide comic relief. To my chagrin that’s exactly what happened. The whole room erupted into laughter. When the hysteria finally subsided, someone patiently explained to me (barely keeping a straight face) that nobody actually knew who Al was – or indeed, whether Al had actually ever even existed. If so he had long since parted ways with the organization, fading away as all workers sooner or later do into the mists of time. That’s when the manager said the thing that I found so memorable. Looking at no one in particular and staring vaguely into the far distance she said, “No organization should ever depend on absent brains.”. Exactly! To ensure the continuity of operational business knowledge, no organization should ever depend on absent brains – or even on brains that could (and eventually always will) become absent in the future. To say it differently, your operational business knowledge should be encoded explicitly in a form that workers you have never even met yet can understand. Operational business knowledge can be either tacit or explicit (read ‘accessible’). The classic test for when knowledge is tacit is ‘lose the person, lose the knowledge’. You make operational business knowledge explicit by expressing it as business rules. So make sure when you lose your Al, he doesn’t walk out the door with the day-to-day knowledge you need to run your business. Encode it as business rules!
Tags: absent brains, Business Rules, knowledge retention, reverse engineering business rules, tacit vs. explicit knowledge
This is an important and timely post. We all may agree that it is much better to have explicitly expressed business rules than tacit knowledge hidden in “Al’s spreadsheets”. But I suspect that this good advice didn’t bring any relief to the mentioned “national taxation authority”. It would not directly help many other companies that already found themselves in similar situations. My question is: what actually can be done to help these companies?
The technology is moving forward and I believe today we may offer some practical solutions to “Al’s spreadsheets” problems. Your “objective was to reverse-engineer business rules from a very complex spreadsheet” but it seems it was impossible to understand how this magic spreadsheet actually worked. However, the fact is that it did work! It makes me believe that modern predictive analytics tools could be in help.
You wrote that the spreadsheet was “mission-critical and used by many of their systems and embedded in various websites.” It means it was possible to run thousands of test cases, for which this spreadsheet produced specific results. It is also probably reasonable to expect that their business experts were able to evaluate the produced results and to mark them as correct or incorrect. Thus, it was possible to create quite representative training sets with positive and negative results. These sets could be used by a rule learner to automatically generate business rules which produce similar or very close results. The modern machine learning algorithms (such as RIPPER) allow you to make sure that generated rules can be understood, interpreted by experts, and managed be business people, while they also can be executed by a rule engine.
It is not just a theoretical consideration. A few years ago OpenRules did a similar work for the major US taxation authority (http://openrules.com/what_they_say.htm#IRS). We did not had an “Al’s spreadsheet” in this case but we even did not have an access to the rules that were used by this authority to red-flag certain tax returns – naturally these rules is an ultimate secret. We only knew the results (positive and negative) for a quite representative population of already audited tax returns. Applying an integrated machine learning and business rules approach, we managed to generate business rules that our customer found quite valuable. More importantly, their subject matter experts were able to improve these rules. Interested readers may find more information about the proper architecture at http://openrules.com/rulelearner.htm.
I need to clarify that we did not reproduce the same rules as original ones (we would never know them), but the results produced by generated rules were better to compare with original rules. Especially, in this case the generated rules avoided to picking up tax returns which would end up with wasted audits.
Again, to address “Al’s spreadsheet” problems it is important to be able to run “something” (without knowing how it actually works) that produces many positive and negative examples to be used as training data. Another interesting example is described in our Rules Compressor (http://openrules.com/rulecompressor.htm). You will see how a set of multiple business rules may be reduced to a much smaller ruleset while both produce similar results with minimal mistakes.