OLAP Introduction and Overview

What Is OLAP?

Online Analytical Processing (OLAP) is a technology that is used to create decision support software. OLAP enables application users to quickly analyze information that has been summarized into multidimensional views and hierarchies. By summarizing predicted queries into multidimensional views prior to run time, OLAP tools provide the benefit of increased performance over traditional database access tools. Most of the resource-intensive calculation that is required to summarize the data is done before a query is submitted.

Data Storage and Access

Decision makers are asked to make timely and accurate decisions that are based on the past performance and behavior of an organization as well as on future trends and directives. To make effective business decisions, business analysts must have access to the data that their company generates and responds to. This access must include timely queries, summaries, and reviews of numerous levels and combinations of large, recurrent amounts of data. The information that business analysts review determines the quality of their decisions.

Organizations usually have databases and data stores that maintain repeated and frequent business transaction data. These data storage systems provide simple yet detailed storage and retrieval of specific data events. However, these systems are not well suited for analytical summaries and queries that are typically generated by decision makers. For decision makers to reveal hidden trends, inconsistencies, and risks in a business, they must be able to maintain a certain degree of momentum when querying the data. An answer to one question usually leads to additional questions and review of the data. Simple data stores do not successfully support this type of querying.

A second type of storage, the data warehouse, is better suited for this. Data is maintained and organized so that complicated queries and summaries can be run. OLAP further organizes and summarizes specific categories and subsets of data from the data warehouse. This results in a robust and detailed level of data storage with efficient and fast query returns. SAS OLAP cubes can be built from either partially or completely denormalized data warehouse tables. Stored, precalculated summarizations called aggregations can be added to the cube to improve cube access performance. Aggregations can either be pre-built relational tables, or you can let the cube create its own optimized aggregates.

Benefits of OLAP

The ability to have coherent, relevant, and timely information is the reason OLAP has gained in popularity. OLAP systems can help reveal evasive inconsistencies and trends in data that might not have been seen before. OLAP users can intuitively search data that has been consolidated and summarized within the OLAP structure. In addition, OLAP tools allow for tasks such as sales forecasting, asset analysis, resource planning, budgeting, and risk assessment. OLAP systems also provide the following benefits:

fast access, calculations, and summaries of an organization's data
support for multiple user access and multiple queries
the ability to handle multiple hierarchies and levels of data
the ability to presummarize and consolidate data for faster query and reporting functions
the ability to expand the number of dimensions and levels of data as a business grows

Implementation

To fully understand the benefits of OLAP and the details of its effective implementation, it helps to examine the technology from two perspectives— that of the users, and that of the information technology (IT) administrators who are responsible for OLAP implementation. The users, typically business analysts and executives, expect the data to be organized according to categories that reflect the way they think about the enterprise. For IT administrators, OLAP can present a long list of technical issues, including these concerns:

storage requirements and associated costs
client and server capabilities
maintenance activities such as update and backup
performance considerations such as the amount of time that is required to build a multidimensional model
the ability of the OLAP solution to integrate with current or planned data warehouse strategies and architectures
security requirements for cube data

The SAS OLAP Server and SAS OLAP Cube Studio provide resources and functionality to address these concerns. When building SAS OLAP cubes, you can perform functions and specify settings that affect the following:

cube aggregation storage and query performance
cube dimension security and identity-driven security
updates of cube data
maintenance of cubes (such as adding calculated members or changing an OLAP schema.)

Because SAS OLAP is a component of the SAS Intelligence Platform, it works in conjunction with other SAS applications to provide an overall solution to the access and maintenance of a company's data.

OLAP Variations

OLAP technology can be further defined by the methods for storing and accessing data and by the performance of queries against that data. SAS OLAP supports three different variations of OLAP technology:

MOLAP (multidimensional OLAP)

is a type of OLAP that stores summaries of detail data (aggregates) in multidimensional database structures. MOLAP cubes are most suited for slicing and dicing of data and are used when performance and query speed is critical. For further information about MOLAP, see the topic MOLAP Aggregation Storage.

ROLAP (relational OLAP)

is a type of OLAP in which multidimensional data is stored in a relational database such as a SAS table or an ORACLE table. ROLAP is more scalable than other OLAP types and handles extensive amounts of data well. Although performance can be somewhat slow, ROLAP is limited only by the size of the relational database it is identified with. For further information about ROLAP, see the topic ROLAP Aggregation Storage.

HOLAP (hybrid OLAP)

is a type of OLAP in which relational OLAP (ROLAP) and multidimensional OLAP (MOLAP) are combined. In HOLAP, the source data is usually stored using a ROLAP strategy, and aggregations are stored using a MOLAP strategy. It combines the best features of both ROLAP and MOLAP. This combination usually results in the smallest amount of storage space. In HOLAP, aggregates can be precalculated and can be linked into a hybrid storage model.