learning-it by example

#259 Data modelling 7 min

Conformed Dimensions and the Bus Matrix in SQL

Joining two fact tables made 25 pounds of refunds look like 50, and drilling across fixed it.

1. What does drilling across mean when you compare separate fact tables such as sales, returns and tickets?
2. In a SQLite demo with a sales fact table and a returns fact table, when sales were joined straight to returns on the customer, what refund total did the report show, against the true total?
3. In Kimball dimensional modelling, when do dimensions conform?
4. In a SQLite drill across of sales, returns and tickets by region, what happened when the returns team used its own customer list with the labels N and S?
5. How does the SQLite demo build its bus matrix, with business processes as rows and dimensions as columns?

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

  1. Predict the matrix and the drill

    Copy the code folder first. Read the DDL in bus.py and source.py, then write down the bus matrix and the region drill across you expect. Run python run.py matrix and python run.py drill and compare. You should see North 60, 20 and 2, and South 65, 5 and 1.

    A hint

    Tickets have no product, so they are missing from the category report.

  2. Add a support ticket

    In source.py, add the ticket ("2026-04-10", "C2") to TICKETS. Predict which row of the region report changes, then run python run.py drill. You should see South with 2 tickets and every other number unchanged.

    A hint

    Customer C2 is Ben, who is in the South.

  3. Fix the labels

    In source.py, change the returns team's labels in RETURNS_TEAM_REGIONS from N and S to North and South. Predict how many rows the report has, then run python run.py unconformed. You should see two rows, North and South, with sales, returns and tickets lined up.

    A hint

    Conforming means the same labels with the same meaning.

  4. Add a return and join the facts again

    In source.py, add a third return to RETURNS: ("2026-04-10", "C3", "P2", "Late", 15). Predict the true refund total, then run python run.py drill and python run.py wrong. You should see North returns 35 and Kitchen returns 35 in the drill, and in the wrong join a refund total of 65 against a true refund total of 40.

    A hint

    The true refunds are now 20, 5 and 15. The join repeats each return once for every sale by that customer.

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

What this does not show

Three made-up processes in SQLite; it shows drilling across and two failures, not shrunken rollup dimensions or speed.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos