Star Schema Practice: Orders, Lines and Correct Totals

The fictional source

Order	Line	Customer	Product	USD
10	1	C1	P1	$60
10	2	C1	P2	$40
11	1	C2	P1	$50

The candidate line key is (order_id, line_number). The product dimension should contain P1 and P2 once each. The customer dimension should contain C1 and C2 once each. Date attributes belong in a date dimension when the reporting model needs consistent calendar filtering.

Model the relationships

Use the dimension’s unique key on the one side and the corresponding sales key on the many side. Check uniqueness rather than assuming it from the table name. If the product dimension contains two rows for P1, a direct join can duplicate both P1 sales lines. Historical product versions need a deliberate versioning design, not accidental duplicate keys.

WITH sales(order_id, line_number, product_id, amount_usd) AS (
  VALUES (10, 1, 'P1', 60),
         (10, 2, 'P2', 40),
         (11, 1, 'P1', 50)
)
SELECT SUM(amount_usd) AS sales_usd,
       COUNT(*) AS order_lines,
       COUNT(DISTINCT order_id) AS orders,
       SUM(amount_usd) * 1.0 / COUNT(DISTINCT order_id) AS aov_usd
FROM sales;

Expected: sales_usd 150, order_lines 3, orders 2, aov_usd 75. This SQL exercise checks the model’s arithmetic independently of a visualisation tool. The same business definitions should guide your BI measures.

Test a filter, not just the grand total

Filtering to product P1 leaves sales of $110 across two orders, with an average of $55 of P1 sales per qualifying order. That is not the average full value of those orders. Decide which question the user intends and label the measure accordingly. Product-specific sales divided by distinct orders has a valid meaning, but it should not silently become a whole-basket metric.

Three design traps

Joining monthly targets directly onto every order line repeats target amounts. Keep facts at their own grain and relate through suitable shared dimensions.
Summing daily distinct-customer counts over a month double-counts customers seen on several days.
Replacing missing dimension keys with an arbitrary real customer misattributes activity. Use an explicit unknown policy and monitor unmatched records.

Practice extension

Add a $10 refund on one line and decide whether to store it as a separate event or an adjustment. Write expected gross and net sales before changing the model. Then explain which date controls each measure and how a viewer can trace the result to the underlying line.

Documentation and further reading

Microsoft relationship guidance