What Are OLAP Cubes?

Solving today’s business problems requires unpacking complex data relationships. Online analytical processing (OLAP) enables teams to quickly analyze multiple data dimensions, making it ideal for decision-making and problem-solving. Because OLAP can handle massive volumes of data efficiently, it’s well suited for data mining and advanced analytics. In this article, we look at what OLAP is, how it works, and the important role that it plays in the data discovery process. We also discuss the OLAP cube, a key component of the original OLAP system. 

OLAP Use Cases

OLAP systems are used in a range of industries to answer a broad variety of business questions. OLAP is especially useful for complex analysis use cases, such as predictive analytics.

Manufacturing

OLAP can be used for trend analysis, combining data from multiple sources including historical sales data, consumer behavior, and other related data such as seasonal changes in consumer demand. Manufacturers employ OLAP to help them accurately plan production. 

Healthcare

OLAP is used in a variety of different ways in the healthcare industry. For example, aggregating patient data from multiple sources can help physicians more accurately predict health risks and improve outcomes. And custom reports that combine data on price, customer location, and health insurance products are used to provide insurers with effective means for measuring profitability.

Financial services

Financial services companies use OLAP systems to identify and reduce risk. By analyzing third-party data, customer data, demographic information, and transaction data, organizations can improve customer due diligence, screening, and fraud detection.

OLAP and OLAP Cubes

OLAP is a processing system that analyzes multiple data dimensions simultaneously from a unified data store, such as a cloud data warehouse or data lake. OLAP is the tool of choice for teams seeking to understand complex data relationships.

Before the advent of the columnar database structure common to cloud data warehouses (CDWs), OLAP systems relied on a structure called the OLAP cube. In traditional OLAP systems, OLAP cubes serve as multidimensional staging platforms that allow users to combine data into organized structures for more efficient analysis. OLAP cubes are usually grouped by business function, so teams can easily find the data sets relevant to their business questions. 

How the OLAP cube enables multidimensional data analysis

Although on-premises relational databases can certainly be used to perform similar functions as OLAP, their speed and performance slow significantly under the high data volumes required for this type of analysis. For this reason, OLAP cubes were developed to add one or more layers atop a single two-dimensional table. For example, an analysis of sales data may make use of an OLAP cube that includes six data dimensions: salesperson, region, dollar amount sold, product type, month, and year. Prior to the advent of the modern cloud data warehouse, data analysts custom-created OLAP cubes to process and analyze the specific data layers needed to support the inquiry at hand. 

OLAP cubes in modern cloud data warehouses

Modern cloud data warehouses have massive compute power and are capable of staging multiple types of data in columnar database structures. For this reason, CDWs don’t require the use of OLAP cubes. However, many organizations find the OLAP system highly effective for business intelligence, so they continue to run OLAP queries (without building cubes) within a CDW that supports it, such as Snowflake. 

Steps Involved in OLAP

Most OLAP workflows involve four primary steps: extracting data from the original sources and loading the data into the CDW or data lake, preparing the data, building a cube if needed, and accessing the data for analysis.  

Data extraction: The first step is to extract the relevant data from original sources, including transactional databases and cloud applications. Once extracted, the data is typically loaded into a unified storage system. 

Data preparation: To ensure data quality, the extracted data must be transformed, tested, and documented. The preparation process includes integrating, cleaning, de-duplicating, restructuring, filtering, aggregating, and joining as needed. 

Building the OLAP cubes: After the relevant data has been prepared, if necessary, data engineers then create OLAP cubes for data analysis. This step includes modeling the OLAP cubes before deploying them to a dedicated server. 

Accessing data: Now, the data is ready for analytics and BI tools, and users can access it through dashboards, query the data, and execute data analysis.

OLAP in Snowflake 

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. Quickly analyze and prototype in Snowsight and take advantage of tight integrations with your favorite business intelligence and advanced analytics tools. Snowflake’s performant, elastic architecture scales up and down, on the fly or automatically, with per-second pricing. 

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