Why Model: Facts, Dimensions and Grain

The Problem with the Flat Table

Many exports look like our ventes_plat.csv file: one big table where each sales row repeats the store name, its region, the product name, its category… It works for a small file, but quickly causes problems:

• redundancy: “Auvergne-Rhône-Alpes” is repeated on every Lyon sale;
• inconsistencies: if one row writes “Lyon” and another “LYON”, you get two stores;
• difficulty adding other facts: how do you link monthly targets by store to a table of detailed sales?
• more complex calculations in DAX, and poorer performance on large volumes.

The solution, coming from dimensional modeling (popularized by Ralph Kimball), is the star schema.

Facts and Dimensions

A fact table records events that you measure: a sale, an order, a call, an accounting entry. It contains:

• keys to the dimensions (CodeMagasin, CodeProduit, Date);
• numeric values to aggregate (Quantite, PrixUnitaire, Montant);
• many rows, and it grows over time.

A dimension table describes the context: who, what, where, when. It contains:

• a unique key (one row per store, per product, per day);
• descriptive attributes for filtering and grouping (Region, Categorie, Mois);
• relatively few rows.

In our file: FaitVentes (NumVente, Date, DateLivraison, CodeMagasin, CodeProduit, Quantite, PrixUnitaire), DimMagasin (CodeMagasin, Magasin, Region), DimProduit (CodeProduit, Produit, Categorie) and a date table.

The Grain

The grain answers the question: “what does one row of the fact table represent?”. Here: one row = one product sold in a store during a sale. Targets, on the other hand, are defined per store and per month: that is another grain, so another fact table.

Always declare the grain before building a model: mixing two grains in the same table leads to wrong totals.

The Star

            DimProduit
                |
DimMagasin — FaitVentes — Dates

The dimensions surround the fact table and are linked directly to it by one-to-many relationships (one dimension row, several fact rows). Filters “flow down” from the dimensions to the facts: choosing the region Hauts-de-France filters the Lille sales.

If you linked DimProduit to a separate DimCategorie table, you would get a snowflake. Power BI supports it, but it’s generally preferable to flatten the category into DimProduit: fewer relationships, simpler filters, a more readable model.

With a good model, measures stay simple:

Total ventes = SUM ( FaitVentes[Montant] )

and work whatever the chosen axis of analysis (store, product, month).