An Org Chart Walks into a Warehouse

Through four versions, KC Star answered one question: what happened to this sale? Dimensions as rows, measures as rows, SCD2 history, quality scores and lineage — all of it orbiting a single Sales table. V4 gave the warehouse a conscience; it still had no idea who sold anything.

V5 is the version where the org chart walks in. Not as a lookup column on Sales — as an entire second schema: an organisation that owns regions, regions that contain offices, offices that employ people, and people who make sales. And because this is KC Star, the hierarchy doesn't just get tables. It gets its own star.

The traditional side of the org

The base tables are deliberately ordinary. Four levels, each with a foreign key up:

CREATE TABLE Organisation (
    OrgId SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Code VARCHAR(50) UNIQUE NOT NULL,
    Industry VARCHAR(100),
    Currency VARCHAR(3)
);

CREATE TABLE Regions (
    RegionId SERIAL PRIMARY KEY,
    OrgId INT NOT NULL REFERENCES Organisation(OrgId),
    Name VARCHAR(255) NOT NULL,
    Code VARCHAR(50) NOT NULL,
    Budget DECIMAL(18,2),
    TargetRevenue DECIMAL(18,2),
    UNIQUE(OrgId, Code)
);

Offices hangs off Regions the same way (address, city, a Capacity column I have never once queried), and Personnel hangs off Offices with EmployeeNumber UNIQUE, Position, Salary and a CommissionRate. The composite uniques — UNIQUE(OrgId, Code), UNIQUE(RegionId, Code) — mean region codes only need to be unique within their organisation, which is how real org charts actually behave.

The interesting table is the fifth one. EmployeeSales is where the org world touches the sales world:

CREATE TABLE EmployeeSales (
    EmployeeSaleId SERIAL PRIMARY KEY,
    EmployeeId INT NOT NULL REFERENCES Personnel(EmployeeId),
    ExternalSalesId INT,  -- References Sales.Id from main database
    QuoteAmount DECIMAL(18,2),
    DeliveredAmount DECIMAL(18,2),
    CommissionEarned DECIMAL(18,2)
);

That comment on ExternalSalesId is doing a lot of quiet work. There is no foreign key — deliberately — because in the V5 fiction the sales tables live in a different database. The whole cross-database story hangs off that one un-enforced integer, and it gets a post of its own.

The org gets its own star

Here is the move that makes V5 more than “we added five tables.” The organizational data goes through exactly the same inversion the sales data went through in V1: dimensions stored as rows, measures stored as rows, types declared in lookup tables.

OrgDimTypeLookup and OrgMeasureTypeLookup mirror their sales counterparts, with one addition each — a HierarchyLevel column, because an org dimension row needs to say which altitude it describes:

INSERT INTO OrgMeasureTypeLookup (MeasureTypeId, MeasureName, HierarchyLevel) VALUES
(1, 'TotalQuotes', 'Employee'),
(2, 'TotalDelivered', 'Employee'),
(3, 'TotalCommission', 'Employee'),
(4, 'QuoteToDeliveryRatio', 'Employee');
-- ...14 organizational measures in total

OrgDim is the row store — SCD2 columns, HierarchyLevel, and the V4 governance columns (DataQualityScore, SourceSystem, BatchId) carried forward. OrgFact holds the computed measures. CurrentOrgVersion caches the SHA-256 hash of the current org state, exactly as CurrentSalesVersion does for sales.

And the automation is the same pattern too. Three triggers keep the org star current:

• TR_Personnel_To_OrgDim_V5 — a new or changed employee explodes into OrgDim rows via ProcessPersonnelToOrgDim().
• TR_EmployeeSales_To_OrgDim_V5 — employee sales activity flows in via ProcessEmployeeSalesToOrgDim().
• TR_OrgDim_SCD2_V5 — a dimension update expires the old row, inserts the new one, recomputes CalculateOrgVersionHash(), and calls CreateOrgFacts().

If you have read the V1 series, that list should feel like déjà vu, suffix included. The version-suffixed trigger names (_V4, _V5, _V6...) exist because more than one version's schema can end up installed in the same database, and two triggers with the same name is how you get the SCD2 infinite-loop stories the V6 README still warns about.

Fourteen measures, and then eighteen more

The 14 organizational measures cover the hierarchy's own vital signs: TotalQuotes, TotalDelivered, TotalCommission, QuoteCount, DeliveryCount, QuoteToDeliveryRatio, AvgQuoteValue, AvgDeliveryValue, repeated at the levels where they make sense.

But the sales star grows too. V5 adds measures 11–18 to the sales MeasureTypeLookup — cross-database measures, each one a windowed aggregate partitioned by an org level:

-- MeasureTypeId 11: OrgLevelTotalSale
SUM(TotalSale) OVER (PARTITION BY OrgId)
-- MeasureTypeId 15: OfficeLevelTotalSale
SUM(TotalSale) OVER (PARTITION BY OfficeId)
-- ...down to EmployeeLevelNetRevenue at 18

One sale, valued at every altitude of the org chart simultaneously. That is the payoff the whole version is built for, and part 3 walks through the rollup machinery that computes it.

Three stars, if you're counting

I said the org gets its own star, which brings the count to two. The honest count is three. Alongside SalesDim/SalesFact and OrgDim/OrgFact, V5's root setup script ships an analytical star — AnalyticalDim and AnalyticalFact, keyed by a polymorphic ReferenceId plus a HierarchyLevel string, fed by real-time triggers, with an AnalyticalUpdateLog tracking every propagation.

Why three? Because V5 exists in two materially different implementations — the version folder tells a batch/SCD2 story, the root DatabaseV5.sql tells a real-time story, and they do not contain the same objects. That divergence deserves its own honest write-up, and it gets one in part 4. By the numbers I care about, the folder build lands at 20 tables, 9 functions, 8 procedures, 7 views and 45 indexes; the root overlay adds the analytical layer on top of a V4 database instead.

Where the series goes

1. An org chart walks into a warehouse — this post.
2. The bridge table with two passports — CrossDatabaseFact, ExternalSalesId, and pretending one PostgreSQL instance is two databases.
3. Rollups at every altitude — MultiLevelRollup, CreateOrganizationalRollups, commissions and quote-to-delivery ratios.
4. One version, two implementations — the folder/root fork, and what I learned from letting variants drift.
5. The analytical star and its update log — the third star, the queue that isn't a queue, and the handoff to V6's security story.

The org chart is in the warehouse. Next: how a sale in one “database” gets claimed by an employee in another.