Online Analytical Processing (“OLAP”) is a set of powerful technologies which can help businesses to understand data. I would like to describe here in very simple terms some of the things OLAP does.
Typically an OLAP system will import data from underlying sources of information such as a traditional database. These traditional databases can, in turn, import or connect to a large number of legacy systems or even simple sources such as spreadsheets. An OLAP system will have a very efficient way of storing the data that it uses to ensure that costs are minimised and that performance is maximised. These systems are cheap to run and allow data to be retrieved very quickly.
As part of the way they hold data OLAP systems will often store a cache of pre-calculated queries that allows them to respond very quickly to problems posed in the business. They can even analyse the types of queries that the business is asking and adjust the cache of pre-calculated results to include questions that are more likely to come up.
OLAP storage systems also have security functions built in to allow for very sophisticated security rules to be put in place for sensitive data.
Exposing Business Relationships – Dimensions
A business is a highly dynamic entity with many different parts. Obviously any technology that seeks to understand a business must also consider all of these moving parts. Traditional reports used to focus on just one aspect of a business; they may have reported that revenue has increased but would typically not be able to say what effect that had on other linked aspects such as profitability or delivery lead times. This was the one-dimensional thinking of yesterday.
OLAP databases are often called cubes because the have many facets or dimensions. It is typical for an OLAP cube to consider relationships between a series of dimensions. Typical dimensions include customers, products, dates and times, geographical location, sales etc. The relationships are formed because a typical transaction will invoke many links in these dimensions. A customer will purchase products at a particular location and time. The delivery may occur at different locations and times and the payment at other locations and times. This kind of multidimensional analysis really focuses questions around the Who, What, Where and When of doing business.
- Who could be a good customer or supplier?
- What might be a good area for us to target a campaign to increase sales?
- When is a good time to place replacement orders from our suppliers given likely demand and seasonality?
- Where are we not performing as well as we should? Which sales areas are winners and which are losers?
Along with the idea of dimensions comes the concept of hierarchies. A hierarchy is often used in many parts of business and the OLAP technology has ways to store and analyse these. Essentially a hierarchy is where a whole is split into many parts which may also split into many part themselves. It is common to view geographical data in a hierarchy. A company may split its activities into large regions such as Central Europe and Asia. Each region will have subsidiaries in the countries of that region and each country will further split into operating areas such as states, cities, towns and so forth.
OLAP cubes can perform calculations using all of the data stored in the cube. These calculations are powerful because they will link many data sources together. Here are some typical examples:
- The production of growth rates from raw data. The system can easily provide month-on-month, quarter-on-quarter, year-on-year or trailing 12 month averages. OLAP cubes can easily generate compound annual growth rates too.
- Consolidations of detailed country data into regional, area and world totals.
- Production of business ratios linking completely different data sources such as sales volume per number of Salesforce heads.
Cubes can also allow for ad-hoc queries to be formulated by users. An analyst in a supermarket may wish to correlate sales of ice cream with rises in temperature to understand if there is a pattern that can be deciphered. The great advantage of understanding these patterns is that it allows business to be very proactive and not reactive.
Our OLAP system holds all of our data together in a way that we can see all the relationships between the dimensions. It also allows us to drill in to the data to gain insight; we can see what is happening right now and also compare to past history; we can drill down to see why things are happening by identifying problem (or good) products, customers, locations; we can use forecasting, trends, seasonality and other inputs to tell us what is likely to happen in the future. Data can be explored from any viewpoint and this will allow users to really understand what is driving the business.