BI Application Design – The Missing Step

If Amazon applied typical BI application design techniques to their web site the user experience might be very different. For example, I might be browsing the DVD's for a gift and need a little inspiration so I hit the 'reports' tab. Here I get presented with a long list of reports, one of which one is 'Top n Products'. I then get prompted with a pick list of products where I select 'DVD' and finally I select '10' to get the Top 10. The list is pretty interesting, but there is nothing that grabs me so I decide to look at the next 10. I re-run the report, selecting 20 instead of 10. This time, I spy the ideal box set and go back to the main site to make my purchase. I am sure you get the picture by now. It sounds awful, clunky and not at all like the actual Amazon experience.

BI reports can sometimes get designed with little or no understanding of the decisions they support. Actually, more often than not, the requirement is communicated as a report layout and the underlying business need is at best inferred or at worst lost in a fixed specification of rows, columns, filters, sorting and grouping. Without an understanding of the audience for the report or how the information is used, the report layout communicates a general requirement in a one-size-fits all report.

A BI specialist on a project that we are currently assisting with recently demonstrated how it should really be done.

The requirement is for a team of internal sales reps each targeted with a number of customers to call each month. Our BI specialist was asked to provide a report which compared actual calls made with target. Rather than creating one multi-purpose report, he created three specific solutions. One for the rep, one for the sales managers and one for the senior management team. The report for the rep contains the number of calls they have made, their target and how many calls they need to make today, this week and for the remainder of the month. It is run daily. The rep can plan their daily, weekly and monthly activity based on this information. The report for the sales managers is ranked so that they can manage the individuals accordingly. This report is weekly, reflecting the frequency with which they review and action the performance of their teams. The senior management team report is monthly and is a team summary for monthly and quarterly sales performance meetings with the sales managers.

Of course, the Amazon comparison is not completely fair. Whilst it is using highly summarised information for the purpose of decision making the decisions are discrete and simple. Which computer game should I buy? What computer games did others that bought this one also buy? It is also an application that has a single user type – customer. Not only that, there are millions of users which makes design effort not only cost-effective but critical to generating revenue.

However, some of the design principles absolutely apply;

1. Understand the decision (which reps do I need to coach to make more calls?)
   
2. Understand the information required to make the decision (how many calls they have made, what is their target and what is the variance)
   
3. Identify the actions that can be made as a result of decision making (reps may be incented, coached or disciplined)
   
4. Link the information as closely as possible to the business process it supports (sales managers review activity levels every Friday morning)
   

Finally, this doesn't mean there is not a case for analysis. BI supports managers and not all decision making is predictable and routine. The business environment is continuously changing and each month or quarter will throw up new and interesting business challenges to solve. These can be subtle variants of historical business challenges or completely new. This is where decision makers need the flexibility to explore and analyse trends, exceptions and patterns to validate what is happening and determine the actions that they will take next.

The answer, as is often the case, lies in a combination of providing BI reports that support decision makers closest to the point at which they need to make the decision along with the flexibility we have come to expect from good OLAP tools.

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.