The Method Of Olap Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

LAP: On-Line Analytical Processing. Refers to array- oriented database applications that enable users (analysts, managers and executives) to view, navigate through, manipulate, and analyze multidimensional databases. With OLAP software, users 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. Exemples of OLAP operations are: Drill-down, Drill-through, Roll-up, Slice, Dice, Pivot, etc.[1]

Decision support software that allows the user to quickly analyze information that has been summarized into multidimensional views and hierarchies. OLAP tools are used to perform trend analysis on sales and financial information. They enable users to drill down into masses of sales statistics in order to isolate products that are the most volatile.[1]

Traditional OLAP products are also called "multidimensional OLAP" (MOLAP) because they summarize transactions into

multidimensional views ahead of time. Data are organized into a cube structure that can be rotated by the user, which is particularly suited for financial summaries. Queries are fast because the consolidation has already been done.[2]

What is OLAP?

OLAP allows business users to slice and dice data at will. Normally data in an organization is distributed in multiple data sources and are incompatible with each other. A retail example: Point-of-sales data and sales made via call-center or the Web are stored in different location and formats. It would a time consuming process for an executive to obtain OLAP reports such as - What are the most popular products purchased by customers between the ages 15 to 30?

Part of the OLAP implementation process involves extracting data from the various data repositories and making them compatible. Making data compatible involves ensuring that the meaning of the data in one repository matches all other repositories. An example of incompatible data: Customer ages can be stored as birth date for purchases made over the web and stored as age categories (i.e. between 15 and 30) for in store sales.

It is not always necessary to create a data warehouse for OLAP analysis. Data stored by operational systems, such as point-of-sales, are in types of databases called OLTPs. OLTP, Online Transaction Process, databases do not have any difference from a structural perspective from any other databases. The main difference, and only, difference is the way in which data is stored.

Examples of OLTPs can include ERP, CRM, SCM, Point-of-Sale applications, Call Center.

OLTPs are designed for optimal transaction speed. When a consumer makes a purchase online, they expect the transactions to occur instantaneously. With a database design, call data modeling, optimized for transactions the record 'Consumer name, Address, Telephone, Order Number, Order Name, Price, Payment Method' is created quickly on the database and the results can be recalled by managers equally quickly if needed.[3]

Figure 1. Data Model for OLTP. [3]

Data are not typically stored for an extended period on OLTPs for storage cost and transaction speed reasons.

OLAPs have a different mandate from OLTPs. OLAPs are designed to give an overview analysis of what happened. Hence the data storage (i.e. data modeling) has to be set up differently. The most common method is called the star design.

Figure 2. Star Data Model for OLAP. [3]

The central table in an OLAP start data model is called the fact table. The surrounding tables are called the dimensions. Using the above data model, it is possible to build reports that answer questions such as:

• The supervisor that gave the most discounts.

• The quantity shipped on a particular date, month, year or quarter.

• In which zip code did product A sell the most.

To obtain answers, such as the ones above, from a data model OLAP cubes are created. OLAP cubes are not strictly cuboids - it is the name given to the process of linking data from the different dimensions. The cubes can be developed along business units such as sales or marketing. Or a giant cube can be formed with all the dimensions.

Figure 3. OLAP Cube with Time, Customer and Product Dimensions. [3]

OLAP can be a valuable and rewarding business tool. Aside from producing reports, OLAP analysis can aid an organization evaluate balanced scorecard targets.[3]

Figure 4. Steps in the OLAP Creation Process. [3]

Multidimensional Data

The Multidimensional Aggregation Cube

(MAC) Data Model:

In this section we present the Multidimensional Aggregation Cube data model. MAC is a user-centric conceptual data model that attempts to cover the requirements described in the previous section in order to provide a highly expressive and intuitive modeling methodology for the information used in multidimensional analysis.

The MAC model uses concepts that are close to the way OLAP users perceive the information. The model tries to be expressive providing the means to model complicated real-world scenarios while using a minimal set of concepts that remain as simple as possible. The MAC model describes data as dimension levels, drilling relationships, dimension paths, dimensions, cubes and attributes.

Dimension levels represent classes of dimension members. Each dimension member represents some instance of a real-world property that an OLAP measure may have. Distinct dimension levels can be related by means of a drilling relationship. A drilling relationship indicates that there is a semantic relationship among the involved levels and describes how the dimension members of the children levels can be grouped into sets that correspond to dimension members of the parent level.

A set of drilling relationships can form a dimension path if several structural requirements are met. A dimension path defines a meaningful composition of drilling relationships and is used to model a valid sequence of abstraction operations (drill-down/roll-up). One or more dimension paths that share common levels can form a dimension.

Finally, we define multidimensional aggregation cubes (MACs) as a relationship among the domains of one or more dimensions. A MAC can have one or more measures. Each one of those can be considered as a simple and atomic attribute of the relationship represented by the MAC. An inctance of a MAC is called a MAC cell or a simple cell. We now give the complete definition of the above terms and provide examples on how they are used.[4]

Multidimensional Database: A database designed for on-line analytical processing. Structured as a multidimensional hypercube with one axis per dimension.[5]

Multi-Dimentional Query Language: A computer language that allows one to specify which data to retrieve out of a cube. The user process for this type of query is usually called slicing and dicing. The result of a multi-dimensional query is either a cell, a 2-dimensional slice, or a multi-dimensional sub-cube.[5]

Multidimensional conceptual view: User-analysts would view an enterprise as being multidimensional in nature - for example, profits could be viewed by region, product, time period, or scenario (such as actual, budget, or forecast). Multi-dimensional data models enable more straightforward and intuitive manipulation of data by users, including "slicing and dicing".[6]

OLAP operations

The analyst can understand the meaning contained in the databases using multi-dimensional analysis. By aligning the data content with the analyst's mental model, the chances of confusion and erroneous interpretations are reduced. The analyst can navigate through the database and screen for a particular subset of the data, changing the data's orientations and defining analytical calculations. The user-initiated process of navigating by calling for page displays interactively, through the specification of slices via rotations and drill down/up is sometimes called "slice and dice". Common operations include slice and dice, drill down, roll up, and pivot. [7]

Slice: A slice is a subset of a multi-dimensional array corresponding to a single value for one or more members of the dimensions not in the subset. [7]

Figure 5. Slice explanation in OLAP cube. The yellow field shows time periods and products just for location "C". [9]

Dice: The dice operation is a slice on more than two dimensions of a data cube (or more than two consecutive slices).[8]

Figure 6. Dice explanation in OLAP cube. The red cube shows information about a chosen time period, product and location. [9]

Drill Down/Up: Drilling down or up is a specific analytical technique whereby the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down). For ex., in Figure 6., the drill down operation has been used, a cube for a chosen time period, product and location has been taken and it has been separated into three other wanted dimensions. [7]

Roll-up: A roll-up involves computing all of the data relationships for one or more dimensions. To do this, a computational relationship or formula might be defined.[7]

Pivot: This operation is also called rotate operation. It rotates the data in order to provide an alternative presentation of data - the report or page display takes a different dimensional orientation.[7]

How it works - Efficiency and Valuability


OLAP was used to drill down, slice, and dice the time series data and find lists of genes induced and suppressed in each of the specified time intervals. OLAP was used to find commonly induced or suppressed genes at two or more time points and in one or more biosamples. OLAP was very quick and efficient in providing those reports. On average OLAP only took 2 to 5 seconds to return a result of a query after the cube was constructed (running on a 1.8GHz Pentium 4 workstation with 1GB RAM).

This is a fraction of the time needed to produce similar reports from complex SQL queries and multiple-table joins.

For instance selecting statistically induced genes common to the 6-, 12- and 24-hour time points, which requires 3-table joins, took almost 25 seconds to achieve, whereas the same report took only 1 second with OLAP running on the same system.[10]


OLAP is valuable because of its flexibility. Once the facts and dimensions are defined within the OLAP server, OLAP tools provide an easy way to analyze data by simply dragging and dropping dimensions and facts into the appropriate locations.

Anyone who's ever tried to develop a cross-tab report will appreciate the simplicity of being able to drag the dimensions and facts into position. If you've never developed a cross-tab report, please note that it is not an easy task. Typically, a substantial amount of time is spent trying to figure out how to make the data convert into the rows and columns. The problem is that every change to the report requires a great deal of effort to execute. In contrast, with OLAP it's as simple as dragging a new dimension in place and removing existing dimensions.

Making the cross-tab report easier is certainly valuable, but it is not an end unto itself. The desired end result is to help transform data into information. It just so happens that many people approach the process of understanding their data as the development of cross-tab reports.

OLAP is useful in helping to determine why the data appears the way it does. For instance, if the sales for North America are way up or way down for a given quarter, it's easy to expand the North American geography into the states to see which state or states may be responsible for the difference.

By progressively expanding portions of levels within a dimension, it is possible to drill-down into progressively more detail, but only the detail that is necessary to spot a trend or a problem.[11]

How it functions:

OLAP is designed to convert data into usable information by allowing the aggregation of data, even when you don't know what characteristics may be important to the question. It works on facts, and facts are numbers.[11]

A fact could be a count of orders, the sum of the order amounts, or an average of order amounts.

Figure 7. OLAP cube example in three dimensions:

Product, Location, Year.


OLAP cube is the fastest and the most efficient method for answering analytical multidimensional questions. It is preferred to be used in need of getting gathered information in one place, the user just needs to direct the software to their own data ware house and all the information will be presented in a multidimensional graphical way, depends on the fields the user wants to be analized.