Creating Relationships and Validating with PivotTables

With four tables loaded into the data model, they are still independent — Power Pivot does not automatically know how they connect. Without defined relationships, combining fields from two different tables in a pivot table produces incorrect results.

The One Side and the Many Side

Every relationship in Power Pivot connects exactly two tables — one acting as the one side and the other as the many side.

Excel displays this in the diagram view using 1 on the dimension table side and * (asterisk) on the fact table side. The arrow between them shows the direction the relationship flows — always from dimension to fact.

Cardinality Types

The Manage Relationships dialog also shows cardinality — the numerical description of how many rows on each side can match:

• 1* Many to one — the standard pattern in this model. Many rows in Sales match one row in Customers, Products, or Dates;
• 11 One to one — each row on one side matches exactly one row on the other. Rare in transactional models;
• ** Many to many — multiple rows on both sides can match. Requires careful handling and is generally avoided in simple models.

Task

Create relationships in the Data Model.

Step 1 — Open Diagram View

Go to the Power Pivot tab in the Ribbon and click Manage.

Switch to Diagram View using the second icon in the bottom-right corner of the Power Pivot window.

You should see four unconnected boxes, one per table. This is your starting point.

Step 2 — Create the three relationships

Create each relationship by clicking and dragging from the key column in the dimension table to the matching column in the Sales table.

Customers → Sales: drag CustomerID from the Customers box to CustomerID in the Sales box.

Products → Sales: drag ProductID from the Products box to ProductID in the Sales box.

Dates → Sales: drag Date from the Dates box to OrderDate in the Sales box.

After each drag, check the ends of the line that appears. The dimension table end should show a 1 and the Sales end should show a *. If they are reversed, delete the line and try again. Ensure the 1 shows up at the Dimension Table side. If this is not the case, reverse the order from which column you are dragging, or use the ''Manage Relationships'' function (see point 3 below).

Step 3 — Verify using Manage Relationships

In the Power Pivot window, go to Design tab → Manage Relationships.

Confirm that all three relationships appear in the list, each showing the correct table and column on both sides.

Close the dialog and close the Power Pivot window.

Step 4 — Validate with a PivotTable

In Excel, go to Insert → PivotTable.

Select Use this workbook's Data Model and click OK.

In the PivotTable Fields pane, drag Region (from Customers) to the Rows area and Total (from Sales) to the Values area.

The PivotTable should show a different sales total for each region, not the same number repeated. This confirms the Customers → Sales relationship is working.

Now also drag Category (from Products) into the Rows area. The table should break down sales by both Region and Category simultaneously, pulling from three different tables at once.