Skip to content
Download
USE CASE

Hierarchies and mappings, maintained in Excel

Cost-center rollups and GL mappings are the plumbing behind every P&L line, and they change every reorg. Re-parent a node, remap an account, and commit the whole move at once, in Excel, without leaving a child pointing at a parent that no longer exists.

Download free

Rollups and mappings that stay valid

Change a ParentCostCenterID, remap an AccountID to a new report line, and commit. The structure updates in your database in one transaction, with every foreign key still resolving.

EXCELDATABASE

The structure changes far more often than the schema

DimCostCenter and your GL mapping table almost never change shape, but their contents move constantly. A sales region gets carved into DACH and Benelux. Inside sales is folded under field sales. Finance decides that account 6450 should report under "Cost of sales" instead of "Operating expenses" from the new fiscal year. None of that is a schema change, it is rows, and yet the rows are exactly what break reporting when they go wrong. Point one cost center at a parent that was deleted last quarter and an entire branch quietly drops out of the rollup. Map an account to two report lines and the P&L stops tying out. The damage usually surfaces in the wrong place: a number in a board pack that nobody can reconcile.

Where the structure usually breaks

Most teams maintain these tables the hard way, and the failure modes are predictable. The crosswalk between old and new codes lives in a separate sheet that goes stale the moment someone edits the reorg memo, because every editor keeps a private copy of which parent maps to which. Workbook Connect removes that copy: the lookups, filters, and primary-key mapping are configured once and shared to the whole team automatically on connect, so everyone re-parents against the same live DimCostCenter rather than a snapshot. The other classic break is the load itself. A hand-edited file handed to a DBA either fails on a foreign-key violation halfway through, leaving the rollup half-moved, or worse, succeeds with a dangling reference that nothing flags until a branch quietly drops out of next month's numbers. Here the foreign-key lookup keeps a child from ever pointing at a parent that does not exist, and the validated commit blocks entirely if any row fails, then writes every row in one transaction once they all pass, so the rollup is never left half-moved and a dangling reference cannot slip through unseen.

The tables behind a rollup and a mapping

A cost-center hierarchy is usually one table that references itself: ParentCostCenterID points back at DimCostCenter. A GL mapping is a separate table that ties each account to a report line, so GLMapping carries a foreign key to DimAccount and another to DimReportLine. Workbook Connect edits exactly these tables, and the foreign keys are what keep every move valid.

∞1AccountID∞1ReportLineparentDimCostCenterCostCenterIDPKCodeNameParentCostCenterIDFKDimAccountAccountIDPKNumberNameGLMappingMappingIDPKAccountIDFKReportLineFKEffectiveFromDimReportLineReportLineIDPKCodeName

An illustrative data model, not your exact schema.

Re-parent and remap in a grid that knows the keys

Workbook Connect points Excel straight at DimCostCenter and GLMapping. To move Inside Sales under Field Sales, you change one cell: ParentCostCenterID. Because the column is configured with a foreign-key lookup against DimCostCenter, the cell offers the real cost-center codes and shows their names, so you pick CC-4100 Field Sales DACH rather than guessing at a number. The same lookup on GLMapping.AccountID and GLMapping.ReportLine means a mapping line can only ever reference an account and a report line that exist. Filters let you pull just the EMEA branch, or just the 6000-series mappings, so a controller works on one slice without scrolling a thousand rows. The configuration, lookups, filters, primary-key mapping, and which technical columns to hide, is set up once and shared to the whole team automatically the next time they connect, so everyone opens the same guard-railed sheet.

A cost-center rollup as you re-parent it

Move a node by changing its parent, watch the per-row status track the edit, and see how a parent that does not resolve blocks the whole commit until it is fixed.

CostCenterHierarchy.xlsx - DimCostCenter
StatusCodeCost centerRolls up to (parent)Level
CC-4100Field Sales DACHCC-4000 Sales EMEA3
ChangedCC-4110Inside Sales DACHCC-4100 Field Sales DACH4
CC-4200Channel PartnersCC-4000 Sales EMEA3
Needs fixCC-4300Sales EnablementCC-90003
CC-5000Marketing EMEACC-1000 EMEA2
NewCC-5100Demand GenerationCC-5000 Marketing EMEA3
Commit Validated, then written to dbo.DimCostCenter
  • Row 2: Inside Sales DACH moved from CC-4000 Sales EMEA to CC-4100 Field Sales DACH after the team was folded under field sales. The parent resolves, so the move is allowed.
  • Row 4: parent CC-9000 does not exist in DimCostCenter, so the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together. Re-parent it under a code that resolves (for example CC-4000) and the whole set commits.

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

One validated commit, or nothing at all

A reorg touches many rows together, and they have to land together. Before anything is written, your rules run in the sheet: every ParentCostCenterID has to resolve to a real cost center, no node may be its own parent, and a mapping cannot send one account to two report lines for the same effective date. A per-row status column marks each line as new, changed, or flagged for deletion. Validation problems are not a row status; if a line fails a rule, it is listed with its reason in a "Please fix the following" dialog, like the row pointing at CC-9000 above. Fix the reason and the row commits as the new or changed line it always was. If any row fails, the commit is blocked and nothing is written, so the rollup is never left half-moved. Once every row passes, hit Commit and they are all written to DimCostCenter and GLMapping together in a single transaction, under the permissions finance already has. If a teammate re-parented the same node since you loaded it, optimistic concurrency catches the conflict on commit instead of letting one move silently overwrite the other. Authoring those rules lives in the Validation Designer, a Pro feature, but once they are saved they are enforced for everyone on every connection, including the free plan.

Works with your database

FAQ

Hierarchies and mappings, answered

Filter DimCostCenter to the branch you want, change the ParentCostCenterID on the rows you are moving, and review them by status before you commit. A parent-child model means re-parenting one node carries its descendants automatically, because the children still point at their unchanged parent. If you model the rollup as level columns (Level1, Level2, Level3) instead, you edit those columns on every affected row and commit them as one set. Workbook Connect does not re-derive levels for you, so plan the edit to cover every row the move touches.

Configure a foreign-key lookup on ParentCostCenterID against DimCostCenter, so the cell only offers codes that already exist. If a parent value still fails to resolve at commit (for example a code you typed by hand, or a parent deleted in the same batch), the commit is blocked and nothing is written. The failing row stays in the sheet with its reason, so you fix it and commit again, and then every row is written together in one transaction.

Yes, with a validation rule. In GLMapping you can require that each AccountID resolves to exactly one ReportLine for a given EffectiveFrom, so a 6000-series travel account cannot land on both "Operating expenses" and "Cost of sales" at once. The Validation Designer (a Pro feature) is where you author rules like that; once saved they run for everyone on every commit, including the free plan.

Optimistic concurrency catches it. If someone committed a change to a row after you loaded it, the conflict is detected on your commit instead of silently overwriting their move. You refresh, see their version, and decide which parent is correct rather than discovering the regression in next month report.

No. Workbook Connect edits rows in tables you already have. It makes no server-side or schema changes, adds no triggers, and creates no objects. It reads and writes DimCostCenter, GLMapping and the like over TLS 1.2 or higher under your existing database permissions, nothing more.

No. Cost-center and GL owners work in Excel with the access you already grant them through Entra ID, a service principal, or SQL auth. Every change honours the permissions defined in your database, so a controller who can only see EMEA cost centers only ever edits EMEA cost centers.

Related use cases

Keep your rollups in step with the business

Download Workbook Connect free and connect it to your database. The people who own your cost-center hierarchy and GL mappings can keep them accurate from Excel, with every parent and every account still resolving when the numbers hit reporting.

Download free