An OLAP cube (Online Analytical Processing) or data cube, also known as a cube operator, is a term commonly used in data warehouse theory to represent data logically. The data is arranged as elements of a multidimensional cube. The dimensions of the cube describe the data and allow easy access. Data can be selected across one or more axes of the cube. The term OLAP (Online Analytical Processing) comes from data analysis.
This type of representation is beneficial for analyzing data, as different aspects (dimensions) of the data are accessed in the same way. Hence the use in OLAP applications, which analyze the data in a data warehouse or prepare it visually.
Basic Operations of OLAP Cube
- Slicing: Cutting out slices from the data cube
- Dicing: This creates a smaller cube that contains a subvolume of the total cube. This is done by partial constraints on one or more dimensions.
- Pivoting/Rotation: Rotate the data cube so that at least one other dimension becomes visible
- Drill-down: Break down aggregations of an information object to detailed values; “Zoom in”
- Drill-Up/Roll-Up: Counter-operation to drill-down; Condensation to a higher hierarchical level (e.g. from the monthly to the yearly view)
- Drill-Across: dimension at the same hierarchical level; Consideration of adjacent dimension elements (different region, different product, different month)
- Drill-through: while drill-up or drill-down moves vertically through the data (hierarchy), drill-through is used to horizontally evaluate further OLAP cubes; sometimes drill-across is equated with drill-through
- Split: The split operator allows a value to be split by multiple dimensions to find more details (for example, a store’s sales for a certain amount of products)
- Merge/Drill-In: In contrast to split, the granularity here is reduced again by removing additional dimensions.
Image credit: blog.infodiagram.com
---
Example Usage of OLAP Cube
OLAP cubes are often used to analyze company data, such as revenue, inventory, and sales. Dimensions that can be important here include, for example, time, store, salesperson, and product. So the cube represents the data (also called facts) turnover, inventory, sales depending on the dimensions period, store, seller, customer and product.
This makes it very easy to answer the following questions:
- How much coffee was sold in the XYZ branch last week?
- How much coffee is there in the warehouse?
- Which seller has sold the most coffee?
- Which store made the most sales last year?
Technical Implementation of OLAP Cube
The data is stored multidimensionally (MOLAP), relationally (ROLAP) or in hybrid configuration (HOLAP). Some systems load the data completely into the main memory during initialization to allow for quick access. As a rule, the cube is “sparse”, i.e. the vast majority of possible intersections in the cube are not assigned values. The way software handles these parts of the cube makes a decisive contribution to the memory requirements and performance of the respective system.
For relational systems, the use of a star schema is typical. It is separated into a fact table and several dimension tables grouped around it.
