After defining our Star schema in the Data Warehouse, we run into the wall of reality. The management department has a new dashboard connected directly to the SQL database. When some random guy tried to cross "Total Sales" by "Country" and "Year" for the last five financial years (we're talking almost 100 million ticket records), the "Loading..." icon just kept spinning for 45 seconds.

"The data has to be instantaneous", he told me. And he was right. The solution isn't about optimizing indexes in the relational engine; we've reached the physical limit of what a hard drive can read on demand. The solution is to surrender to OLAP Cubes.

The concept: Pre-calculating the universe

Imagine a Rubik's cube. On the X axis you have Time (Year, Month, Day), on the Y axis Geography (Country, City) and on the Z axis Product (Category, Brand). At the intersection of those axes, there's a number: the sales amount.

The genius of MOLAP (Multidimensional Online Analytical Processing) systems like SQL Server Analysis Services is that they don't calculate the sum when you ask for it. They calculate it at dawn.

During the nightly maintenance window, we run a process that sweeps the entire relational fact table. The Analysis Services engine starts summing all possible combinations: it sums sales by year, by month, by country, by brand, and the combinations of all of them. It generates highly compressed binary files with these pre-calculated results.

Multidimensional Syntax: MDX

When the dashboard fires the query in the morning, it doesn't use SQL. It uses MDX (Multidimensional Expressions). It doesn't look into individual records, it simply goes to the exact "intersection" of the cube where the final number is already saved.

-- A typical MDX query to extract 2011 sales in Europe
SELECT 
    { [Measures].[Importe Total Ventas] } ON COLUMNS,
    { [Geografia].[Region].&[Europa] } ON ROWS
FROM [Cubo_Ventas_Corporativo]
WHERE ( [Tiempo].[Año].&[2011] )

The MDX syntax might seem like alien gibberish for those of us coming from SQL. Here there are no FROM tables, but the entire cube. We don't join keys, we navigate through "Hierarchies" and "Members".

The result is that, whether asking for the total of a specific day or the accumulated sum of the company's entire history, the response takes milliseconds. The number was already calculated waiting to be read.

Reflection: The price of latency

This instantaneous speed magic comes with a very dark cost: data latency. Because aggregations take hours to pre-calculate (the famous "cube processing"), the data directors see is always, at best, delayed by a day (data at yesterday's close).

For pure financial environments this is enough, but marketing teams increasingly demand to see the impact of their campaigns "in real time". We find ourselves at a technical crossroads. Either you accept slow load times hitting the relational engine directly (ROLAP), or you accept yesterday's data with a cube (MOLAP).