Data model

The tables an analysis uses and the relationships between them, joined on keys, so measures can be sliced by any related field.

DefinitionThe tables an analysis uses and the relationships between them, joined on keys, so measures can be sliced by any related field.

A data model describes which tables hold the facts (sales lines, invoices) and which hold the descriptions (customers, products, dates), and how they connect through key columns. In Excel, the Data Model is the store behind Power Pivot: tables loaded once, related by keys, and summed with measures instead of lookups copied down every row.

How it is computed

Load each table, give every dimension table a unique key, relate the fact table's foreign key to it (one-to-many), and define measures such as Revenue = SUM of the amount column. Filters on a dimension then flow to the facts.

Example

A sales table of 250,000 rows relates to a customers table of 3,000 rows on customer ID. If 40 customer IDs appear twice in the customers table, the key is no longer unique and the relationship cannot be one-to-many until the duplicates are fixed.

Where it goes wrong

Duplicate keys, sales rows whose customer ID has no match, and a missing date table. The full guide is Power Pivot in Excel.

Articles that use this term

How-to guides

How to make a pivot table in Excel from sales data

A step-by-step guide to building a pivot table from an invoice-level sales ledger: preparing the data as an Excel Table, sales by region and month, each region's share of the total, slicers by product line, and the check that the pivot ties to the ledger. Every figure comes from a ten-line sample ledger you can rebuild.

Sep 30, 202618 min read How-to guides · Finance and FP&A teams

Power Query in Excel: clean a ledger export once, refresh it every month

A step-by-step guide to Power Query in Excel using a messy sales ledger export: removing title and blank rows, trimming IDs, turning CR suffixes into negative amounts, reading day-first dates with the right locale, merging a customer master and grouping by customer. It ends with the check that the cleaned total equals the export's own footer, and the monthly refresh.

Sep 30, 202618 min read AI and self-service analytics

Asking questions of a sales workbook without rebuilding it in a BI tool

How a team whose data lives in Excel or Google Sheets can ask plain-English questions of the workbook they already keep: reading the structure as it is, mapping columns once, validating the totals, and getting answers with the rows behind them, without pivot tables or a data warehouse.

Sep 16, 20263 min read Data quality and reconciliation · Shipping and logistics

TEU and chargeable weight: keeping ocean and air apart in one roll-up

How a forwarder builds one customer view across ocean and air without ever adding a container to a kilogram: mode as a dimension, units per mode, reconciliation per mode to invoiced volume, revenue as the only figure that sums across modes, and the reporting mistakes that follow when the two are blended.

Sep 16, 20262 min read How-to guides

The sales roll-up hierarchy: region, team, person, account, and why product is not a level

The four-level tree that every sales measure is summed through, the difference between a level and a dimension, the identity that must hold at each level, and the mapping decisions that make one hierarchy serve finance, sales operations and the salesperson at once.

Sep 16, 20263 min read Alternatives and comparisons

Adaptive Planning pricing: what the published price covers, and what else a first year includes

Adaptive Planning pricing, from its published sources, and the other costs to budget in year one: implementation, modeling, training and consumption.

Sep 24, 20262 min read