16  Computing OLAP Data Cubes

A Technical and Reproducible Approach

Data cubes organize multidimensional information for summarized analysis, interactive exploration, and decision-making. In a typical data warehouse architecture, data is extracted from transactional systems, cleaned and integrated, stored in an analytical repository, and presented via reports, data mining models, or OLAP cubes (Santizo Galicia 2026).

The objective of this chapter is to formalize this pipeline into a technical, reproducible framework. We present the fundamentals of Online Analytical Processing (OLAP), the mathematical formulation of data cubes, core navigation operations, and a practical implementation in Python. This discussion draws upon data warehousing literature (Inmon 2005; Kimball and Ross 2013), classic OLAP research (Chaudhuri and Dayal 1997; Gray et al. 1996), and modern data analysis libraries such as pandas (McKinney 2010; The pandas development team 2024).

An OLAP cube is not merely a geometric three-dimensional block. Rather, it is a mathematical abstraction representing aggregated measures across dimensions. For instance, an enterprise might analyze sales across time, region, and product. Each unique combination of dimensions identifies a specific cell within the cube, housing aggregated measures such as total revenue, units sold, average cost, or profit margin.

16.1 From Data Warehouses to OLAP Analytics

Transactional Online Transaction Processing (OLTP) systems are optimized for individual operational transactions: purchases, payments, inventory updates, customer registrations, or account transfers. Conversely, Online Analytical Processing (OLAP) systems are optimized for querying aggregated data to answer strategic business questions. This architectural distinction requires distinct data structures from those used in operational databases.

Definition. An OLAP system is an analytical framework that organizes quantitative measures across categorical or hierarchical dimensions to facilitate multi-perspective aggregated queries. \[ C : D_1 \times D_2 \times \cdots \times D_k \longrightarrow \mathbb{R}^m \]

In this formulation, \(D_1,\ldots,D_k\) represent analytical dimensions (e.g., time, region, product, or store branch). The value \(C(d_1,\ldots,d_k)\) contains one or more aggregated measures. For example, if \(D_1\) is time, \(D_2\) is region, and \(D_3\) is product, a single cell represents computer sales in the Central region during Quarter Q1.

Multidimensional architectures comprise three core components:

1. Dimensions: Axes of analysis (e.g., time, region, product).
2. Hierarchies: Levels of detail within a dimension (e.g., day \(\rightarrow\) month \(\rightarrow\) quarter \(\rightarrow\) year).
3. Measures: Aggregable quantitative variables (e.g., sales, units, profit, budget).

A key advantage of OLAP is tailoring granularity to user roles. Executive leadership might query total sales by country; regional management examines sales by state; and store supervisors analyze sales per location. The underlying analytical repository supports these perspectives via cube navigation operations.

OLAP Cube — Interactive Visualizer
Dimensions: Region × Time × Product  ·  Measure: Sales (100K MXN)

Low sales High sales ■ Cell outside active subcube

16.2 Multidimensional Model

Consider a transaction set \(\mathcal{T}\) where each record contains dimension attributes and a revenue measure. A transaction is represented as:

\[ t_i = (r_i, \tau_i, p_i, v_i), \]

where \(r_i\) denotes region, \(\tau_i\) time period, \(p_i\) product, and \(v_i\) revenue. Sum-aggregated cube values are obtained by grouping transactions sharing identical dimensional coordinates.

Definition. Given a set of transactions \(\mathcal{T}\), the cell value of a sales cube is defined as the sum of sales values across all transactions matching a specific dimension tuple: \[ C(r,\tau,p)=\sum_{i:\; r_i=r,\; \tau_i=\tau,\; p_i=p} v_i \]

This formulation demonstrates that a data cube is an aggregation function. Production cubes often incorporate dozens of dimensions, such as sales channel, customer segment, store location, marketing campaign, and tax status.

16.2.1 Dimensions, Measures, and Granularity

Granularity defines the atomic level of detail at which data is stored or queried. A table recording daily sales per store and SKU possesses higher granularity than a table recording monthly sales per region and product category. Selecting appropriate granularity is a core design decision balancing storage footprint, query performance, and analytical flexibility (Kimball and Ross 2013).

Definition. The granularity of a data cube is the atomic level of detail defining its dimensions: \[ G(C)=\{\ell_1,\ell_2,\ldots,\ell_k\} \]

Here, \(\ell_j\) represents the level used in dimension \(D_j\). For example, \(G(C)=\{\text{month},\text{state},\text{product}\}\) indicates queries operate at month, state, and product resolution. Transitioning from month to week increases granularity; transitioning from state to country decreases granularity.

16.3 Core Operations in OLAP Data Cubes

OLAP operations facilitate navigation across hierarchical levels and subcubes (Santizo Galicia 2026). Below, we formalize the five primary operations: drill-down, roll-up, slice, dice, and pivot.

16.3.1 Drill-down

Drill-down navigates from aggregated summaries to detailed atomic data. For example, if annual sales totaled 1,000 units in 2025, drill-down operations break down sales by quarter, month, state, or SKU.

Definition. A drill-down operation replaces an aggregated level \(\ell\) of a dimension with a more detailed level \(\ell'\) lower in the same hierarchy: \[ \ell' \prec \ell \]

For example, within the temporal hierarchy:

\[ \text{year} \rightarrow \text{quarter} \rightarrow \text{month} \rightarrow \text{week} \rightarrow \text{day}, \]

moving from year to quarter or month to week constitutes a drill-down operation (Santizo Galicia 2026).

16.3.2 Roll-up

Roll-up is the inverse operation, aggregating granular data into higher-level summaries. Daily sales aggregate into monthly, quarterly, or annual totals; store-level sales roll up into state or national figures.

Definition. A roll-up operation replaces a detailed level \(\ell'\) with a higher-level aggregated level \(\ell\), combining cells via an aggregation function \(A\): \[ C_{\ell}(d)=A\left(\{C_{\ell'}(d'):\; d' \mapsto d\}\right) \]

Function \(A\) may be sum, average, maximum, count, or a derived metric. Revenue typically uses summation; pricing metrics utilize weighted averages; inventory metrics apply end-of-period balances.

16.3.3 Slice

A slice operation fixes a single dimension to a specific coordinate value, producing a subcube with one fewer dimension. If the original cube contains Region, Time, and Product dimensions, fixing Product = Tablets yields a 2D Region \(\times\) Time slice.

Definition. Given a cube \(C:D_1\times\cdots\times D_k\rightarrow\mathbb{R}\), a slice operation binds dimension \(D_j\) to a constant value \(a\in D_j\): \[ C_{D_j=a}(d_1,\ldots,d_{j-1},d_{j+1},\ldots,d_k)=C(d_1,\ldots,a,\ldots,d_k) \]

16.3.4 Dice

A dice operation extracts a subcube by selecting subsets across two or more dimensions simultaneously. Unlike a slice (which fixes a single dimension to one scalar value), a dice operation filters across multiple values per dimension.

Definition. A dice operation restricts the data cube to subsets \(S_j\subseteq D_j\) across one or more dimensions: \[ C_{S_1,\ldots,S_k}=C\big|_{S_1\times\cdots\times S_k} \]

For example, selecting Time \(\in \{\text{January},\text{February}\}\) and Product \(\in \{\text{Smartphones},\text{Tablets}\}\) creates a 2×2 subcube slice (Santizo Galicia 2026).

16.3.5 Pivot or Rotate

A pivot operation rotates the visual orientation of the cube without modifying underlying values. Pivoting re-orients row and column headers to highlight alternative analytical comparisons (e.g., swapping product rows and monthly columns).

Definition. A pivot operation applies a permutation \(\pi\) to the dimension ordering of a data cube: \[ C^{\pi}(d_{\pi(1)},\ldots,d_{\pi(k)})=C(d_1,\ldots,d_k) \]

16.4 Manual Computation of a Data Cube

To solidify these concepts, we walk through a manual calculation on a small sample dataset.

Consider an enterprise tracking quarterly sales across Location, Time, and Department. Departments are:

\[ (W,P,D,E)=(\text{Wood},\text{Plumbing},\text{Decoration},\text{Electrical}). \]

Dimensions are defined as:

• \(D_1=\text{Location}=\{\text{CDMX},\text{Guadalajara},\text{Monterrey},\text{Merida}\}\).
• \(D_2=\text{Time}=\{Q1,Q2\}\).
• \(D_3=\text{Department}=\{W,P,D,E\}\).

The sales cube maps as:

\[ C:\text{Location}\times \text{Time}\times \text{Department}\longrightarrow \mathbb{R}. \]

Raw transactional totals:

Time	CDMX \((W,P,D,E)\)	Guadalajara \((W,P,D,E)\)
Q1	\((70,40,20,30)\)	\((60,35,25,40)\)
Q2	\((50,60,22,28)\)	\((45,50,30,35)\)

Time	Monterrey \((W,P,D,E)\)	Merida \((W,P,D,E)\)
Q1	\((48,38,18,26)\)	\((52,42,15,37)\)
Q2	\((36,55,25,30)\)	\((44,33,20,28)\)

16.4.1 Step 1: Map Cube Cells

Each value maps directly to a coordinate tuple. For instance:

\[ C(\text{CDMX},Q1,\text{Wood})=70, \]

\[ C(\text{Merida},Q2,\text{Plumbing})=33. \]

The full cube comprises \(4 \text{ locations} \times 2 \text{ quarters} \times 4 \text{ departments} = 32\) elementary cells.

16.4.2 Step 2: Compute Location Totals

Location totals sum across all time periods and departments:

\[ \text{Total}(u)=\sum_{t\in\{Q1,Q2\}}\sum_{d\in\{W,P,D,E\}} C(u,t,d). \]

• CDMX: \((70+40+20+30) + (50+60+22+28) = 160 + 160 = 320\).
• Guadalajara: \((60+35+25+40) + (45+50+30+35) = 160 + 160 = 320\).
• Monterrey: \((48+38+18+26) + (36+55+25+30) = 130 + 146 = 276\).
• Merida: \((52+42+15+37) + (44+33+20+28) = 146 + 125 = 271\).

Location	Total
CDMX	320
Guadalajara	320
Monterrey	276
Merida	271
Grand Total	1187

16.4.3 Step 3: Compute Time Totals

• Q1: \(160 + 160 + 130 + 146 = 596\).
• Q2: \(160 + 160 + 146 + 125 = 591\).

Time	Total
Q1	596
Q2	591
Grand Total	1187

16.4.4 Step 4: Compute Department Totals

• Wood: \(70+50+60+45+48+36+52+44 = 405\).
• Plumbing: \(40+60+35+50+38+55+42+33 = 353\).
• Decoration: \(20+22+25+30+18+25+15+20 = 175\).
• Electrical: \(30+28+40+35+26+30+37+28 = 254\).

Department	Total
Wood	405
Plumbing	353
Decoration	175
Electrical	254
Grand Total	1187

16.4.5 Step 5: Manual Slice Operation

Applying filter constraints: \(\text{location} \in \{\text{CDMX},\text{Merida}\}\), \(\text{time} = Q1\), and \(\text{department} \in \{\text{Wood},\text{Electrical}\}\):

Location	Time	Wood	Electrical	Total
CDMX	Q1	70	30	100
Merida	Q1	52	37	89
Total		122	67	189

16.4.6 Step 6: Manual Roll-Up Operation

Rolling up individual store locations into regional zones:

Location	Region
Monterrey	North
CDMX	Central
Guadalajara	West
Merida	Southeast

Region	Time	Wood	Plumbing	Decoration	Electrical	Total
Central	Q1	70	40	20	30	160
Central	Q2	50	60	22	28	160
North	Q1	48	38	18	26	130
North	Q2	36	55	25	30	146
West	Q1	60	35	25	40	160
West	Q2	45	50	30	35	160
Southeast	Q1	52	42	15	37	146
Southeast	Q2	44	33	20	28	125
Total		405	353	175	254	1187

---

16.5 Reproducible Python Implementation

Below, we build the OLAP cube using Python and pandas (Santizo Galicia 2026).

import pandas as pd

records = [
    # North
    ("North", "Q1", "Televisions", 35), ("North", "Q1", "Computers", 22),
    ("North", "Q1", "Washing Machines", 10), ("North", "Q1", "Cameras", 14),
    ("North", "Q2", "Televisions", 28), ("North", "Q2", "Computers", 26),
    ("North", "Q2", "Washing Machines", 13), ("North", "Q2", "Cameras", 17),
    ("North", "Q3", "Televisions", 18), ("North", "Q3", "Computers", 15),
    ("North", "Q3", "Washing Machines", 8), ("North", "Q3", "Cameras", 12),
    ("North", "Q4", "Televisions", 30), ("North", "Q4", "Computers", 33),
    ("North", "Q4", "Washing Machines", 16), ("North", "Q4", "Cameras", 15),

    # Central
    ("Central", "Q1", "Televisions", 40), ("Central", "Q1", "Computers", 30),
    ("Central", "Q1", "Washing Machines", 18), ("Central", "Q1", "Cameras", 11),
    ("Central", "Q2", "Televisions", 32), ("Central", "Q2", "Computers", 28),
    ("Central", "Q2", "Washing Machines", 20), ("Central", "Q2", "Cameras", 14),
    ("Central", "Q3", "Televisions", 25), ("Central", "Q3", "Computers", 19),
    ("Central", "Q3", "Washing Machines", 15), ("Central", "Q3", "Cameras", 10),
    ("Central", "Q4", "Televisions", 38), ("Central", "Q4", "Computers", 35),
    ("Central", "Q4", "Washing Machines", 22), ("Central", "Q4", "Cameras", 16),

    # West
    ("West", "Q1", "Televisions", 20), ("West", "Q1", "Computers", 27),
    ("West", "Q1", "Washing Machines", 9), ("West", "Q1", "Cameras", 6),
    ("West", "Q2", "Televisions", 24), ("West", "Q2", "Computers", 31),
    ("West", "Q2", "Washing Machines", 11), ("West", "Q2", "Cameras", 8),
    ("West", "Q3", "Televisions", 29), ("West", "Q3", "Computers", 36),
    ("West", "Q3", "Washing Machines", 7), ("West", "Q3", "Cameras", 9),
    ("West", "Q4", "Televisions", 19), ("West", "Q4", "Computers", 34),
    ("West", "Q4", "Washing Machines", 10), ("West", "Q4", "Cameras", 5),

    # Southeast
    ("Southeast", "Q1", "Televisions", 26), ("Southeast", "Q1", "Computers", 17),
    ("Southeast", "Q1", "Washing Machines", 12), ("Southeast", "Q1", "Cameras", 9),
    ("Southeast", "Q2", "Televisions", 21), ("Southeast", "Q2", "Computers", 20),
    ("Southeast", "Q2", "Washing Machines", 16), ("Southeast", "Q2", "Cameras", 11),
    ("Southeast", "Q3", "Televisions", 23), ("Southeast", "Q3", "Computers", 18),
    ("Southeast", "Q3", "Washing Machines", 14), ("Southeast", "Q3", "Cameras", 10),
    ("Southeast", "Q4", "Televisions", 27), ("Southeast", "Q4", "Computers", 24),
    ("Southeast", "Q4", "Washing Machines", 19), ("Southeast", "Q4", "Cameras", 12),
]

df = pd.DataFrame(records, columns=["region", "time", "product", "sales"])
df.head()

	region	time	product	sales
0	North	Q1	Televisions	35
1	North	Q1	Computers	22
2	North	Q1	Washing Machines	10
3	North	Q1	Cameras	14
4	North	Q2	Televisions	28

16.5.1 MultiIndex Representation

cube = df.set_index(["region", "time", "product"]).sort_index()
cube.head(10)

pivot_view = df.pivot_table(
    index=["region", "time"],
    columns="product",
    values="sales",
    aggfunc="sum",
    fill_value=0,
)
pivot_view

16.6 Executing OLAP Operations in Python

16.6.1 Slice: Fixing Quarter Q2

slice_q2 = df[df["time"] == "Q2"]
slice_q2.pivot_table(
    index="region",
    columns="product",
    values="sales",
    aggfunc="sum",
    fill_value=0,
)

16.6.2 Dice: Subcube Filtering

dice = df[
    df["region"].isin(["North", "Central"])
    & df["time"].isin(["Q1", "Q4"])
    & df["producto" if "producto" in df else "product"].isin(["Televisions", "Cameras"])
]

dice.pivot_table(
    index=["region", "time"],
    columns="product",
    values="sales",
    aggfunc="sum",
    fill_value=0,
)

16.6.3 Roll-Up: Geographic Zone Aggregation

zone_map = {
    "North": "North-Central Zone",
    "Central": "North-Central Zone",
    "West": "West-Southeast Zone",
    "Southeast": "West-Southeast Zone",
}

df_zones = df.assign(zone=df["region"].map(zone_map))

rollup_zones = df_zones.pivot_table(
    index=["zone", "time"],
    columns="product",
    values="sales",
    aggfunc="sum",
    fill_value=0,
)

rollup_zones

16.6.4 Pivot: Rotating Orientation

pivot_product_time = df.pivot_table(
    index="product",
    columns="time",
    values="sales",
    aggfunc="sum",
    fill_value=0,
)

pivot_product_time

16.7 Aggregations and Derived Measures

Measures within an OLAP cube are categorized as additive, semi-additive, or non-additive (Kimball and Ross 2013).

Definition. An additive measure can be validly aggregated via summation across all cube dimensions: \[ M(A\cup B)=M(A)+M(B),\qquad A\cap B=\varnothing \]

For non-additive measures (such as conversion rates or profit margins), percentages cannot be summed directly; they must be recomputed dynamically from raw numerators and denominators.

Calculating revenue share per product:

product_sales = (
    df.groupby("product", as_index=False)["sales"]
    .sum()
    .assign(market_share=lambda x: x["sales"] / x["sales"].sum())
)

product_sales

Calculating quarter-over-quarter growth per product line:

time_sales = (
    df.groupby(["product", "time"], as_index=False)["sales"]
    .sum()
    .sort_values(["product", "time"])
)

time_sales["growth"] = time_sales.groupby("product")["sales"].pct_change()
time_sales

16.8 Reusable Function for OLAP Views

def build_olap_view(df, row_dims, col_dims, measure="sales", agg_func="sum"):
    """Constructs a tabular OLAP view using pandas.

    Parameters
    ----------
    df : pandas.DataFrame
        Long-format transactional dataset.
    row_dims : str or list[str]
        Dimensions mapped to row headers.
    col_dims : str or list[str]
        Dimensions mapped to column headers.
    measure : str
        Target numeric metric column.
    agg_func : str or callable
        Aggregation function ("sum", "mean", "count").
    """
    return df.pivot_table(
        index=row_dims,
        columns=col_dims,
        values=measure,
        aggfunc=agg_func,
        fill_value=0,
    )

build_olap_view(df, row_dims=["region", "time"], col_dims="product")

16.9 Conclusions

OLAP data cubes provide the conceptual foundation for multidimensional analytics. Their core strength resides not just in visual 3D representations, but in the mathematical model linking dimensions, hierarchies, measures, and navigation operations. Mastered together, drill-down, roll-up, slice, dice, and pivot allow analysts to seamlessly navigate between executive summaries and granular operational data.

16.10 References

Chaudhuri, Surajit, and Umeshwar Dayal. 1997. “An Overview of Data Warehousing and OLAP Technology.” ACM SIGMOD Record 26 (1): 65–74. https://doi.org/10.1145/248603.248616.

Gray, Jim, Adam Bosworth, Andrew Layman, and Hamid Pirahesh. 1996. “Data Cube: A Relational Aggregation Operator Generalizing Group-by, Cross-Tab, and Sub-Totals.” Proceedings of the Twelfth International Conference on Data Engineering, 152–59. https://doi.org/10.1109/ICDE.1996.492099.

Inmon, William H. 2005. Building the Data Warehouse. 4th ed. Wiley.

Kimball, Ralph, and Margy Ross. 2013. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. 3rd ed. Wiley.

McKinney, Wes. 2010. “Data Structures for Statistical Computing in Python.” Proceedings of the 9th Python in Science Conference, 56–61. https://doi.org/10.25080/Majora-92bf1922-00a.

Santizo Galicia, Jessica. 2026. Computación de Cubos de Datos. Presentación de clase, Facultad de Ciencias, Universidad Nacional Autónoma de México.

The pandas development team. 2024. Pandas Documentation. Pandas. https://pandas.pydata.org/docs/.