Online Analytical Processing for Complex Business Questions

As more data becomes available and more organizations seek to become data-driven in all their operations, technologies have evolved to support the demand for complex, multidimensional analyses. One of these technologies is online analytical processing (OLAP). OLAP enables teams to conduct data analysis quickly on massive volumes of data, so it’s ideal for data mining and advanced analytics as well as general business intelligence.

What Is Online Analytical Processing?

OLAP is a set of technologies that enables quick multidimensional analytical queries.

Unlike traditional relational databases, OLAP does not store individual transaction records in a two-dimensional format. Instead, it uses a data structure called an OLAP cube to organize multiple dimensions of data to be analyzed. This cube contains all of the data dimensions needed to define a business problem. For example, an organization reporting sales may create an OLAP cube that includes six data dimensions: salesperson, dollar amount sold, region, product type, month, and year. OLAP cubes are capable of accommodating any number of data arrays, making it possible for users to analyze multiple data dimensions to solve complex business problems. 

OLAP is useful primarily because it empowers teams to see a variety of different views of the data so they can find the insights they need rapidly. Most business questions involve a variety of dimensions, so having the ability to query data in a multidimensional manner is crucial to accessing insights at the speed of business. 

How Is OLAP Different from OLTP?

Online transactional processing (OLTP) systems use relational databases that function in two dimensions. OLTP systems are transactional, capable of processing massive numbers of transactions in real time. In contrast, OLAP systems are analytical in nature, optimized for use by data scientists, analysts, and business decision-makers. OLAP tools are used to support business intelligence (BI), data analytics, and data mining applications. Many organizations use OLTP as a preparatory first step for OLAP.  

The Online Analytical Processing Workflow

Online analytical processing system setup follows a standardized, repeatable workflow.

Data extraction

The first step in the OLAP process is the extraction of relevant data from the original sources. Once extracted, the data is typically placed into a unified storage system, most often a cloud data warehouse. 

Data preparation

Before the data can be analyzed, it must be transformed, tested, and documented. Preparation ensures data is integrated, cleaned, de-duplicated, restructured, filtered, aggregated, and joined as needed. 

Building a cube

Once all relevant data has been prepared, data engineers create the OLAP cubes, modeling them before they’re deployed to a dedicated server. Once the cube has been created, the data it contains can’t be updated. This requires a new cube for each query. Some data warehouses such as Snowflake run OLAP queries without the need to set up OLAP cubes. These providers use online analytical processing as a foundational part of their database schema.

Accessing data

At this point, the data is ready for analytics and BI tools and can be accessed through dashboards. Users can query the data and execute data analysis.

5 Types of OLAP Analysis Operations

The multidimensional nature of OLAP supports a variety of analysis operations. Although OLAP systems utilize a specific type of SQL language called multidimensional expression (MDX), most databases used to conduct OLAP analysis are also capable of supporting standard SQL queries. 

Drill-down

The drill-down operation enables users to move from a macro to a micro view of a data dimension. One common example would be online sales data. The drill-down function could be used to move from annual sales to monthly sales to daily sales. 

Roll-up

The reverse of the drill-down function, roll-up allows users to move from a more detailed view of a data dimension to a broader one. Sticking with the sales data example from above, the roll-up operation could be used to move from store-level sales, to city-level, to country-level. 

Slice

Slice operations can be used to create a smaller subset of data by removing a single dimension. This makes it easier to analyze row-level details apart from the other dimensions represented in the cube. An example of a slice operation would be to select and remove all of the data from one specific time dimension such as a fiscal quarter.

Dice

Dicing offers the same separation functionality as slicing, but it selects multiple dimensions. One application of the dice operation would be to pick several related data dimensions such as the amount of product sold, the region in which it was sold, and the time frame in which it was sold.

Pivot

The pivot function allows users to display the data in different ways. Not unlike the pivot feature found in Excel, the OLAP pivot is easier to use, faster to execute, and provides a dynamic, multidimensional view of the data that would be difficult to access using traditional spreadsheets.

OLAP Use Cases Across Industries

Organizations from a variety of industries are harnessing the power of online analytical processing to perform multidimensional analysis at high speeds on large volumes of data. Here are a few ways organizations are using OLAP to quickly analyze their data.

Healthcare

Because of OLAP’s speed, it’s ideal for interpreting the textual, nonnumerical data that healthcare providers need to optimize patient care. OLAP makes it possible to easily isolate factors that may play a significant role in patient outcomes such as socioeconomic status or geographic location.

Manufacturing

Manufacturers often use OLAP to conduct an in-depth product defect analysis for quality-control initiatives. Using the drill-down function, quality-control experts can quickly isolate defective products by shift, production line, and other dimensions to isolate factors that may be contributing to an uptick in product defects.

Retail

Retailers use OLAP to more accurately spot emerging consumer trends by region and country. OLAP also helps retail marketing teams identify the right products for upcoming sales promotions by tracking what customer segments purchase what products and which  SKUs are likely to be purchased together. 

Snowflake for Online Analytical Processing

Snowflake is a fully managed platform with unique features that make it an ideal solution to support data processing and analysis. Snowflake uses OLAP as a foundational part of its database schema and acts as a single, governed, and immediately queryable source for your data. In addition to its built-in analytics features, the platform offers seamless integrations with popular business intelligence and analytics tools. And because Snowflake quickly scales up and down, on the fly, or automatically, with per-second pricing, you can efficiently process and analyze massive data sets as the need arises. 

See Snowflake’s capabilities for yourself. To give it a test drive, sign up for a free trial.