OLAP

OLAP is an abbreviation of On-line Analytical Processing, which is not simply a name of a particular product, but a name of real technology. The elaborations of the managers in business decisions belong to the fields, which are difficult to automate. At present, however, there is a possibility to help the managers in the process of taking decisions, their selection and acceptance. This can be done by OLAP.

Data sources Data warehouses OLAP End users
From the data sources through data warehouses to OLAP and end users. The end user uses BI2M.


Past attempts on taking decisions look like this: The manager goes to the specialist from the information department and shares his question with him. After that the specialist from the information department builds a query to the current system for data processing, which is based on relational database. The specialist receives a report, makes an interpretation of the report and brings it to the management personnel. Of course, such system covers somewhat the decision support, but has extremely low effectiveness and many disadvantages. An exiguous quantity of data is used to support critical decisions. There is another problem. Similar process is too slow, because the process of writing the queries and interpretation of the report is long. It takes many days, while the manager must take a decision immediately. If we give an account, that after the receiving of the report the manager may be interested in another question (more precise or requiring examination of the data in another section), this slow cycle must repeat. Another problem is the different fields of activity of the specialist from the information department and the manager. They may think in different categories and may not reach an agreement. This will require additional specifying iterations and again time which is never enough. Another important problem is the complexity of the reports. The manager has not enough time to select the values of interest from the report. Furthermore they can be too many (let’s remember huge reports with many pages, where several of them are useful, and the rest are just in case). Let’s note as well, that the work on the interpretation lies first of all on the specialist from the information department. Let’s say that the knowledgeable specialist has to do routine and less effective work on drawing charts etc., this naturally will not affect good his qualification Moreover, it is not a secret, that “good-wishers” can appear in the chain of interpretation, and they can be interested in premeditated contortion of the incoming information.

In reality the problems are not due to the low quality of the current systems, but because of fundamental difference between the activity which is automated by them, and the work on elaboration and taking decisions. The difference is that the data in existing system is simply records for events and facts, but not information in the full sense of the word. The information is something, which decreases indefiniteness in any sphere. In connection with unfitness of the systems, created on the base of relational databases, in the field of decision support, doctor Edgar Codd, the inventor of the relational data model of databases say: "Having an RDBMS doesn't mean instant decision-support nirvana. As enabling as RDBMSs have been for users, they were never intended to provide powerful functions for data synthesis, analysis, and consolidation (functions collectively known as multidimensional data analysis)". Be speaking of synthesis of information, to convert the data from the existing systems into information, even into quality evaluations. OLAP allows such transformation.

The idea of multidimensional data model is in the base of OLAP. Human thinking is multidimensional by nature. When you ask questions, you put restraints, which formulate the questions in many dimensions. That’s why the analysis process on multidimensional model is so close to the reality of the human thought. The factors, which influence on the work of the firm (for example: time, products, branches of the company, geography) are taken as dimensions in the multidimensional model. This way you receive a hypercube (naturally the name is not very felicitous, because a cube is usually a figure with equal ribs while in the given instance it is not so, which after that fills with the indicators of the firm activity (prices, sales, plans, profits, losses etc. The dimensions of the hypercube can be hierarchical and there can be relations between them. Through the analysis process the user can see the data from different view points, in different sections and solving concrete problems . Different operations can be executed on the cubes, including prognostic and conditional planning (type of analysis “what, if”). It is possible to perform operations on hypercubes having different number of dimensions.

It is common knowledge when in 1985 doctor Codd published his rules for creating RDBMS (Relational Database Management System), they evoked a storm of response and later on they had a powerful effect on DBMS industry in general. It is not well known, however, that in 1993 Codd published work named “OLAP for user – analysts: an IT mandate”. There he exposed the basic conceptions of OLAP and defined 12 rules used for defining and evaluating OLAP software. They are:

There are now several sets of rules and definitions proposed by industry experts and vendors. The definition below was produced by the now inactive OLAP Council (a consortium of OLAP vendors and users):

OLAP definition

"On-Line Analytical Processing” (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.

OLAP database example

Typical OLAP cube used in BI2M

Basic OLAP functionality

Where can be OLAP useful?

The main object in OLAP is the cube, which contains the current analytical data in interest of the end user. To support the questions which the user asks, the cubes organize the data in dimensions and measures in multidimensional structure. For example let’s see the question: “What were our general hardware sales in North - eastern region over the first quarter of this year?”. The data cube, which answers this question, includes three dimensions and one measure:

Cube - set of data, organized and summarized in multidimensional structure, defined by set of dimension and measures.

Multidimensional structure - example of database, which examines the data not as relational tables and columns, but as informational cubes, which contain dimensions and summary data in cells. Every cell is addressed by a set of coordinates, which define a position in the dimensions of the structure. For example a cell with coordinates {Sales, 2002, San Francisco, software} would contain summary for sales of software in San Francisco during 2002.

Dimensions - The dimensions are structure attribute of cubes. They are organized hierarchies of categories and levels. This categories and levels describe similar sets of members, on which the user wants to found an analysis.

The dimensions are hierarchical and in most cases their members are ordered in pyramid-like configuration. The horizontal row is a result of values on columns with one and the same level in the hierarchy of the dimension, and the vertical row is a result of values of columns with different levels of the hierarchy of the dimension. For example the dimension Factory Location:

Image to OLAP technology and databases

This dimension is defined by choosing a column Region, and then a column City from the following table:

City_ID Region City
1 East Maine
2 East Ohio
3 West Idaho
4 West Texas

The dimensions categorize the numeric data /measures/ in a cube for analysis. For example if a measure in the cube is Product Count, its dimensions are Product, Time and Factory Location. The end users having access to the cube can divide Product count in different categories according to Product, Time and Factory Location.

We recommend our BI2M application for all OLAP users. Trial version is available for download.



Go to Menu