Skip to content
Download
USE CASE

Warehouse dimensions, maintained in Excel

DimProduct, DimCustomer, DimAccount: the tables every report groups and filters by. Workbook Connect lets the people who own those attributes correct them in Excel and commit straight to the warehouse, validated against your foreign keys, with no load job for a one-word fix.

Download free

Keep your dimensions current

Fix a brand spelling, add a new SKU, regroup a category, and commit. The DimProduct your reports join to updates in the warehouse, in one transaction.

EXCELDATA WAREHOUSE

One mistyped attribute splits a total in two

I maintain these tables for a living, so here is the real failure mode. A product lands in DimProduct with Brand spelled "HermanMiller" instead of "Herman Miller", and overnight that one product drops out of the brand total in every report that groups by Brand. The numbers are not wrong, the grouping is, and the first sign anyone gets is a sales lead asking why their brand is down four percent. The fix is trivial: edit one cell. Getting that cell edited is the problem. The dimension lives in the warehouse, the person who spotted it has no write access, and the change waits behind whoever owns the load. By the time it ships, two more SKUs have come in with the same typo.

Why the foreign keys are the whole point

A dimension is only useful because facts join to it. FactSales references DimProduct by ProductID, and DimProduct references DimCategory by CategoryID. Every attribute you edit has to keep those joins intact, which is exactly what the lookups and key checks protect.

∞1ProductID∞1CategoryIDDimCategoryCategoryIDPKCategoryDepartmentDimProductProductIDPKSKUNameBrandCategoryIDFKFactSalesSaleIDPKProductIDFKOrderDateQuantityNetAmount

An illustrative data model, not your exact schema.

Edit DimProduct the way you already read it

Workbook Connect points Excel straight at the dimension table. You browse to DimProduct, open it, and there are the rows in a normal grid: SKU, Name, Brand, Category, list price. Correct the brand, retype a description, bump a price, or add a row for a new SKU, all in the spreadsheet you already live in. Two configuration choices do the heavy lifting and are set once, then shared to the whole team automatically on connect. First, hide the surrogate key and audit columns so a product owner sees attributes, not internals. Second, put a foreign-key lookup on CategoryID so the Category cell only accepts a real member of DimCategory. Filters let you pull just one brand or one department at a time, so you are not scrolling forty thousand SKUs to fix six.

A dimension table as you edit it

Correct attributes, add a member, and watch the per-row status track every change. The one row that would orphan a join blocks the whole commit until it is fixed.

DimProduct.xlsx : DimProduct
StatusSKUNameBrandCategoryList price
ChangedCF-250-BLKAeron Office ChairHerman MillerSeating€ 1,395
CF-118-OAKLinnmon Desk 120cmIKEADesks€ 89
ChangedCF-402-GRYMarkus Task ChairIKEASeating€ 199
NewCF-560-WHTBekant Sit-Stand 160cmIKEAStanding Desks€ 459
Needs fixCF-771-BLKEmbody Gaming ChairHerman MillerGaming€ 1,695
ChangedCF-630-NATHilver Desk BambooIKEADesks€ 129
Commit Validated, then written to dbo.DimProduct
  • Row 1: Brand was corrected from "HermanMiller" to "Herman Miller" so this product groups with the rest of the brand in reports.
  • Row 5: category "Gaming" is not a member of DimCategory, so the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together. Map it to an existing category or add the category first.

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

Nothing reaches the warehouse until it survives validation

The per-row status column is the part I rely on most: every line you touch is marked New, Changed, or flagged for deletion, so before you commit you can see exactly what is about to be written. Workbook Connect validates the whole sheet in Excel first. In the grid above, the brand correction and the new Bekant SKU are ready, but the "Gaming" category cannot resolve to a member of DimCategory, so the commit is blocked and nothing is written. The failing row stays in the sheet with its reason, so you map it to an existing category or add the category first, then commit again. When every row passes, they are all written to DimProduct together in a single transaction, under the permissions you already have, with no server-side schema change anywhere. Authoring those rules is the Validation Designer (a Pro feature), but the rules themselves, and the Configurator and User roles, are enforced for everyone on every connection, including the free plan.

It edits the dimension, it does not pretend to own your SCD

Worth being blunt about, because it is the question I always get. Workbook Connect writes to the dimension table directly. It does not version rows, stamp effective dates, or run type-2 history for you. If your warehouse keeps history through its own load design, your edits become the current values that process consumes. If DimProduct is a flat type-1 dimension, the corrected brand simply replaces the old one on commit. What it does give you is a safe, audited grid for the routine maintenance that otherwise queues behind an engineer: correct an attribute, add a member, regroup a value, validated against your keys, committed live to Fabric Warehouse, Fabric SQL Database, Azure SQL Database, or SQL Server.

Works with your warehouse

FAQ

Warehouse dimensions, answered

Any table you can connect to and have rights to: DimProduct, DimCustomer, DimAccount, DimCategory, and the rest. You browse and open them as ordinary grids in Excel and edit the attribute columns directly. The technical key columns can be hidden per table so contributors only see the fields they own.

No. Workbook Connect edits the dimension table you point it at, in place. It does not version rows, set effective dates, or run any SCD type-2 logic for you. If your warehouse keeps history through its own design or load process, your edits become the current values that process reads. If you maintain a flat type-1 dimension, the edited value simply replaces the old one on commit.

Configure a foreign-key lookup on the column. When an editor fills DimProduct.CategoryID, the lookup offers the real members of DimCategory, so the row can only point at a category that exists. If a free-text value still slips through, your validation rules catch it on commit and block the whole commit with the reason listed, rather than writing an orphan into the warehouse. Nothing is written until every row passes, then they all commit together.

Yes. They sign in with your existing authentication (Entra ID, Service Principal, Windows Integrated, or SQL auth), and every read and write honours the database permissions you already grant. Workbook Connect makes no server-side schema changes, so a product owner correcting a brand name cannot alter a table, a key, or a constraint.

Optimistic concurrency catches it. If someone changes DimProduct row ProductID 4072 after you loaded it, the conflict is detected when you commit and that row is reported back to you instead of silently overwriting their change. You re-read the current value and decide.

Yes. Workbook Connect connects live to Microsoft Fabric Warehouse and Fabric SQL Database, as well as Azure SQL Database and SQL Server. PostgreSQL, Databricks Lakebase, and Databricks SQL Warehouse are coming soon with early access.

Related use cases

Stop queueing one-cell fixes behind a load job

Download Workbook Connect free and connect it to your warehouse. The next time a brand is misspelled or a category needs splitting, the person who owns the attribute fixes it in Excel and commits, validated against your keys, straight to the source your reports read.

Download free