OLAP Cubes
Online Analytical Processing (OLAP) is a technology that allows for fast and interactive data analysis of multi-dimensional data. OLAP cubes or Hypercubes are arrays of data across many dimensions like time, location, and product, which makes them easier to query and analyze than traditional database tables.
Why are OLAP Cubes Important?
The need for OLAP cubes comes from the challenges data analysts and data scientists faced when they tried to perform analytic queries on transactional databases. Answering complex questions on relational data requires extensive joins, complex schema designs, and pre-aggregation, which makes it expensive, inefficient, and slow to query. By having the query results structured into a cube, users can slice and dice the pre-computed data more efficiently.
What is the Downside of OLAP Cubes?
In the early days of data warehousing, OLAP cubes were popular because they addressed many issues associated with relational databases that were not built for analytic processing. However, OLAP cubes have two primary shortcomings:
- Downtime: The data in the OLAP cube is only valid for the time the cube is populated; if the underlying source data changes, the cube cannot refresh without required downtime. This works for applications that only rely on historical data but is insufficient for real-time analysis.
- Inflexibility: OLAP cubes must request the dimensions they want in advance, which makes it difficult to explore new variables on the fly.
Today, the use of OLAP cubes has largely been eclipsed by new data warehousing attributes such as columnar data models, star schemas, persistent result sets, and materialized views.
OLAP Cube Functions
The following capabilities define what you can do with an OLAP cube.
Slicing
Slicing allows users to analyze a subset of the multi-dimensional array by specifying a single value for one of its dimensions. For example, with an OLAP cube with dimensions of product categories and sales across stores, over time, you could slice the cube by a dimension of time, like a specific quarter.
Dicing
Dicing, similar to slicing, enables the user to analyze a sub-selection across dimensions by specifying values for several dimensions and multiple criteria. For example, looking at the total sales of a specific product with the dimensions of product, time, and region, you could choose to dice the cube by one particular quarter and a specific product.
Drill-Down
Drilling down on a result allows examination of a result at a more detailed level by navigating hierarchies.
Roll-Up
A roll-up aggregates data at higher levels and is used to provide a big-picture view. A user can view product revenues at an individual store and then perform a roll-up to zoom out to all stores, which puts the particular store’s performance into the context of all stores for a given period.
Pivoting
Pivoting allows the user to view data from multiple perspectives by viewing it from different dimensions.
OLAP Cube Use Cases
Retail
Store managers need to know what products are selling well to determine if they need to order more stock or retire a product entirely. At a regional level, users want to view total sales by store. Their OLAP cubes might have dimensions such as store, total sales, time, or product.
Logistics
Supply chain professionals need to know the quantity of goods each distribution center needs to optimize delivery schedules and warehouse capacity. This cube’s dimensions include region, product sales, quantity on hand, and available capacity.
Finance
A CFO at a multinational corporation needs to analyze business performance across multiple dimensions. The chief financial officer (CFO) must be able to drill down into specific time windows at a regional and national level. In this case, the highest roll-up might be global revenue, which can be viewed against a timeline and broken down by selling price and profitability.
Benefits of OLAP Cubes
Below are the primary reasons businesses use OLAP cubes:
- OLAP cubes are fast because all the data is pre-aggregated.
- OLAP cubes are easy to navigate using simple operations such as roll-ups, drill-downs, pivots, slicing, and dicing.
- They are more interactive than tabular reports and are helpful for simple business intelligence functions.
Actian and OLAP
For OLAP users looking to simplify the business intelligence (BI) life cycle, the Actian Vector analytics database provides a viable alternative to OLAP cubes. It offers superior performance and provides real-time data without the need to pre-aggregate data. By coupling a modern BI solution with Actian Vector, analysts gain the ability to perform real-time analysis, exploration, and visualization using a powerful and intuitive user interface.