Modeling
The grain of a fact table
What one row of a fact table represents. Decide it first.
Choose the finest grain the source offers (an order line, an event): you can aggregate up, never down. Every measure must be true at that grain; a measure at another grain belongs in another fact table.
SELECT COUNT(*) AS row_count,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT order_id || '-' || line_no) AS order_lines
FROM order_lines;
| row_count | orders | order_lines |
|---|---|---|
| 5473 | 2500 | 5473 |