Chapter 25 | Relational Data Analysis

Excel Data Model and Relationships

Connect sales, customers, products, employees and calendar tables into one reliable analytical model—without repeating master information inside every transaction row.

Start Learning Build the Model

OrganizeSeparate transaction facts from descriptive business dimensions.

RelateConnect tables through clean, compatible and validated key columns.

ModelUse a star-schema structure for clearer and faster reporting.

AnalyzeCreate PivotTables using fields from several related tables.

Choose Language

Topic 25 of 40

Learning Objectives

After This Chapter, You Will Be Able To

Design, connect, validate and use a professional Excel Data Model for multi-table analysis.

Classify Tables

Identify fact, dimension, lookup, bridge and calendar tables according to their business role.

Validate Keys

Check primary and foreign keys for uniqueness, completeness, compatibility and matching coverage.

Create Relationships

Build correct one-to-many relationships and avoid ambiguous or many-to-many structures.

Report Across Tables

Create multi-table PivotTables that combine descriptive fields with transaction measures.

Data Model Foundations

Move Beyond One Large Flat Worksheet

A Data Model stores several related tables inside one workbook and allows Excel to analyze them together without repeating every master-data field in the transaction table.

1 What Is the Excel Data Model?

The Excel Data Model is an internal relational structure that can contain multiple tables, relationships and analytical calculations. It becomes especially useful when one flat worksheet would be too wide, repetitive, difficult to maintain or unable to represent the real business structure clearly.

Definition: A Data Model is a collection of related tables that Excel can analyze as one logical reporting system.

Why a Model Is Better Than Repeating Everything

Flat-File Habit	Data-Model Approach	Business Benefit
Customer name and city repeated on every invoice line	Customer details stored once in a Customer table	Less duplication and easier correction
Product category typed repeatedly	Product details linked through Product ID	Consistent classification
Monthly worksheets joined manually	Transactions loaded into one fact table	Scalable reporting
Complex lookup formulas across many columns	Relationships connect tables logically	Cleaner reporting structure

Professional Tip: Use Power Query to prepare tables and the Data Model to define how those tables relate. Cleaning and modelling are connected but different responsibilities.

Practical Experiment 1: Split a Flat Sales File

Take a sales sheet containing Invoice, Date, Customer, City, Product, Category, Quantity and Amount.

Step 1: Identify Repetition

Mark fields repeated for the same customer and product.

Step 2: Separate

Create Sales, Customers and Products tables with suitable key columns.

Step 3: Compare

Explain how one correction in a master table replaces hundreds of repeated corrections.

Learning Output: A normalized three-table business structure.

2 Fact Tables and Dimension Tables

A strong model begins by assigning a clear role to every table. The most common structure uses one central fact table connected to several dimension tables.

FactSales

InvoiceLineIDPK
DateKeyFK
CustomerIDFK
ProductIDFK
QuantityMeasure
NetAmountMeasure

Many Transactions

Fact tables record business events. Their rows are normally numerous and contain measurable values plus foreign keys.

DimCustomer

CustomerIDPK
CustomerNameAttribute
CityAttribute
SegmentAttribute

DimProduct

ProductIDPK
ProductNameAttribute
CategoryAttribute

Fact Table

Stores transactions, events or periodic snapshots.
Contains measures such as quantity, sales, cost and hours.
Usually has many rows.
Contains foreign keys to dimensions.

Dimension Table

Stores descriptive business information.
Contains one unique row per customer, product, employee or date.
Provides fields used for grouping and filtering.
Should have a unique primary key.

Practical Experiment 2: Classify Business Tables

Review tables named Sales, Customers, Products, Employees, Calendar and SalesTargets.

Step 1: Classify

Mark each table as fact, dimension or supporting table.

Step 2: Explain

Write the grain: what one row represents in every table.

Step 3: Improve

Identify fields that are stored in the wrong table.

Learning Output: A documented table-role and grain matrix.

Keys and Relationships

Connect Tables Through Reliable Business Keys

A relationship works only when the connecting columns represent the same business entity and the lookup side contains one unique value for every key.

3 Primary Keys, Foreign Keys and Data Types

A primary key uniquely identifies each row in a dimension table. A foreign key appears in the fact table and points to the related dimension record.

Primary Key

One unique, nonblank value per dimension row—for example, CustomerID in DimCustomer.

Foreign Key

A repeating value in the fact table—for example, CustomerID in FactSales.

Compatibility

Both columns should use compatible data types and the same coding logic.

Key Validation Checklist

Unique Lookup KeyNo duplicate IDs on the one side

No BlanksRequired key values are complete

Same Data TypeText-to-text or number-to-number

Same Format LogicNo hidden spaces or inconsistent codes

Match CoverageForeign keys exist in the dimension

Important: A customer name is usually a poor relationship key because names may repeat, change or contain spelling differences. Prefer a stable CustomerID.

Practical Experiment 3: Audit Relationship Keys

Use CustomerID in a customer master and sales table.

Step 1: Test Uniqueness

Check duplicate CustomerIDs in the master table.

Step 2: Test Coverage

Identify sales CustomerIDs missing from the master.

Step 3: Test Type

Confirm both columns are stored consistently as text or number.

Learning Output: A relationship-readiness audit report.

4 Cardinality and One-to-Many Relationships

Cardinality describes how many rows on one side can relate to rows on the other side. Most professional Excel models should be designed around one-to-many relationships.

1 → *

One-to-Many

One product connects to many transaction rows. This is the standard model relationship.

1 → 1

One-to-One

Each key appears once in both tables. Consider whether the tables should be combined.

* ↔ *

Many-to-Many

Keys repeat on both sides. Redesign using a bridge or distinct lookup table.

0 / ?

Unmatched

A foreign key has no valid dimension record, producing blank or unknown reporting groups.

Why Duplicate Keys Cause Trouble

If ProductID appears twice in the Product table, Excel cannot safely treat that table as the unique lookup side. Duplicate keys may prevent relationship creation or produce uncertain business interpretation.

Model Rule: Put the unique key on the “one” side and the repeating transaction key on the “many” side.

Practical Experiment 4: Repair a Many-to-Many Problem

Use a Sales table and a ProductCategory table where Category appears several times in both tables.

Step 1: Diagnose

Explain why Category is not unique on either side.

Step 2: Create Lookup

Build a distinct Category table with one row per CategoryID.

Step 3: Relate

Connect both business tables through the new distinct lookup.

Learning Output: A controlled one-to-many model replacing a many-to-many design.

5 Create and Manage Relationships in Excel

Tables can be added to the Data Model while importing through Power Query, while creating a PivotTable, or through Power Pivot. Relationships can then be created in Diagram View or through Excel’s relationship-management commands.

Prepare Tables

Use clean headers, keys and data types.

Add to Model

Load each required table to the Data Model.

Create Links

Connect foreign keys to unique dimension keys.

Test Report

Use fields from several tables in one PivotTable.

Relationship Management Practices

Use business-friendly table and field names.
Create relationships at the correct grain.
Avoid connecting dimensions directly to dimensions unless the business design genuinely requires it.
Document every relationship: from table, from column, to table, to column and expected cardinality.
When multiple date columns exist, choose the main active date relationship for reporting; other date roles may require advanced DAX treatment later.

Practical Experiment 5: Create a Three-Table Model

Load Sales, Customers and Products to the Data Model.

Step 1: Relate

Connect Sales[CustomerID] to Customers[CustomerID].

Step 2: Relate Again

Connect Sales[ProductID] to Products[ProductID].

Step 3: Report

Create a PivotTable showing Sales Amount by Customer City and Product Category.

Learning Output: A working multi-table PivotTable.

Model Design

Build a Clear Star Schema

A star schema places the transaction fact table at the centre and connects it directly to descriptive dimensions. It is easier to understand, filter and maintain than a chain of unclear relationships.

6 Star Schema, Grain and Table Design

DimDateDate, month, quarter, year

DimCustomerCustomer, city, segment

FactSalesQuantity, sales, cost

DimProductProduct, category, brand

DimEmployeeSalesperson, team, region

Define the Grain Before Creating Relationships

The grain states what one fact-table row represents. For example: “one row per invoice line,” “one row per employee per day,” or “one row per product per warehouse per month.” Mixing several grains in one fact table creates confusing totals and duplicated values.

Table	Recommended Grain	Typical Key	Typical Measures / Attributes
FactSales	One invoice line	InvoiceLineID	Quantity, Gross Sales, Discount, Net Sales
DimCustomer	One customer	CustomerID	Name, City, Segment
DimProduct	One product	ProductID	Name, Category, Brand
DimDate	One calendar date	DateKey	Month, Quarter, Year, Financial Period

AICPE Quality Learning Commitment: A strong model should be understandable to a learner, an analyst and a manager. Use clear names, documented grain and visible validation rules.

Practical Experiment 6: Draw a Star Schema

Plan a retail model using Sales, Customers, Products, Stores and Calendar.

Step 1: Centre

Place the transaction table in the middle and write its grain.

Step 2: Surround

Place one unique dimension table for every reporting subject.

Step 3: Label

Write each primary key, foreign key and relationship direction.

Learning Output: A documented retail star-schema blueprint.

7 Date Tables, Bridge Tables and Special Structures

A dedicated Date table gives consistent month, quarter, year, weekday and financial-period analysis. Bridge tables help resolve business structures where a simple one-to-many relationship is not available.

Date Dimension

Contains one row per date and supports consistent calendar and financial reporting across facts.

Bridge Table

Contains distinct connecting combinations, such as Employee–Project or Product–Tag membership.

Role-Playing Dates

Order Date, Ship Date and Payment Date may all connect to one Date dimension, but advanced measures determine which date role is used.

When a Bridge Table Is Needed

Suppose one employee can work on several projects and one project can contain several employees. Connecting Employees directly to Projects creates many-to-many behaviour. An EmployeeProject bridge with one row per valid assignment creates two controlled one-to-many relationships.

Caution: A bridge table should represent a real business association. Do not create arbitrary distinct lists merely to silence a relationship error without understanding the reporting logic.

Practical Experiment 7: Build a Calendar and Bridge Design

Create a Date table for one year and an EmployeeProject assignment table.

Step 1: Calendar

Add Date, Month, Month Number, Quarter, Year and financial-period fields.

Step 2: Bridge

Create unique EmployeeID–ProjectID assignment rows.

Step 3: Explain

Describe how both structures improve filtering and reporting accuracy.

Learning Output: A reusable Date dimension and a valid bridge-table plan.

Model Validation

Prove That the Model Produces Correct Results

A relationship diagram may look correct and still produce inaccurate totals. Professional users test keys, row counts, totals, blanks and filter behaviour.

8 Relationship Testing and Reconciliation

Row-Count TestConfirm fact-table rows have not unexpectedly increased or disappeared.

Total TestCompare Data Model totals with controlled source totals—for example, $125,000 in net sales.

Blank-Member TestInvestigate blank customer, product or date groups caused by unmatched foreign keys.

Filter TestConfirm a dimension filter changes the expected measures and only those measures.

Period TestConfirm monthly totals add back to the annual result.

Refresh TestAdd new data, refresh and confirm relationships and totals remain valid.

Professional Model Audit Sequence

Confirm every table’s grain and expected row count.
Test dimension keys for uniqueness and blanks.
Test fact foreign keys for unmatched values.
Reconcile basic measures before adding complex calculations.
Filter by one dimension at a time and observe results.
Check blank members and unexpected categories.
Refresh with new records and repeat critical controls.

Practical Experiment 8: Perform a Complete Model Audit

Use the Sales–Customer–Product–Date model created earlier.

Step 1: Reconcile

Compare fact-table row count and total sales with the source.

Step 2: Filter

Test city, category and month filters independently.

Step 3: Investigate

Document every blank or unmatched model member and propose correction.

Learning Output: A signed-off model validation checklist.

Interactive Model Lab

Select the Right Data-Model Design

Choose a business structure and receive a recommended table and relationship approach.

Transaction Pattern Reporting Need Current Problem

Recommendation: Select the business pattern, reporting need and current problem, then click the button.

Real-Time Practical Assignment

Build a Retail Sales Data Model

Create a complete multi-table analytical model that can answer management questions without lookup-heavy flat sheets.

Project Brief

A retail business has separate files for Sales, Customers, Products, Stores and Calendar. Management wants one PivotTable that can analyze net sales by city, product category, store type, salesperson and month.

Prepare Tables

Clean headers, assign data types, remove duplicate dimension keys and document grain.

Add to Data Model

Load only required analytical tables and keep unnecessary staging outputs connection-only.

Create Relationships

Connect customer, product, store and date dimensions to the Sales fact table.

Build Report

Create a multi-table PivotTable with filters and percentage-of-total analysis.

Validate Results

Reconcile row count, total sales, blank members and period totals.

Document Model

Provide a schema diagram, key dictionary, relationship register and refresh checklist.

Required Outputs

FactSales table with one row per invoice line.
DimCustomer, DimProduct, DimStore and DimDate tables.
Relationship diagram showing all one-to-many links.
PivotTable: Net Sales by Month and Product Category, filterable by City and Store Type.
Exception list for unmatched CustomerID, ProductID, StoreID or DateKey.
One-page model quality and reconciliation report.

Success Standard: The model must refresh, preserve source totals, avoid duplicate dimension keys and allow fields from several tables to work together correctly.

Practice Worksheet

Complete These Data-Model Tasks

Save screenshots, relationship notes and validation results as part of your learning portfolio.

Design Tasks

Write the grain of five sample business tables.
Classify each table as fact, dimension or bridge.
Identify primary and foreign keys.
Draw a star schema for a service business.
Create a Date table field list.

Validation Tasks

Find duplicate keys on the lookup side.
Find unmatched foreign keys in the fact table.
Compare Data Model sales with source sales.
Test filters from three dimensions.
Document one refresh and reconciliation cycle.

Portfolio Output: Keep the workbook, schema image, relationship register, exception report and reconciliation sheet. These demonstrate practical Excel modelling ability.

Common Mistakes

Mistakes Data-Model Builders Should Avoid

A model is valuable only when relationships reflect the real business and produce trustworthy results.

Risky Habits

Using names instead of stable IDs as relationship keys.
Allowing duplicate values on the lookup side.
Connecting tables with different data types.
Mixing invoice-level and invoice-line-level records in one fact table.
Creating many-to-many links without a clear bridge design.
Using dimensions to store transaction measures.
Ignoring blank members in PivotTables.
Trusting the diagram without reconciling totals.

Professional Practices

Use stable primary and foreign keys.
Document one row’s meaning for every table.
Build one-to-many relationships wherever possible.
Use a star schema with clear fact and dimension roles.
Create bridge tables only for valid business memberships.
Use a dedicated Date dimension for time analysis.
Investigate unmatched keys and blank groups.
Reconcile counts and totals after every structural change.

Remember: A technically accepted relationship may still be logically wrong. Always validate the business meaning, table grain and expected filter behaviour.

Quick Revision

Remember These Data Model Principles

Review these concepts before learning Power Pivot and DAX measures.

Define the Grain

State exactly what one row represents before building relationships or calculations.

Separate Facts and Dimensions

Keep measurable events in facts and descriptive attributes in dimensions.

Use Stable Keys

Connect unique primary keys to compatible foreign keys.

Prefer One-to-Many

Use bridge or distinct lookup tables to resolve valid many-to-many structures.

Build a Star Schema

Connect dimensions directly to the central fact table for clear reporting.

Reconcile the Model

Test totals, blanks, row counts, filter behaviour and refresh results.

Protected Learning Content