Smart Infrastructure & Smart Applications for the Smart Business – Infrastructure & Application Performance Monitoring
Data Warehouse Automation & Real-time Data – Reducing Time to Value in a Distributed Analytical Environment
Over that last decade or more I have reviewed many different BI Systems for clients. During that time and still today, one thing has continually cropped up time and again irrespective of vertical industry is the problem of inconsistent metrics. You see it in different data marts, different BI tools and of course across spreadsheets that may access BI systems. This problem has plagued enterprises for years and despite many efforts is still there today and particularly acute in Excel spreadsheets that fly around the enterprise as people supply intelligence in spreadsheet form to others. Historically this problem has often arisen when companies have built different BI systems at the line of business level rather than at an enterprise level. You might think that this is a crazy idea but time and again it has happened as pressure from line of business executives has grown to deliver some kind of BI to support decision making in their particular area.
Take any large bank for example. Here you may find separate product based risk management BI systems for mortgage products, card products, loan products and savings products. Product based risk management BI systems are a classic set-up in many financial services organisations and it is only recently that many financial institutions are seeing the need to re-visit risk management at the customer levels to see risk exposure across all products owed by a customer.
Another example is when an IT department has built one or more BI systems and then given the user base total freedom to select their own BI tools. This is the ‘Field of Dreams’ approach to building a data warehouse, i.e. the ‘build it and they will come strategy’. Therefore the user base in this example may be choc full of different BI tools chosen by different user departments to access the data stores that IT have built. A third example is the parallel build approach whereby companies have spawned different BI teams around a large enterprise in order to speed up BI system development. These teams often end up building separate data marts as stand alone initiatives and so no common data definitions get used across BI systems.
Looking at these examples there is clearly been lots of opportunity over the years for inconsistency to creep into the BI implementations. This has not happened deliberately. It is more of an inadvertent consequence of stand-alone developments and lack of communication across BI development teams and line of business departments.
The consequence today is that is not uncommon to find different BI tools in an enterprise each offering up the same metric under different data names. You may also find that what looks like the same metric may have different formulae for different instances of it because of different user interpretations of it. Worse still is different metrics with the same name and different formulae. This is something that can cause significant confusion among business users often meaning that they are not clear on what BI metrics mean. In situations like this it is not uncommon to see users resorting to creating their own spreadsheets with their own version of the metrics they need and with their own version of formulae. Then of course they start emailing people attached spreadsheets and so the rot sets in and inconsistency starts to spread undermining all the investment in BI. I sense a few heads nodding out there, would I be right? So what can you do about it? Fundamentally a major problem with many BI tools is that they offer up what is marketed as ‘flexibility’ to the end user by giving them the chance to create their own metrics without policing this. So it is rare to see a BI tools either preventing users from creating duplicate metrics to those that already exist or to at least warn them that a metric already exists to avoid re-inventing it. This lack of policing is at its worst among the Excel spreadsheet ‘junkies’ that exist out there.
Amidst a climate of increasingly stringent compliance regulations this problem is beginning to seriously worry executives such as CFOs that often carry ownership of the compliance problem. What they want is common metrics definitions and for all tools to share access to these definitions. In particular, for reporting tools and spreadsheets to have access to such common metrics definitions and to prevent authorised users from inadvertently creating the sane metrics again and again without knowledge of what already exists. Is it any wonder therefore that common metadata is becoming increasingly important year on year.
What we want are “metrics cops” that prevent users from creating inconsistent metrics definitions. Therefore if a user tries to create a metric perhaps with a new name but with the same formulae that already exists elsewhere, a metrics cop would intercept this request and inform the user that a metric with the same formulae already exists and they should re-use the commonly defined metric if they need that metric in a report. Equally, if two users each try to create a metric with the same names but different formulae, a metrics cop should once again intervene and prevent this from happening so that clarity, consistency and compliance are all maintained. It was therefore with interest that I looked at the recent e-Spreadsheet 9 announcement from Actuate (http://www.actuate.com) which has the capability to generate spreadsheets for many different users while guaranteeing metrics re-use across all spreadsheets. No only that but if the same metric is needed in different spreadsheets, this product inserts the same formulae in all spreadsheets that need it, thereby guaranteeing consistency. Several other BI vendors are beginning to look at this problem but often only across their own BI tools. As we move towards shared metadata across all BI systems to enforce compliance, the need for metrics cop functionality in BI tools such as reporting and OLAP is becoming a must for many enterprises. In addition performance management tools and applications need to also enforce this.
Therefore look closely to see how BI vendors stack up in terms of policing metric creation in their BI tools and performance management applications to help prevent your users from inadvertently contributing to metrics inconsistency, reconciliation and compliance problems. Also look to see how BI tools can discover what metrics already exist in your BI systems and how they can import metrics definitions from other technologies where they are defined. Finally check to see how BI platforms allow you to share common metrics definitions across 3rd party BI tools. This is especially important when it comes to Excel.