Skip to content
Download
USE CASE

Master data management, done in Excel

Let the people who own the customer and product master maintain it in the grid they already live in. Workbook Connect points Excel at your real tables, validates every edit against your foreign keys and rules, and commits the clean rows straight to the database.

Download free

One customer master, edited where stewards work

Open DimCustomer in Excel, correct a country code, add a new account, flag a duplicate for deletion, and commit. The spreadsheet and the database hold exactly the same records, always.

EXCELDATABASE

Why the customer master drifts

The person who knows that Northwind Trading moved offices, changed its VAT number, or merged with another account is a sales operations steward, not a DBA. But the only sanctioned way to fix DimCustomer is to raise a ticket, attach a spreadsheet, and wait for someone to write an UPDATE. So the real maintenance happens in shadow spreadsheets that never make it back, country codes get stored three different ways (NL, NLD, and "Netherlands"), the same customer shows up twice under two IDs, and downstream reports quietly add up to the wrong number. The data is wrong not because nobody cares, but because the people who care cannot touch it.

The tables behind a customer master

A master record is rarely a single flat table. DimCustomer carries foreign keys of its own, and other tables key back to it. Workbook Connect edits these tables directly, and those foreign keys are what keep every edit honest.

∞1CustomerID∞1ProductIDDimCustomerCustomerIDPKNameCountryCodeSalesRepIDFKSalesOrderOrderIDPKCustomerIDFKProductIDFKOrderDateQuantityDimProductProductIDPKSKUNameCategoryIDFK

An illustrative data model, not your exact schema.

Maintain master data without leaving Excel

A steward opens the workbook and sees DimCustomer as a grid: the live rows, already filtered to the segment they own and with the technical columns (audit timestamps, surrogate keys) hidden. They fix the spelling of a name, raise a credit limit, add a row for a brand-new account, or flag a duplicate for deletion, and a per-row status column marks each one as new, changed, or flagged. When they fill in CountryCode or SalesRepID, a foreign-key lookup offers the real values from the lookup tables, so a customer can only point at a country and a rep that actually exist. The lookups, filters, primary-key mapping, and column visibility are configured once by a Configurator and shared to the whole team automatically on connect, so every steward opens an identical, guard-railed sheet.

The customer master as you edit it

Edit records in the grid, watch the per-row status track every change, and let validation block the whole commit until every row fits, so nothing reaches the table until it all passes.

CustomerMaster.xlsx, DimCustomer
StatusCustomerIDNameCountrySales repCredit limit
ChangedC-10042Northwind Trading BVNLA. Jansen€ 50,000
C-10043Adatum GmbHDEM. Weber€ 25,000
NewC-10044Contoso Foods LtdGBS. Okafor€ 40,000
ChangedC-10045Litware Nordic ABSEP. Lind€ 0
Needs fixC-10046FRC. Bernard€ 15,000
Needs fixC-10047Tailspin LogisticsXQA. Jansen€ 30,000
Commit Validated, then written to dbo.DimCustomer
  • Row 1: CountryCode corrected from "NLD" to the ISO-3166 alpha-2 value "NL", which now resolves against DimCountry.
  • Row 5: Name is blank. The "Name is required" rule fails, so the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together.
  • Row 6: CountryCode "XQ" has no match in DimCountry, so the foreign-key lookup fails and the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together.

An illustrative example of the editing workflow, not a product screenshot.

Validated first, then committed in one transaction

Nothing reaches DimCustomer until the whole sheet passes. Your rules run in Excel before commit: Name is required, CountryCode must resolve against DimCountry, a credit limit cannot be negative. If any row fails, the commit is blocked and nothing is written. The failing rows stay in the sheet with their reasons, so a blank name or an invented country code never makes it into the table; you fix them and commit again, and then every row is written to DimCustomer together in a single transaction, under the database permissions the steward already has. If another steward changed the same customer since this workbook loaded, optimistic concurrency catches it on commit rather than letting one edit quietly bury the other.

The database stays the system of record

There is no second copy of the master to keep in sync and no portal to stand up and maintain. Excel is the window, your database is the truth. Edits go back over TLS 1.2 or higher, through the authentication you already use (Entra ID with MFA, a service principal, or Windows Integrated), and against the tables exactly as they are. Workbook Connect makes no schema changes, so the constraints, keys, and permissions your DBA defined stay in force on every commit, whether you connect to Azure SQL Database, SQL Server, a Fabric SQL Database, or a Microsoft Fabric Warehouse.

Works with your database

FAQ

Master data management, answered

The reference tables the rest of your data hangs off: DimCustomer, DimProduct, DimSupplier, DimCostCenter, and the lookup tables (country, category, sales rep) their foreign keys point at. If it is a table you can connect to, you can maintain it in the grid.

There is no export file and no import job. Workbook Connect reads the rows from DimCustomer live and writes your edits back to that same table on commit, in one transaction. The rows in the sheet are the rows in the database, so there is nothing to reconcile and no staging table to clean up afterward.

Configure a foreign-key lookup on the column once. When a steward fills in CountryCode or CategoryID, they pick from the live values in the lookup table, and a typed value that has no match (for example country code "XQ") is caught before commit and blocks the whole commit until you fix it. The data integrity lives in the database, not in a fragile data-validation list copied into the workbook.

Yes. Each works in their own workbook, and optimistic concurrency compares each row against the database at commit time. If someone else changed the same customer since you loaded it, the conflict is flagged on commit instead of one edit silently overwriting the other.

Yes. Workbook Connect honours the permissions already defined on your database, so a steward with read-only rights on DimProduct can browse but not commit. On top of that, Configurator and User roles are enforced on every connection: Configurators set up tables, lookups, and rules, Users work within them. Authoring rules in the Validation Designer and Manage Access are Pro features.

No. Workbook Connect reads and writes rows in your existing tables and makes no server-side schema changes. It will not add columns, alter keys, or create triggers. Your DBA keeps full control of the model.

Related use cases

Put the customer and product master back in the hands that own it

Download Workbook Connect free, point it at your database, and let your stewards maintain DimCustomer and DimProduct themselves, accurately, in Excel, with the database staying the single source of truth.

Download free