Anyone who has recently entered the world of Business Intelligence knows that, after overcoming the data extraction (ETL) phase, you reach the real battlefield: dimensional modeling. We've started building multidimensional OLAP cubes using SQL Server Analysis Services (SSAS) on SQL Server 2008 R2, and the discussions on how to structure the databases are turning epic.

The debate boils down to the eternal fight between two design philosophies: the Star Schema, proposed by Ralph Kimball, against the Snowflake Schema.

The theory behind the debate

In a Data Warehouse, we have the "Fact Table" (metrics or transactions, like sales) surrounded by "Dimensions" (the context: time, customers, products).

In the Snowflake Schema, dimensions are normalized. If we have a "Product" dimension, it links to a "Subcategory" table, which in turn links to "Category". This is what we were taught in university to avoid data redundancy.

In the Star Schema, we break the purist rules: we denormalize dimensions. The entire hierarchy of Category, Subcategory, and Product is flattened into a single, massive Dim_Product table.

At first, my transactional database developer brain resisted the star schema. "We're repeating the category name a thousand times, it's a waste of space!" I would yell at my colleague. But then I put it into practice.

The technical mud: Performance vs Storage

The relational engine powering the SSAS cube (Data Source View) suffers terribly when it has to resolve multiple massive JOINs. Let's look at the difference in SQL code when processing or querying the cube.

Typical query in Snowflake:

SELECT 
    c.NombreCategoria, 
    SUM(f.ImporteVenta) AS TotalVentas
FROM Fact_Ventas f
JOIN Dim_Producto p ON f.IdProducto = p.IdProducto
JOIN Dim_SubCategoria sc ON p.IdSubCategoria = sc.IdSubCategoria
JOIN Dim_Categoria c ON sc.IdCategoria = c.IdCategoria
GROUP BY c.NombreCategoria;

Equivalent query in Star Schema:

SELECT 
    p.NombreCategoria, 
    SUM(f.ImporteVenta) AS TotalVentas
FROM Fact_Ventas f
JOIN Dim_Producto p ON f.IdProducto = p.IdProducto
GROUP BY p.NombreCategoria;

The difference at the database engine level is abysmal. SSAS digests star schemas much faster during cube processing because it saves jumping through three different tables. And most importantly: when designing the cube in Visual Studio (BIDS), mapping hierarchies from a single table is a walk in the park, while with the snowflake you expose yourself to granularity issues and complex relationships.

Reflection: Normalization is overrated in BI

I had to admit defeat in this debate. Hard drives are ridiculously cheap. Worrying about saving 50 Megabytes by denormalizing text strings in a dimension table is absurd when the real bottleneck is CPU performance when processing JOINs.

The Star Schema has become my de facto standard. However, SSAS is a heavy disk-based monster (MOLAP). I've been reading about the new "In-Memory" technologies that Microsoft is cooking up (like the VertiPaq engine or BISM). If in the future we can load the entire analytical model directly into RAM, I wonder if this debate between star and snowflake will cease to make sense. Maybe the brute force of memory will beat model design, but for now, Kimball rules.