learning-it by example

#257 Data modelling 6.5 min

Declare the Grain: The Mistake That Breaks Reports

A daily sales report said 108 pounds when the shop sold 54, and one rule fixed it.

1. In a made-up SQLite shop, what did a daily sales report add up to when order lines and order totals were loaded into one fact table, and what had the shop really sold?
2. In a made-up SQLite shop, when an order's shipping charge was copied onto every one of its order lines, what did the shipping report total, against the true shipping in the source?
3. In dimensional modelling, what one rule fixes the mistake of mixing grains in one fact table?
4. In the fixed fact_order_line table (primary key on order id and product), which constraint made SQLite refuse an order total row with no product?
5. In a made-up SQLite shop, when shipping was copied onto every order line, why did a check of the sales amounts alone still pass although the shipping report was wrong?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Predict the four runs

    Copy the code folder first. Read source.py, mixed.py and fixed.py, then write down the report totals you expect from python run.py mixed, pushed and fixed. Run them and compare. You should see 108 against 54, then 54 and 20 against 54 and 15, then 54 and 15 matching.

    A hint

    The mixed table holds both the order lines and a total row per order.

  2. Raise a shipping charge

    In source.py, change the shipping of O4 from 5 to 10. Predict the shipping report totals of the pushed and fixed runs, then run python run.py pushed and python run.py fixed. You should see 25 against 20 for the pushed table and 20 against 20 for the fixed tables.

    A hint

    O4 has one line, so the pushed table copies its shipping only once.

  3. Add a line to an order

    In source.py, add the line ("O2", "Pen", 1, 2) straight after the O2 Pad line. Predict the numbers, then run python run.py mixed. You should see 12 rows for 8 order lines, and a report total of 112 against a source total of 56.

    A hint

    The mixed table doubles every pound, so the report total is twice the source total.

  4. Loosen the guard

    In fixed.py, delete the words NOT NULL after product TEXT in the fact_order_line table. Predict what happens to the order total row with no product, then run python run.py guard. You should see the repeat line still refused, but the row with no product accepted.

    A hint

    The primary key on its own does not stop an empty product in an ordinary SQLite table.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Four made-up orders in SQLite; it shows two grain mistakes and one fix, not allocation or dimension tables.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos