All writingAnalytics and BI · 7 min read

Reporting through a reorganisation without rewriting last year

Sales territories get redrawn and last year's numbers move. Both answers are correct, and the model has to say out loud which one it is giving you.

dbtsemantic layerSnowflakePostgresPower BIAnalytics and BIData engineeringSaaSManufacturingFintech

The territory redraw went live on the first of the month. Two weeks later the sales VP opens the quarterly comparison and North has lost a slice of last year's revenue it certainly booked at the time. Nobody deleted anything. One large account moved from North to Central in the new structure, and the dashboard, quite reasonably, is now showing every deal that account has ever signed under Central.

Two analysts get asked to explain it, and both come back correct. One says the figure is right, because Central carries that account and its target today, so the year on year comparison is like for like against the team being held to it. The other says the figure is wrong, because North's people did the work and hit their number, and the record of last year should say so. This is not an argument about SQL. It is an argument about which question the report is answering, and the model has already answered it on everyone's behalf without saying so.

Both answers need to exist. That is the whole of it. The damage is not caused by choosing the current structure or the historical one, it is caused by choosing silently, so that the same chart means one thing in January and a different thing in April while the title stays the same. Once a finance lead has been surprised twice by a number that moved without an explanation, they stop opening the dashboard and go back to the spreadsheet they control. Trust in reporting rarely dies from a wrong number. It dies from a number that changed and nobody could say why.

The facts did not change, the dimension did

No transaction was edited. The order lines, the invoices, the closed opportunities are all exactly as they were. What changed is an attribute hanging off a dimension: which territory this account belongs to, which cost centre this employee sits in, which product family this SKU rolls up to. In most warehouses that attribute lives in one row per account, and when the source system is refreshed, the new value overwrites the old one. The history of the attribute is not deleted so much as never recorded in the first place. Every historical report then inherits today's structure, retroactively, for all time.

As-was and as-is are different questions

It helps to stop calling one of them correct. They are two questions that reasonable people in the same business ask on the same day, for different purposes, and a reporting layer that only supports one of them is not neutral, it is opinionated in a way nobody signed off.

  • As-was How did the business perform under the structure that was actually in place. This is the one commissions, quota attainment, historical commentary and any audited or externally published figure need. Restating it is not a modelling improvement, it is changing the record of what happened.
  • As-is How would the structure we have today have performed over the same period. This is the one planning, target setting and any comparison of a manager against their own predecessor need. Without it, every reorg resets the baseline to zero and nobody can tell whether the new Central region is doing well or badly.
  • As-of a chosen date The same question asked against a fixed point that is neither then nor now, most often the structure in place when the budget was set. Less common, but it is the one that ends the argument in a mid-year planning review, and a type 2 dimension gives it to you free.
A fact row for a closed deal in Q2 last year, with two arrows leaving it: one to the dimension version valid on the close date showing territory North, one to the current dimension version showing territory Central. The dimension is drawn as three stacked rows sharing one account ID, each with its own valid-from and valid-to window.
One fact, two legitimate joins. The date on the fact decides which dimension version is as-was, and the current-flag decides which is as-is.

Model both, name both, and never let a filter decide

The technical move is small and well understood. The discipline is in the naming. If both views exist but they are reached by a hidden join choice or an unlabelled filter, you have made the problem worse, because now two dashboards disagree and both look official. The rule we hold to is that the choice must be visible in the field name a business user reads, not buried in the model a business user never opens.

  1. 01
    Snapshot the hierarchy honestlyCapture the org, territory or cost centre structure with real effective dates, from the source system if it keeps them, and from a governed mapping file if it does not. Build the type 2 table in dbt on top of that, and treat the effective dates as the input you must get right rather than a field you can derive.
  2. 02
    Key the facts to the version, not the entityThe fact row stores the surrogate key of the dimension version that was valid on the event date. This is set once, at load, and never changes afterwards. It is what makes as-was reproducible three years later without re-running anything.
  3. 03
    Keep a current view alongside itA simple view filtered to the latest version of each natural key, in Snowflake or Postgres, whichever the warehouse is. Cheap to build, and it is what the as-is join uses.
  4. 04
    Expose both in the semantic layer with different namesTerritory (at time of sale) and Territory (current). Not one field with a toggle. Two fields, both selectable in Power BI, both documented in one sentence, so that a chart's meaning is legible from its axis label.
  5. 05
    Default to as-was for anything with money or people attachedCommission statements, quota attainment, published figures. Make as-is an explicit choice the user makes, because as-is is the view that quietly rewrites history and it should cost one deliberate click.
The order matters. Effective dates first, keys second, naming last and most important.

The damage is not caused by choosing the current structure or the historical one. It is caused by choosing silently.

The pattern, stated plainly

The failure modes are almost all about dates

Once teams accept the model, the work that actually consumes the week is not the SQL. It is reconciling what the business means by when.

  • Effective date versus load date A type 2 table built from load timestamps records when your pipeline noticed the change, not when the change took effect. If HR approves a cost centre move on the fifteenth and the file lands on the twenty-eighth, thirteen days of activity land in the wrong bucket. This is the single most common way a correct-looking history turns out to be fiction.
  • Backdated changes Reorgs are routinely announced in March and made effective from January. Your model needs to be able to insert a version into the middle of an existing timeline and repoint the affected facts, which means the fact-to-version assignment cannot be a one-way write you never revisit.
  • Reused codes A cost centre is closed and eighteen months later the same code is issued to an unrelated team. The natural key is no longer a key. You find this by looking, not by testing, and the only fix is a mapping decision somebody senior has to make.
  • Merges and splits Two territories become one, or one becomes three. There is no clean version chain to write, because the relationship is many to many across the boundary. Decide explicitly whether the merged entity inherits both histories, neither, or one, and write that decision down where the reader of the dashboard can find it.
  • Mixed reports An as-was measure sitting next to a filter on current territory. It runs, it returns rows, and the total is meaningless. Naming the fields distinctly is what prevents this, which is why the naming is not cosmetic.
A single month drawn as a timeline. The cost centre move takes effect on the fifteenth and the source file lands on the twenty-eighth, with the thirteen days between them shaded. Below, two bars: versions keyed to the effective date split on the fifteenth, so the thirteen days sit with the new cost centre; versions keyed to the load timestamp split on the twenty-eighth, so the same thirteen days stay with the old one.
Thirteen days is what one late file costs. The table looks identical either way, which is why this is found by reading the load logic rather than by looking at the report.

When a slowly changing dimension is the wrong answer

Type 2 everything is a bad instruction and we have talked clients out of it. Every versioned dimension adds a join condition, a set of tests, a class of bug where somebody forgets the date predicate and doubles the revenue, and a permanent explanation cost for every new analyst. If the hierarchy has changed once in four years and nobody has ever asked for the old shape, a plain overwrite with a dated note in the changelog is the better engineering. The question to ask is not whether the attribute can change, it is whether anyone will ever need to report on it as it was.

There is a harder case where the answer is that this is not a data project at all. If the org structure lives in a spreadsheet that gets overwritten each quarter and no one can tell you the effective date of the last change, then building a type 2 dimension on top of it produces a history that looks authoritative and is not. You would be encoding your pipeline's schedule as though it were the company's decisions. Fix the record first. That is an operations conversation, and it is usually a short one, because the person who owns the reorg already knows the date they announced it.

Somebody has to own the mapping

The reorg mapping, old entity to new entity with an effective date, is a business artefact and it should be produced by the people running the reorg, before go-live, not reverse engineered by an analyst in week three from whatever the CRM happens to contain. It is usually one page. Getting it as an input rather than an archaeology exercise is the difference between a change that costs a day and one that costs a month and still leaves people arguing about last year. When we scope this work in a Week 1 audit, that document is the first thing we ask for, and how quickly it can be produced tells us more about the reporting problem than any look at the warehouse does.

The reorg will happen again. It always does. A model that can answer both questions turns the next one into a data load rather than a rebuild, and, more usefully, it turns the meeting about the moved numbers into a thirty second explanation instead of a fortnight of quiet distrust.

Before the next reorg lands

If your territory or cost centre history is about to be overwritten again, the cheapest moment to fix it is before the change goes live, not after someone spots a moved number. Our Week 1 audit looks at how your hierarchies are recorded, which reports would silently restate, and what the mapping document needs to contain. Fixed fee, two calls, and you keep the one-pager whether or not we build anything.

Contact us →More writing →