Dimensional modeling (star schema)

The star schema has a fact table in the center and dimension tables around it.

• Fact: one business event per row, at the defined grain (here, one order item), with numeric measures (quantity, amount) and keys to the dimensions.
• Dimension: describes the context (customer, product, date), with attributes to filter and group by.

The first decision is the grain: what exactly one row of the fact table is. Mixing grains in the same fact table (item and whole order) is what makes numbers double.

A small star, running here

The fact table has item grain. The question "how much did each category sell, by customer segment" becomes two JOINs and a GROUP BY.

CREATE VIEW fact_items AS
SELECT i.order_id, o.order_date, o.customer_id, i.product_id, i.qty, i.qty * p.price AS revenue
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN products p ON p.id = i.product_id
WHERE o.status = 'delivered';

SELECT p.category, c.segment, SUM(f.revenue) AS revenue
FROM fact_items f
JOIN products p ON p.id = f.product_id
JOIN customers c ON c.id = f.customer_id
GROUP BY p.category, c.segment
ORDER BY revenue DESC;

category    | segment   | revenue
----------- | --------- | -------
displays    | wholesale | 1800
displays    | retail    | 900
peripherals | retail    | 380
peripherals | wholesale | 300
accessories | retail    | 60

Order 109, from a customer that does not exist in the dimension, disappeared from the result in the JOIN with customers. In a real model, the dimension gets an "unknown" row (key -1) so the fact table does not lose sales.

Star or snowflake

In a *snowflake*, the dimension is normalized into several tables (product → category → department). It saves repetition, but costs JOINs in every query. For BI (Power BI included), the recommendation is the star: wide, denormalized dimensions.

• A surrogate key, an integer generated in the warehouse, isolates the fact table from key changes in the source and allows dimension history (see Slowly changing dimensions).
• A dedicated date dimension (one row per day, with month, quarter, holiday) is almost always the first one to be built.