Home > OLAP Module > What is OLAP ?

What is OLAP ?

OLAP stands for On Line Analytical Processing, and supports multi-dimensional analysis of information. It is the process of extracting information from a data source (this could be a transactional system, or a data warehouse), and compressing it into a format that is optimized for multi-dimensional analysis.

An OLAP database allows business decision makers to analyse data that has been sorted into hierarchical structures. The data is static so all mathematical aggregations can be built into the database query, thereby providing a more efficient and resource friendly means of reporting. This data warehouse can then be pulled into a pivot table within Microsoft Excel, where the user is able to drill down into the report, using the hierarchical dimensions built into the query.

The OLAP Module allows the user to define the dimensions and measures required and then create the .cub file, which will then become a data source for reporting within the Sage Intelligence Reporting Report Manager. As the data remains static, the .cub file should be rebuilt daily, to ensure that the data remains relevant.

OLAP Cubes

OLAP cubes can be considered an extension to the two-dimensional array of a Microsoft Excel spreadsheet. A company might wish to analyze some financial data by product, by time-period, by city, by type of revenue and cost, and by comparing actual data with a budget. These additional methods of analyzing the data are known as dimensions and an OLAP cube allows for the presence of be more than three dimensions for more powerful information analysis.

Functionality

An OLAP cube consists of numeric facts called Cube Measures (or measures) which are categorized hierarchically by Cube Dimensions (or dimensions).

OLAP Views

A business owner may want to view or "pivot" the data in various ways, such as displaying all the cities down the page and all the products across a page. This could be for a specified period, version and type of expenditure. Having seen the data in this particular way the business owner may then wish to view the data in another way. The view could effectively be re-oriented so that the data displayed now has periods across the page and type of cost down the page. OLAP allows users to pivot data very fast and very efficiently.

OLAP Hierarchy

Each of the elements of a dimension could be summarized using a hierarchy. The hierarchy is a series of parent-child relationships, typically where a parent member represents the consolidation of the members which are its children. Parent members can be further aggregated as the children of another parent.

For example May 2005 could be summarized into Second Quarter 2005 which in turn would be summarized in the Year 2005. Similarly, cities could be summarized into regions, countries and then global regions; products could be summarized into larger categories; and cost headings could be grouped into types of expenditure.

OLAP Terminology

The user driven process of creating different views, is sometimes called "slice and dice". Common OLAP functions include slice and dice, drill down, roll up, and pivot.

• 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.

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

• 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).

• 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.

• Pivot: This operation is also called rotate operation that rotates the data in order to provide an alternative presentation of data.