333 Rule To Keep Your BI Apps In Check

Over the last 25 years in the business I heard my share of BI horror stories: “we have over 20 different BI tools”, or “we have a few thousand reports in our BI application”. BI is very much a self fulfilling prophecy – “build it, and they will come”. As we popularize BI, and as technology becomes more scalable, more stable, more function rich and user-friendly  - BI spreads like wildfire and often becomes uncontrollable.

I can’t help but to quote from one of my favorite books by a British author Jerome K. Jerome “Three Men In A Boat, To Say Nothing Of A Dog”. One of the reasons I love the book, in addition to it being one of the funniest novels I ever read, is that I can almost always find a very relevant humorous quote to just about any life or business situation. At the beginning of the book three British gentlemen are planning a vacation on a river boat. As they plan for how much food and supplies they should carry, they quickly realize that there isn’t a boat big enough to fit the dimensions of the Thames river to carry all that junk.

“How they pile the poor little craft mast-high with fine clothes and big houses; with useless servants, and a host of swell friends that do not care twopence for them, and that they do not care three ha'pence for; with expensive entertainments that nobody enjoys, with formalities and fashions, with pretence and ostentation, and with - oh, heaviest, maddest lumber of all! - the dread of what will my neighbour think, with luxuries that only cloy, with pleasures that bore, with empty show that, like the criminal's iron crown of yore, makes to bleed and swoon the aching head that wears it!

It is lumber, man - all lumber! Throw it overboard. It makes the boat so heavy to pull, you nearly faint at the oars. It makes it so cumbersome and dangerous to manage, you never know a moment's freedom from anxiety and care, never gain a moment's rest for dreamy laziness - no time to watch the windy shadows skimming lightly o'er the shallows, or the glittering sunbeams flitting in and out among the ripples, or the great trees by the margin looking down at their own image, or the woods all green and golden, or the lilies white and yellow, or the sombre- waving rushes, or the sedges, or the orchis, or the blue forget-me-nots.

Throw the lumber over, man! Let your boat of life be light, packed with only what you need - a homely home and simple pleasures, one or two friends, worth the name, someone to love and someone to love you, a cat, a dog, and a pipe or two, enough to eat and enough to wear, and a little more than enough to drink; for thirst is a dangerous thing.

You will find the boat easier to pull then, and it will not be so liable to upset, and it will not matter so much if it does upset; good, plain merchandise will stand water. You will have time to think as well as to work. Time to drink in life's sunshine - time to listen to the AEolian music that the wind of God draws from the human heart-strings around us - time to -

I beg your pardon, really. I quite forgot.”

Yes, my apologies too for the digression, but I think the author very eloquently illustrates the point in am trying to make – in many instances most of the BI reports and much of the data in the data warehouses are useless junk.

How do we address this unstoppable proliferation of BI applications and reports? One is to address an existing situation, another one is to implement a process to prevent it from happening in the first place.

1. Use “BI on BI” to weed out existing BI junk.

  • Most of BI tools come with usage analytics allowing you to see who is using what, how, when and how often. You can then analyze usage patterns and get rid of unused and/or restructure lightly used reports, queries, calculated metrics, tables, indexes, aggregate tables, and other BI objects.
  • A slightly more difficult challenge, but still quite doable and critical, is to understand whether multiple reports actually do the same job (same SQL, same columns, etc)  - which is often a result of poor menus and folder structure, and poor search capabilities, resulting in end users building new reports rather than finding an existing one that can do the job.
  • Also, if you have multiple BI tools, then look at tools from www.appfluent.com and www.teleran.com to provide you with a centralized repository of BI usage from multiple BI applications.

2. Use 333 rule to stop the proliferation at the root. One global pharmaceutical company is using this approach quite effectively, by sticking to the following process:

  • They use Composite Software EII technology to virtualize access to all of their data sources
  • Whenever there is a request for a new report that requires new data source or additional data sets from existing sources they first create manual extracts, pull them into text files and create EII views on top of these extracts
  • If, and only If, after 3 weeks, the new reports are still being actively used, they then point the virtual EII views directly to the operational data sources.
  • If, and only If, after 3 months, the reports are still being actively used, they then create new DW tables and populate them with the new data sets. This is where they go through the motions of also cleansing the data, and integrating with their MDM processes.
  • If, and only If, after 3 quarters, the reports are still being actively used, they then completely productionalize  the process with QA, UAT, DR and other production controls, risk management, and operational procedures.

The company claims two very significant benefits of this approach. First, more than 50% of the original requests fall off the queue before reaching the final third phase, weeding out potential future BI junk and freeing up database, server and human resources for other tasks. Second, it becomes much easier for IT to say “yes” to new requests since they now that the initial deliverable, the first “3” typically does not require much effort. It’s a huge win-win-win scenario.

Any other ideas on how to get rid of BI lumber and lighten the load of our BI boats?

Comments

re: 333 Rule To Keep Your BI Apps In Check

Useful post, particularly the example of creating a process to manage what goes into the central warehouse vs. what stays out. I would refine it further, and create secondary repositories for things that are specific to workgroups or departments, details that only a small number of people care about.

The BI tools provide usage data, but it's poorly organized and hard to use. They ought to be aging reports and automating archival as a part of operation. I made that feature request to two vendors at least five years ago because it's such a common problem. Not much you can do when vendors won't recognize problems with their own tools except add more lumber to deal with the lumber you already have.