Data Model Relationships and the Star Schema in Excel

Why relationships?

In a normal workbook, to show sales by product category you would add a Category column to the sales sheet with VLOOKUP or XLOOKUP. With a million rows, that is slow and makes the file huge.

In the Data Model, you keep Sales and Products as separate tables and connect them with a relationship on Product ID. The PivotTable can then use Category from Products and Amount from Sales together.

Fact tables and dimension tables

The star schema

Put the fact table in the middle and connect each dimension table to it. Drawn out, it looks like a star. This is the layout Power Pivot and Power BI work best with.

            Products
               |
   Date ——— Sales ——— Stores
               |
           Customers

Each relationship is one-to-many: one row in Products (one product) matches many rows in Sales (every time it was sold). Filters flow from the “one” side to the “many” side: pick a category, and the sales are filtered.

Create a relationship in Power Pivot

1. Load each table into the Data Model (from Power Query, or Power Pivot › Add to Data Model).
2. In the Power Pivot window, click Home › Diagram View.
3. Drag ProductID from Products onto ProductID in Sales. A line appears between the tables.
4. Repeat for the other dimension tables.

You can also use Design › Create Relationship and pick the tables and columns from lists.

Add a date table

A separate date table lets you group by month, quarter and financial year, and it is needed for time calculations such as year-to-date.

1. In the Power Pivot window, go to Design › Date Table › New.
2. Power Pivot creates a Calendar table and relates it to your dates. If it doesn’t, drag your date column onto the Calendar’s Date column.
3. Add a financial year column if you report April to March, then mark it with Design › Mark as Date Table if it isn’t marked already.

For the financial year formula, see Get the financial year (April–March).

Rules that avoid most problems

• The key column on the “one” side must be unique: no product listed twice in Products.
• Both key columns should have the same data type: text with text, number with number.
• Use fields from dimension tables in Rows, Columns and slicers, and numbers from the fact table in Values.
• Don’t connect two fact tables directly. Connect both to the shared dimension tables.
• Hide ID columns from the PivotTable field list (right-click › Hide from Client Tools) so users only see readable fields.

FAQ