What Is OLAP? OLAP Defined

Online Analytical Processing — or OLAP, for short — is an important way of organizing and presenting data. Today, nearly every business collects reams of data in digital formats, but many of them still store and analyze it in a multitude of spreadsheets or similar tables in a database. That has led to the notion of "spreadsheet hell," where the people who must enter and analyze the data are overwhelmed by an unruly mob of conflicting, difficult-to-work-with and frequently error-prone spreadsheet files.

Enter OLAP, which was developed to help companies escape from this spreadsheet underworld. A core element of most data warehouse applications, OLAP enables fast, flexible data analysis for business intelligence (BI) and decision support.

What Is OLAP (Online Analytical Processing)

With a spreadsheet, data is presented two-dimensionally in rows and columns. For each item of data, both the row and the column must always have an entry, even if this means that the same date, the same product or the same customer is listed many times. This results in very large spreadsheets that quickly become cumbersome and awkward to use — all the more so when more than one person has access and makes changes to them.

Spreadsheets are similar to SQL and relational databases, as well as to traditional data warehouses, which all store data in rows and columns in a two-dimensional format and suffer from some of the same shortcomings. Pulling data from a very large relational database, for instance, can be a slow process and reorganizing the results to answer different questions can be a labor-intensive chore.

OLAP allows data to be stored in three or more dimensions instead of just two, which leads to a number of advantages. Presented in the form of a cube, the same data point (like a date or a SKU code) needs to be entered only once, making for faster searches and easier extraction. Also, cubes can be sliced, diced and rotated in a number of ways, allowing the user to narrow or broaden a search and take a variety of approaches to visualizing the data.

This makes OLAP a powerful tool for data discovery and what-if forecasting, and is the reason it is used as the underlying technology for many business intelligence (BI) applications.

What Is an OLAP Cube?

The OLAP cube's data structure is optimized for very rapid data analysis. It includes numeric facts called measures, which are organized along a three-way axis.

For example, a company might organize its sales data by product, timeframe and location for comparative purposes. In this case, the OLAP cube's three dimensions might be product, month and store. Later, more layers can be added, creating additional dimensions. The top layer of the cube might organize sales by store, for instance, but additional layers could be added for city, state and country. Multidimensional OLAP databases with more than three dimensions are known as hypercubes.

Smaller cubes can also be carved out from the larger parent cube. The store layer, for example, could contain cubes arranged by product, month and salesperson.

Key Takeaways

• OLAP is a valuable tool for analyzing a wide assortment of business data.
• It can be used to track and monitor a company's day-to-day operations, as well as for forecasting and planning purposes.
• OLAP provides a multitude of ways to parse and present data, making it ideal for business intelligence, decision support and data mining.

OLAP Explained

Businesses use many factors to track their activities. When these are tracked on a spreadsheet or a relational database management system (RDMS), they are logically grouped into two dimensions on the 'x' and the 'y' axes.

Monthly sales, for example, may be tracked by displaying products in a column on the y axis and the months of the year in a row on the x axis. Each x and y intersection would hold the entry for the amount of sales of a particular product in a given month. Realistically, however, a business may also want to track its sales by location, salesperson, discounts applied and any number of additional factors. Managers need to do this in order to drill down into particular questions ("Why did this product stop selling in Chicago in July?") and to get a more complete picture of how the business is faring ("Why was revenue flat even though sales were up for the year? Was too much discounting taking place?").

Using OLAP, new dimensions can be added to an OLAP data cube to track each of these variables. Then analysts can take any view — any slice, section or angle of the cube — to produce a report that contains the key points of interest. Monthly product sales in San Francisco? No problem. Compare sales in months with more discounting to months with less discounting? Also done in a snap.

Better still, any of this data can be presented visually, making it much easier to identify trends. And the view can be changed by simply rotating the cube. So, a chart that shows which products sold best in New York in November can be turned to show in which month products sales were best in New York.

All this can be done very quickly and with a minimum of fuss, giving a business the answers it needs both for long-term planning and to monitor its day-to-day operations.

How Does OLAP Work?

OLAP performs rapid, in-depth analysis on large volumes of data. The data comes from one or more data warehouses, data marts or some other type of centralized data store. The data can also come from different types of sources, including spreadsheets, emails and text documents, as well as audio and video files.

Once it is extracted, the data is stored in a data warehouse (which could be the same one it came from in the first place), where it is cleansed and formatted into OLAP cubes. The cubes are then loaded onto an OLAP server and some initial calculations take place, which ready the data for further analysis. Using an OLAP client, an analyst or business user can now pull the data from the OLAP cubes by running queries against it.

How Is OLAP Used?

Because OLAP lets business leaders quickly perform an ad-hoc data analysis in a variety of ways, it can be a potent tool for making better-informed decisions. It is ideal for data mining and trend analysis, and for performing complex analytical calculations, including data modeling. The software is also well suited for more routine business functions, such as financial reporting, budgeting and sales forecasting.

Consequently, OLAP serves as the foundation for many different business applications, including BI, business performance management (BPM) and simulation modeling.

Types of Analytical Operations OLAP Can Perform

OLAP cubes support four basic types of data analysis:

1. Drill-downs provide a closer look and reveal more particulars about the data. This allows an analyst to extract details about individual products, for example, such as how well they are selling in stores versus online, or in one part of the country versus another.
2. Roll-ups are the opposite of drill-downs, allowing the analyst to pull back and see the forest, not only the trees. For instance, instead of viewing sales data store by store, or salesperson by salesperson, a roll-up operation allows it to be viewed city by city or region by region.
3. Slicing and dicing a smaller “sub-cube” out of a larger OLAP cube is another way to parse the data. By slicing the data by time period, for instance, an analyst might highlight all sales data for the first quarter. The analyst then might dice it to get a look at first quarter sales data in the New England region.
4. Pivoting or rotating turns an OLAP cube so that one view of the data is replaced by another. So, for example, instead of comparing store sales by month, the analyst can rotate the data to compare monthly sales by store. This is quite similar to creating a pivot table in a spreadsheet but is much easier to do and requires less training.

Types of OLAP Systems: MOLAP vs ROLAP vs HOLAP

There are three basic types of OLAP systems, each with its own advantages and disadvantages:

1. Multidimensional OLAP, abbreviated as MOLAP, organizes data into an OLAP cube and is generally the fastest and most practical way to analyze it. MOLAP is the most common type of OLAP system.

   • The advantages of MOLAP are that it can manage, analyze and store data very quickly. It is also the easiest type of OLAP to use and can be very helpful when an analyst or business leader needs to sort data in a variety of ways.
   • MOLAP's disadvantages are that it can only handle a limited amount of data and is resource-intensive. This means it can be slower to perform queries than some other OLAP systems when larger volumes of data are involved. Also, the data that it holds may be less detailed than the data stored by other types of OLAP.

2. Relational OLAP, also known as ROLAP, works directly with the data in a relational database without first reorganizing it into a cube.

   • The chief advantage of ROLAP is that it is better suited to analyzing large quantities of data to answer very specific questions.
   • The disadvantages of ROLAP are that it can slow down the performance of the RDMS and requires specialized training to use. Also, the analysis it provides is static; the data can't be sliced or pivoted to answer different questions as they come up.

3. Hybrid OLAP, referred to as HOLAP, organizes data into OLAP cubes, but also works directly with data in a relational database.

   • HOLAP's major advantage is that it combines the speed of MOLAP with the scalability of ROLAP, so it can quickly analyze large volumes of detailed data in a variety of ways.
   • The biggest disadvantage of a HOLAP system is its complexity. It is the most difficult type of OLAP system to support and the hardest to use. This can also make it the most expensive type of OLAP system to deploy and maintain.

Comparison of OLAP Systems

OLAP vs OLTP

In addition to OLAP, online transaction processing (OLTP) is another major type of data processing system used by most companies. The first uses data to provide insights; the second executes and keeps track of sales and other business transactions. Data warehouses generally receive their source data from OLTP systems and then use OLAP systems to analyze it.

• OLTP is designed to process hundreds to thousands of transactions per second, or more. To create the necessary speed and accuracy at scale, OLTP processes use small, easy to execute transactions that can be rapidly verified. Transactions either succeed completely, or are entirely canceled, making the OLTP database a definitive source of completed transactions. By design, no transaction should interfere with the completion of other transactions. Common applications include ATMs, ecommerce, credit card payment processing, online bookings, reservation systems and record-keeping tools.
• OLAP is designed to analyze both transactional and historical data. Applications include business intelligence, data mining and trend analysis — not commercial transactions that require real-time response. It is also commonly used for financial reporting, budgeting and forecasting.

Another way of looking at it is that OLTP generates the data that OLAP analyzes.

Comparing OLTP vs OLAP

Advantages of OLAP

OLAP provides fast and intuitive data analysis and reporting. Easy to use, it allows business managers and analysts to perform these functions themselves, which reduces the demand for IT experts and data scientists with specialized skills.

• Users can create quick ad hoc reports. The arrangement of data into OLAP cubes allows users to quickly create ad hoc reports by slicing and dicing the cube data. It is not hard, for example, for a user to create a visual representation of all product sales for a specific month, year, store or country.
• Ability to generate regular reports. Another significant advantage is that standard (e.g., quarterly) reports can be automated. This is a big time-saver and reduces the possibility that a reporting error will be made. Relevant data can flow directly into the OLAP cube, instead of first being manually collected and validated.

By helping companies make more and better use of their data, OLAP promotes better decision-making, greater operational efficiencies, faster response times and a more competitive stance.

The Disadvantages of OLAP

Although it reduces business users' dependence on IT teams, OLAP does not eliminate that dependence — and this may be its biggest drawback.

• Data must be put into a data warehouse first. OLAP can't directly access transactional data without first entering it into a data warehouse, where it must be preformatted before it can be analyzed. This can be a time-consuming process and means business users are still dependent on IT professionals to lay the groundwork before they can begin conducting queries.
• Modifications require resources. Moreover, any modification to the data structure of an OLAP cube requires the entire cube to be updated, and this, too, requires IT expertise and can be a slow process.
• There's potential for delays. Since the demands of business users are many and IT resources are often few, this dependency can become a significant bottleneck. OLAP projects may be delayed and, absent a good analysis, the business may be hampered in its ability to react quickly to changes in the marketplace.
• OLAP queries may require advanced skills. Another potential limitation is that even though performing OLAP queries is relatively easy, not every business user may have the requisite skill. In this case, the user may be forced to rely on a data analyst or some other intermediary to perform queries on his or her behalf, which slows down the process and makes it more cumbersome.

Choosing an OLAP Solution

There are many OLAP systems on the market, but a few key attributes separate the best of these tools from the rest of the pack. These include technical features such as a robust metadata layer, the ability to perform database operations in parallel, and strong security measures, as well as business benefits such as rapid query execution, a user-friendly interface and low total-cost-of-ownership.

The most important consideration is to select a set of OLAP tools that can work with the data warehouse and other IT systems already in place, and powerful enough to meet the needs of the business. The amount of training that's required to make effective use of the system should also be taken into account.

Implementing OLAP

OLAP implementations will vary based on the type of software used, the data sources that are tapped to feed the system and the business reasons for implementing it in the first place. Each industry or line of business will have its own unique requirements that dictate the type of data it needs to analyze, where it will come from and how its OLAP cubes should be structured. Each type of business will have its own reporting requirements — and perhaps even individual users. Business managers may prefer a dashboard, while analysts may be better served by a web-based front end.

One OLAP solution could be used by finance professionals seeking to analyze data originating in an enterprise resource management (ERP) system, while another might be used by a medical institution to analyze patient data drawn from an electronic health record (EHR) system. In these and many other cases, the way OLAP is implemented would be very different.

Conclusion

Regardless of the specifics of its implementation, online analytical processing can benefit most modern businesses. OLAP's ability to quickly, accurately and dynamically analyze large quantities of data brings needed speed and flexibility to help shape businesses' strategic direction, as well as tactical day-to-day decisions.

OLAP FAQs

What is the difference between OLAP and OLTP?

OLTP is designed to process transactions such as credit card payments as quickly and accurately as possible, whereas OLAP is designed to analyze both transactional and historical data for business intelligence, financial reporting and other applications.

What is an example of OLAP?

A manufacturer could use OLAP to analyze its product sales. Using OLAP cubes, it could determine which of its products are selling better at different times of the year, in different parts of the country and at different price points. Then the manufacturer could conduct what-if queries to determine the extent to which discounts and promotional offers could boost its sales in a given month or sales region.

How is OLAP used with data warehousing?

OLAP cannot work with transactional data unless it is first stored and formatted in a data warehouse. Data from the data warehouse is then tapped to feed OLAP cubes.

How is OLAP used with data mining?

Once the data in a data warehouse is organized into OLAP cubes, a data mining application can search the cube data in a variety of ways to identify trends and meaningful patterns.