Model like a pro: the star schema

The tempting alternative: join everything into one wide table and chart from that. It works for a week, then hurts four ways:

• Repeats: a customer's city is copied onto every order line they ever bought. Fix a typo in one place, it survives in a thousand others.
• No reuse: next month you build a subscriptions report. The flat table was built for orders - you start from zero. Dimensions built once get reused by every fact you add later.
• Measure errors: flat tables mix grains. Put monthly_qty from subscriptions next to order lines and SUM happily double counts. Stars force one grain per fact.
• Memory: BI engines compress repeated dimension values brilliantly when they live in their own narrow tables - a star is smaller and faster than the flat version of itself.

The grain is the answer to: what does one row of the fact table represent? For fact_sales the sentence is: "one row = one order line". Write that sentence before you write any SQL, and put it in the table's documentation forever.

• Misdeclared grain = double counting. If someone believes the grain is "one row = one order" and sums a column that repeats per line, every multi-item order counts twice or more. The single most common wrong-number bug in BI.
• One grain per fact. Orders at line grain and subscriptions at month grain do not belong in the same fact table. They become two facts sharing the same dimensions - still one star family, two centers.
• The grain picks your measures. At line grain, revenue is SUM(quantity * unit_price) and it is safe. Coarser grains lose detail you can never recover.

Dates hide inside every table, so why build a separate dim_date? Because a common date table gives you three things raw date columns never will:

• Continuous days: one row per calendar day, even days with zero orders. Without it, a month with no sales silently vanishes from your trend line instead of showing as zero.
• Ready-made attributes: year, month, quarter, weekday, is_weekend - computed once, reused by every chart, always spelled the same way.
• Time intelligence: year-to-date, same-month-last-year, rolling 3 months - the b4 measures - all require a proper date table underneath. No date table, no time intelligence.

SQLite has no calendar generator built in, but here is the seed of one - the distinct months Daybreak has traded. A real dim_date extends this to one row per day:

SELECT DISTINCT strftime('%Y-%m', order_date) AS month
FROM orders
ORDER BY 1;

Daybreak has order dates (orders.order_date) and signup dates (customers.signup_date). Do you build two date tables? No - you build one dim_date and relate it twice, once per role. The same physical table "plays the role" of order date in one relationship and signup date in another. In Power BI the second relationship is created inactive and activated per measure; in Tableau you simply relate the field you need. One calendar, many roles - that is the whole trick, and it is a named PL-300 topic.

Run it, then read the output columns against the star diagram above - they match one for one.

CREATE VIEW fact_sales AS
SELECT oi.order_id,
       oi.product_id,
       o.customer_id,
       o.order_date,
       o.status,
       o.channel,
       oi.quantity,
       oi.unit_price,
       oi.quantity * oi.unit_price AS line_revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id;

SELECT * FROM fact_sales LIMIT 8;

customers is a dimension too. Join it to fact_sales and slice revenue by plan - the skeleton is ready, run it, then try swapping plan for city or country.

CREATE VIEW fact_sales AS
SELECT oi.order_id, oi.product_id, o.customer_id, o.order_date,
       o.status, o.channel, oi.quantity, oi.unit_price,
       oi.quantity * oi.unit_price AS line_revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id;

SELECT c.plan,
       ROUND(SUM(f.line_revenue), 2) AS revenue
FROM fact_sales f
JOIN customers c ON c.customer_id = f.customer_id
GROUP BY 1
ORDER BY 2 DESC;

The mini-BI has been quietly running this same star logic since session b1. Build revenue by category below, press Show SQL, and read the joins - order_items to orders to products. That is your star, just written inline. A production semantic model does exactly this on your behalf, thousands of times a day.