Integrating Business Intelligence and Planning

I was prompted to post on this subject by a LinkedIn question, what place do your Cognos or Hyperion Planning applications have in your Enterprise Data Warehouse?

The major vendors, including IBM (Cognos), SAP (BOBJ) and Oracle (Hyperion) have both Planning & Forecasting and BI solutions in their vast product portfolio. The questioner was focused on understanding what the challenges are in integrating data from the planning application into the enterprise data warehouse. It is, after all, common for the business to want to report, say actual revenue along with planned or forecast revenue and a variance. This is basic corporate performance management and answers the question 'how am I doing against plan?' or 'am I going to make forecast?'

There is an understandable misconception that because both the BI tools and Planning tools come from the same vendor that they 'automatically' integrate. They don't. There are points of integration including UI, security and, in Cognos Planning, functionality to create a reporting database and metadata. However, none of this represents a panacea. If you think about it for a moment, they can't. A planning or forecasting application is as just that, an application. It is another source of data along with all other operational systems and the reporting requirements have to be considered carefully. And, of course, the way in which plan and actual data are integrated and reported should be driven from the requirement and not vendor product functionality.

There are a number of considerations, including;

Data flows both ways. A planning application usually requires actual data because it is common for business managers to build this year's plan from a prior year's actuals. Or it might be that YTD actual will help business manager pull together more accurate forecast numbers. This means that actual data needs to flow from the data warehouse into the planning application. Similarly, a planning application may require historical plans which are not stored in the planning application but might be found in the EDW or other operational sources.

Data in a Cognos Planning Database is not Like Other Operational Data. Cognos planning models are incredibly flexible. However, this means that that changes to the model, even seemingly trivial changes, can result in column and/or table name changes. This will 'break' the metadata and reports. Add to this the fact that planning applications, unlike other operational applications, can change each year and you will find that you are trying to hit a moving target. A level of abstraction is required to protect the reporting from these application changes which is one of the reasons that EDW's exist in the first place.

Planning Hierarchies are not the same as Dimensions. It is not unusual for planning hierarchies to be extracted from dimensions in the EDW. However, they are not always the same thing. For example, it may be that major products are forecast individually but below a certain threshold products are forecasted as 'other'. This is, perhaps, a trivial example but is just another example of what we already know. That reference or master data can be subtly different across operational systems and the business has to define a common definition for the purpose of consistent management reporting.

Planning Reporting is Real-Time. There is usually an operational (and therefore real-time) requirement to report during the planning process. The finance team or profit centre managers adjust their forecast numbers across dimensions until they arrive at an overall plan that meets the objectives set for them by their senior management team. They will want to repeatedly run reports to validate the latest position. This real-time reporting requirement might drive the developers of the planning application to build all reporting from within the planning application but will get frustrated that as the reporting requirements grow so will the need to import more and more actual data into the planning application. Again this simply confirms what we already know about data warehousing. Some reporting requirements are operational and need to come from the operational application, others are summarised BI or management reporting and will be developed against the EDW.

This isn't an exhaustive list of considerations but they do illustrate why reporting requirements for a planning application should be considered at the outset of a planning application project. It is inevitable that integrated, consistent, enterprise reporting will need extensions to the BI and EDW solution whilst other real-time, operational reports will stay within the planning application. This is why a reporting solution from a planning application should be properly architected rather than expected to appear 'out of the box' form the product vendor.