OLAP - Online Analytics Processing Example

OLAP stands for OnLine Analytical Processing and it deals with Historical Data or Archival Data. Historical data are those data that are archived over a long period of time. OLAP helps to answer multi-dimensional analytical (MDA) queries in a fast manner.

OLAP and multi-dimensional analysis

Key aspect of OLAP approach is to achieve a multi dimensional analysis of organization data. Let us explore this aspect of OLAP.

Business is a multidimensional activity and businesses are run on decisions based on multiple dimensions. Businesses track their activities by considering many variables. When these variables are tracked on a spreadsheet, they are set on axes (x and y) where each axis represents a logical grouping of variables in a category. For example, sales in units or dollars may be tracked over one year’s time, by month, where the sales measures might logically be displayed on the y axis and the months might occupy the x axis (i.e., sales measures are rows and months are columns).To analyze and report on the health of a business and plan future activity, many variable groups or parameters must be tracked on a continuous basis—which is beyond the scope of any number of linked spreadsheets. These variable groups or parameters are called Dimensions in the On-Line Analytical Processing (OLAP) environment.

Unlike relational databases, OLAP tools do not store individual transaction records in two-dimensional, row-by-column format, like a worksheet, but instead use multidimensional database structures—known as Cubes in OLAP terminology—to store arrays of consolidated information. The data and formulas are stored in an optimized multidimensional database, while views of the data are created on demand. Analysts can take any view, or Slice, of a Cube to produce a worksheet-like view of points of interest. Rather than simply working with three dimensions, companies have many dimensions to track—-for example, a business that distributes goods from more than a single facility will have at least the following Dimensions to consider: Accounts, Locations, Periods, Salespeople and Products. These Dimensions comprise a base for the company’s planning, analysis and reporting activities. Together they represent the “whole” business picture, providing the foundation for all business planning, analysis and reporting activities. The capability to perform the most sophisticated analyses—-specifically, the multidimensional analysis provided by OLAP technology—is an organizational imperative. Analysts need to view and manipulate data along the multiple dimensions that define an enterprise—essentially, the dimensions necessary for the creation of an effective business model.

Types of OLAP systems

There are three broad categories of OLAP systems which are traditionally categorized using following terminologies - MOLAP, ROLAP, and HOLAP.

  1. MOLAP stands for Multi-dimensional Online Analytical Processing. This is the more traditional way of OLAP analysis. In this method, data is stored in a multidimensional cube. The storage is not in the relational database tables, but in a proprietary format specific to a vendor.
  2. ROLAP stands for Relational Online Analytical Processing. In this method, base data and the dimension tables are stored as relational tables and new tables are created to hold the aggregated information. This will again give the appearance of traditional OLAP's slicing and dicing functionality.
  3. HOLAP stands for Hybrid Online Analytical Processing. HOLAP is intended to combine best of both the worlds. Though there is no clear agreement across the industry as to what constitutes "Hybrid OLAP", every one agrees that a database will divide data between relational and specialized storage. For data aggregation is needed, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational database tables.

Why we need an OLAP system

The more data a company can access about a specific activity, the more likely that the plan to improve that activity will be effective. All businesses collect data using many different systems, and the challenge remains: how to get all the data together to create accurate, reliable, fast information about the business. This is where the advantage of OLAP comes into picture.

  • OLAP servers provides better performance for accessing multidimensional data.
  • OLAP systems gives analytical capabilities that are not in SQL or are more difficult to obtain.
  • OLAP's multidimensional view of data provides the foundation for analytical processing through flexible information access. It’s very important benefit, because more control and timely access to strategic information equal more effective decision-making.
  • OLAP is a technology that can be distributed to many users using a variety of platforms.

Future of OLAP systems

Current OLAP systems, referred as traditional OLAP, is facing significant challenges to survive. There are couple of key constraints it has to deal with:

  1. Advance of big data and Hadoop technologies which help to analyse large data without the use of OLAP system
  2. Some of the inherent drawback in the traditional implementation of OLAP systems such as mandatory pre-modeling, dependence of IT, lesses interactive capabilities and more.

Even with these challenges, OLAP is here to stay though with some twists and turns. Organizations will not suddenly abandon the significant investments they’ve already placed into their OLAP infrastructure but, as time and opportunity go on, there will be a transition as companies explore new lower cost, faster methods to the same goal. The end of OLAP is a long way but the process has already begun by exploring alternative technologies.