BI basics, episode 2 of 6
Rows, columns and tables
How data is stored, and why a tidy table beats a pretty one.
All the data you will ever analyse arrives in the same shape: a table. Understand what a good table looks like and half the difficulty of BI disappears.
What a table is
A table is a grid with two simple rules.
- Each row is one thing. One sale, one customer, one product.
- Each column is one fact about that thing. Its date, its price, its name.
Here are a few sales from our cafés.
| Date | Café | Product | Quantity | Amount |
|---|---|---|---|---|
| 2 March | Harbour | Flat white | 2 | 9.00 |
| 2 March | Harbour | Croissant | 1 | 3.50 |
| 2 March | Old Town | Flat white | 1 | 4.50 |
| 3 March | Old Town | Iced latte | 3 | 15.00 |
Read any row and you can say a full sentence: “On 2 March, Harbour sold two flat whites for 9.00.” If every row makes a clear sentence, the table is in good shape.
Tidy rules
Spreadsheets built for people to look at often break these rules. Tables built for analysis follow them.
- One row per event. Not one row per day with a column for each product.
- One column per fact. Not “March”, “April”, “May” as three columns. Use one column called Month.
- One value per cell. Not “Flat white, Croissant” squeezed into one cell.
- No totals inside the data. A “Total” row will be added up with everything else and double your numbers.
- No blank rows, merged cells or headings halfway down. They help the eye and confuse every tool.
A table that follows these rules looks plain. That is the point. A plain table can be turned into any report; a pretty one can only ever be itself.
Two kinds of table
Look at the business and you will find two kinds.
Tables of things that happen. Sales, payments, bookings, deliveries. They grow every day and each row has a date and usually an amount.
Tables of things that exist. Products, cafés, customers, staff. They change slowly and each row describes one item.
The sales table above mentions “Flat white” many times. Somewhere there is also a product table that lists flat white once, with its category and its price.
| Product | Category | Price |
|---|---|---|
| Flat white | Hot drinks | 4.50 |
| Iced latte | Cold drinks | 5.00 |
| Croissant | Bakery | 3.50 |
How tables connect
The two tables share a column: Product. That shared column is called a key. It lets you bring the category from the product table onto every sale, so you can ask for sales by category without typing “Hot drinks” ten thousand times.
In real systems the key is usually a code, such as P014, because names change and codes do not. The idea is the same.
Try it
Open a spreadsheet you use at work. Ask three questions.
- What is one row? Can you say it as a sentence?
- Does every column hold one kind of fact?
- Are there totals, blank rows or merged cells mixed in?
If the answers are untidy, you have found the first job of any BI project.
Next: the two words that turn tables into questions, measures and dimensions.