OLAP = from a sea of data to meaningful information?

OLAP - A computer-based means of analysing multidimensional views of data that can be turned into usable information in order to understand trends, patterns and indicators which support business decision-making.

The focus of many organisations is on CRM as a means of moving from where they are today to somewhere they would like to be. What CRM really means to the organisation is ill-defined and vague but often takes on the mantra-like quality of customer retention, customer focus and/or customer something. Many consultants get hung up on whether the focus should be on one-to-one marketing, or whether they should focus where the money is and go for systems integration. Oh yes, SI (systems integration) usually refers to installing a 'CRM system' such as a call/contact centre, sales force automation and/or clever database marketing. Then the company can say they have done CRM and the consultants can move on to the next victim (sorry client). If the organisation has a CRM strategy that includes a sensibly thought-out programme for implementing technology to support CRM that's fine, but few do! Technology drives the CRM quest, not an understanding of what is needed to change the organisation.

OK what has all this got to do with online analytical processing (OLAP)? Well for a number of organisations that get themselves into the position of doing CRM via technology, many fall at the first hurdle of even having useful reports on what matters in the organisation.

So you don't even get useful reports that support the business and now you're implementing CRM. To me that means you are not going to get very far. Some fundamentals need to be addressed. I firmly believe that you need to have a CRM strategy that is more than technology-based. You need to consider what information, not data, you need to run your business and then focus on how you can become a CRM organisation. Without clarity at this level you run the risk of building call/contact centres and any other CRM technological solution based on sand. The foundation of any customer facing system and any business decision is trusted data that is in the form of information. Data is important but is of little value until it is put into context and becomes information so that decisions can be made. The lack of forethought regarding trusted information sources when building some of the latest customer-facing solutions is beyond belief.

Now OLAP is only part of the answer. For many organisations starting out on the road towards providing their managers with better information, so as to enable them to do their job and begin the journey towards CRM, OLAP is a major weapon. To start it might be an idea to do an audit of what data-information you currently have, and what your teams actually use and think they need. Managers are often adrift in a sea of data when what they want is pertinent information to help them make decisions. So they want less data but more and better information. I like the idea of giving them TACI: Timely, Accurate, Concise Information. Either business managers with domain knowledge assisted by able OLAP practitioners or highly trained business users can make this happen. Without that training in the tools or the knowledge of the business little will be accomplished.
OLAP for some is a tool to be used for analytic CRM, but it is much more and should be seen as a business tool that enables the business to get at information in a meaningful way. Is it a wonder that both managers and consultants get confused? Some vendors have begun to refer to large-scale (for them) implementations as data warehouses (even though they cannot easily act as a historical archiving tool). The power of OLAP is the ability it gives the business to create multi-dimensional views of the underlying business. The need to have that OLAP cube model stems from the multi-dimensional nature of business itself. Just as the data warehouse (DW) needs clean, trusted, well-organised granular data so does the OLAP with the addition of data aggregation.

In many cases it's the DW that feeds the OLAP tools. However OLAP tools can be seen as verification systems or clever aggregation-based reporting systems that enable you to use a subset of that larger data warehouse to drill down, slice, dice and roll up data (a very oversimplified view). Data is stored in multi-dimensional structures that you can visualise as cubes of data within even more cubes, with each side representing a dimension. Many use the snowflake schema that is itself an extension of the star schema concept used by some DW consultants as discussed in an earlier article, Data at a granular level. You can look at how a product/service within a region within a branch is doing monthly, quarterly etc., The data held is aggregated so a cell would have the total sales of the product/service for that branch whilst another cell holds the total for the month.

Often you will find that these tools are used for consolidations and dynamic budgeting, as well as for forecasting, sales, customer and general business analysis. They are business tools and they support business users directly not through the information services (IS) group. The whole point of these tools is for the business user to be able to use them themselves, and suppliers like COGNOS, Business Objects, Microstrategy and SAS are continually enhancing their user interface.

So OLAP is another of those 'must have' tools that support you in building that customer knowledge infrastructure. Oh, and you could consider: M(multidimensional)OLAP, R(relational)OLAP, H(hybrid)OLAP, W(web enabled)OLAP and any other acronyms that could help. However as with all technologies of this kind it would be sane to find out what the business needs are before purchase not after. It is also in your own interests to have a CRM strategy that incorporates the need for tools, techniques and change management, then has senior management follow through.

Tools and techniques don't take the place of a business strategy and well-motivated managers. However, without some of the tools, strategies account for little if your managers don't have access to the relevant information. If your call centres and sales teams don't have access to accurate and trusted information how will they serve or market to customers and prospective clients?

Some interesting history

The OLAP Council, developed in January 1995 to serve as an industry guide and customer advocacy group for OLAP, offers the following definition: "OLAP is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user."

Features of an OLAP Database from Dr. Codd, courtesy G. Simon website
The following points summarise the key aspects of the 12 OLAP rules as defined by Dr. Codd in his original 'Providing OLAP (On-line Analytical Processing) to User-Analysts: An IT Mandate' (a vendor inspired paper).

1. Multi-Dimensional Conceptual View
   OLAP databases support a multi-dimensional view of the data allowing for the classic "slice and dice" operations or pivoting and rotation of the conceptual cube of data. This might involve looking at the data in terms of the products or product categories on display as well as by the outlet channel that provided the business and then moving on to levels of persistency inherent in the business from each source. All of this available as and when required by the user-analyst. Interestingly, this rule is given subtle levels of shading by some suppliers of OLAP-type software who argue that a multi-dimensional conceptual view of data can be achieved without multi-dimensional storage.


2. Transparency
   The users should have no need to know that they are looking at an OLAP database. As far as they are concerned, they are using tools with which they are familiar to get the data they require in order to make the decisions they have been charged with making. Nor should they need to know the source of the data. For example, there should only be one definition of persistency and this should be applied at all data sources irrespective of their provenance.


3. Accessibility
   The tools in use should have a map of data sources within it (the implementation of the Categorical model) which point it to the most appropriate source of data to support a specific query and perform any conversions of data or semantic meaning in order to give an agreed and predetermined interpretation of the enterprise business model.


4. Consistent Reporting Performance
   As the number of dimensions or the number of levels of aggregation changes, there should be no alteration in the way key figures are calculated. The system models should be robust enough to cope with changes to the enterprise model. This is essential if the figures presented in the OLAP tool are to be believed and its analysis or predictions are to be trusted.


5. Client-Server Architecture
   The OLAP tools should be capable of being deployed in a client-server environment, implying that the multi-dimensional database server should be accessible from a range of other applications and tools.


6. Generic Dimensionality
   "Every data dimension must be equivalent in both its structure and operational capabilities ... The basic data structure, formulae and reporting formats should not be biased toward any one data dimension." Providing OLAP (On-Line Analytical Processing) to User-Analysts: An IT Mandate, E.F. Codd, S.B. Codd and C.T. Salley, 1993.


7. Dynamic Sparse Matrix Handling
   Typical multi-dimensional models can easily run into millions of cell references, many of which will have no appropriate data at any one point in time. These null values should be stored in an efficient way and not have an adverse effect on the accuracy or speed of information retrieval.


8. Multi-User Support
   OLAP tools should support and indeed encourage group working and the interchange of ideas and analyses between users. To achieve this, multi-user access to the data is essential.


9. Unrestricted Cross-Dimensional Operations
   The rules which govern the progress of data "roll ups" through levels of a hierarchy should be defined and available so that no matter which slice of data is taken, the rules will be applied consistently.


10. Intuitive Data Manipulation
    The user-analyst's view of the data should at all times contain all information necessary to effect the navigations (the slicing and dicing) which are appropriate without the need to resort to the use of a menu or multiple trips across the user interface.


11. Flexible Reporting
    The user should be able to retrieve any view of the data required and present it in any way that they require.


12. Unlimited Dimensions and Aggregation Levels
    There should be no limit imposed by the OLAP tool to the number of dimensions, which can be built into a model.

Scaling
Some of the above represented wishful thinking when the original rules were written. Yet the promise of OLAP is gradually being realised. For many years there have been concerns about the ability to scale up OLAP tools to handle the millions of items that organisations need to analyse to get at the information they want. This has lead to many information architectures incorporating both OLAP tools and data warehouses as the means of achieving their informational goals. There have been further developments as vendors make their products/services applicable to specific verticals and other supplying horizontals. Some vendors claim they have no problems scaling up, just as some vendors claim they have the fastest this, the largest that and so on. Well maybe they have - but buyer beware!

References

Some useful sites to assist you in your quests for knowledge:

www.cognos.com/
www.businessobjects.com/
www.microstrategy.com/
www.ncr.com/solutions/crm/crm.htm
www.olapcouncil.org/
www.oracle.com/ip/analyze/warehouse/bus_intell/index.html
www.sas.com/technologies/olap/

As always, any comments on this editorial can be made here, either by using the 'add a comment' link below.

Best regards,

Michael Meltzer