Mike Ferguson

By Mike Ferguson

January 2004

Conquering CPM and BI Integration – Making It Happen

Building on from my last article on this subject which looks at why corporate performance CPM and BI need to be integrated, this second article looks at how to make the kind of integration possible and why metadata interchange on metrics is key to making it happen.

Over the last six months the market for CPM software has heated up significantly with several vendors competing for business in this area. Cognos has led the charge and had considerable success with their Metrics Manager product.
Recently, Cognos arch rival Business Objects has entered the market with their new Performance Manager offering built with Business Objects Application Foundation. Of course many other vendors are already in this market including SAP, PeopleSoft, Oracle, SAS and Corvu to name but a few.
These CPM products are being deployed over the top of packaged analytic applications (often supplied by the same vendor) but have the ability for users to also add their own custom metrics to business performance scorecards and dashboards to extend the reach of CPM beyond packaged analytics.
This fairly new approach to enterprise business performance management differs from stand-alone scorecard software products of the past because they are based on a strategy that is based on integrated business intelligence that links CPM to traditional BI processing. The idea is that analytics at the operational and tactical levels are “rolled up” into KPIs that can be viewed in dashboards and attached to specific strategic objectives in scorecards at the strategic CPM level.
To do this however is often considerably more difficult than market hype makes it out to be. It requires most companies to face up to a challenge of rolling up lower level analytics into KPIs at the strategic level. The reason why this is difficult is because most metrics that companies need to contribute towards the calculation of KPIs at the strategic CPM level are often distributed across a mix of both packaged and custom built analytic applications.
In addition these custom built and packaged analytic applications may be deployed across a range of heterogeneous data stores such as relational (e.g. NCR Teradata, IBM DB2, Oracle, Microsoft SQL Server) and multi-dimensional (e.g. SAS MDDB, Microsoft Analysis Server, Hyperion Essbase) data mart databases often on different servers running different operating systems.
The job of integrating CPM with BI is therefore not necessarily straight forward. There are in fact a number of approaches to solving this problem that are discussed below. The options are:
1. Use data integration software to acquire and integrate data from multiple different custom and packaged data marts and store this data in a separate KPI database for use by CPM software. This approach is often known as stand-alone CPM (see figure 1)
2. Build separate CPM solutions (e.g. scorecards) on top of existing BI “islands” (e.g. each data mart, each packaged analytic application) and give access to multiple scorecards via an enterprise or BI Portal (see figure 2)
3. Use metadata interchange preferably using industry standard CWM XMI to share and exchange metadata between applications, DBMSs and tools so that KPI metrics trees can be built up in a CPM product (see Figure 1) by discovering and importing metadata about all metrics in custom built and packaged applications already deployed. Metrics are then attached to objectives in objectives trees and one or more scorecards built to track business performance and steer the business towards meeting its objectives.
The CPM product then needs to support federated query across multiple heterogeneous data stores to be able to drill down from high level metrics to lower level ones as users navigate the measures in a metrics tree. Scorecards and dashboards showing objectives and associated KPIs can then be integrated into portal products to deliver personalised objectives and accompanying metrics to the appropriate managers around the organisation (see figure 3)
A variation on this approach is to bypass CWM metadata interchange when not supported by underlying technologies and simply re-key metadata about metrics in existing custom built analytic applications into the CPM product to add them to one or more metrics trees.
As support for web services grows CPM software may request metrics via a web services interface to packaged applications, BI tools and databases. This is shown in figure 4
It is interesting that the above options show a very familiar technology choice. Option 1 is centralised, option 2 is distributed and option 3 is federated.
So the question is, are these options choices or are they actually the stages of a CPM life-cycle that starts off centralised and ends up federated? From experience I am suggesting that this is a life-cycle. How many times in technology have we seen this? It happened in database (which started off centralised, then went distributed and is now federated), it happened much more rapidly in portals (also now federated), it happened in data warehousing and now it’s happening in BI/CPM. Let’s look therefore at the strengths and weaknesses of the options.
Option 1 is very simple to understand and that in itself is a strength.
Here lower level summary metrics data is extracted from other places and integrated to create a separate KPI database for CPM. There are problems with this strategy however. What happens if a KPI signals a business problem? How does a manager for example drill down to the detail to understand what the problem is in depth? After all, only then can a solid well informed decision be made.
The answer is that he or she cannot do a metrics drill down unless all the detail needed is in the KPI database. At worst, that could mean that the data from all custom and package based analytic applications would have to be consolidated and integrated into a KPI database. With some warehousing systems being in the 10?s or possibly 100?s of terabytes, this option seems impractical.
If chosen, then it is clear that precise understanding may not be possible at a managerial level with this option which begs the question as to how managers can effectively manage when they do no have access to the all the information they need to make the best decision possible. In fact this issue is a very well known problem and has been at the root of why “stand-alone” scorecard products have either failed or been much less effective than expected in the past. Integration with detailed intelligence is a critical success factor.
Option 2 is also very simple to understand.
In this case multiple scorecards are built using the CPM software and then integrated into a portal to allow personalised access to appropriate scorecards and metrics depending on user role. With this approach, metrics trees can be built in the CPM product but have limitation in that their calculation is reliant on data available in a specific custom built data mart or packaged analytic application being accessed.
The problem that arises with this approach is when a metric is needed that requires data not held in the data mart or packaged application being accessed but that is held in another system. To hold the data needed for such “new” metrics, may require the data model of the underlying data mart to be changed. In addition, depending on how the CPM product is deployed (e.g. multiple separate CPM product installations) “islands” of CPM metadata about objectives and metrics may exist and not be shared. This kind of deployment would not benefit enterprise business performance management.
Option 3 is an attempt to integrate metrics and objectives across the board while maintaining the flexibility offered by focussed packaged and custom built analytic applications.
Here the key to successful integration and management is based on metadata being made available so that objectives trees and metrics trees can be built that reference data in existing custom built and packaged analytic applications. Figure 5 shows an example of one of these metrics trees.
In a metrics tree the CPM product holds metadata that gives it “know how” on what lower level metrics are needed and used to calculate higher level ones. In addition, CPM products can track each metric in the tree independently. Business analysts and managers can build as many metrics trees as required and can re-use metrics across multiple trees
However it is clear that metadata on metrics is being created in multiple technologies and so it becomes necessary to share and re-use definitions to prevent chaos occurring such as several metrics with the same name and different formulae, or metrics with different names and the same formulae. If we look at database vendors such as Oracle and IBM, these vendors have now extended their DBMS catalogs (system tables) to capture metadata about metrics, dimensions, hierarchies etc. to make the DBMS ?olap aware?.
For example, Oracle have an OLAP catalog that is CWM compliant. It holds metrics trees that Oracle knows about and that Oracle can calculate dynamically. Equally IBM DB2 V8.1 has added metrics trees (see Figure 6) so that it knows about the relationship between metrics in the database. Packaged applications also hold metadata on metrics.
For a CPM product to integrate correctly with existing “olap aware” databases and packaged applications requires that the metadata about metrics defined in these technologies can be discovered and accessed to allow re-use of these metrics across in CPM developed scorecards and dashboards.
Figure 7 shows how the industry CWM metadata interchange standard can be used by CPM products to discover and import metrics metadata from DBMSs, BI tools, and packaged analytic applications to use in scorecards that can be delivered via a portal. As users navigate metrics trees to drill down from high level KPIs to low level metrics CPM products that support federated query can support users who need to drill into one or more data stores to get at detailed data.
As DBMS vendors continue to add support for BI metadata and enterprise information integration (e.g. DB2 Information Integrator) we may well see CPM vendors increase their use of the DBMS to facilitate BI integration.
Of these three options, it would appear that option 3 is the direction and something that companies should look to moving towards. What this strategy says however is that metadata and interchange of common data definitions are yet again at the centre of BI integration and indeed operational application integration and yet in many cases there is little attention paid to it by major enterprises.
It is time most companies took common metadata (common naming, definitions and XML vocabulary) seriously and put it high on their list if they want to stop chaos and switch to management using integrated intelligence and to drive operations by integrating intelligence into operational business processes.