Supervisory Organization

The reporting tree. Every worker is hired into a supervisory organization, and every rollup in every people report follows this hierarchy. It is recursive, effective-dated, and no BI tool can use it as delivered.

RuleFlatten the tree into level columns in your warehouse, and keep the history as the tree changesNeverrebuild org assignment from event feeds. A reorg is an event on the supervisor, not on the workers it moves.

◆ What it is— the required org type among several, each answering a different question.

Organization type	What it answers	Note
Supervisory	Who reports to whom. The management tree.	Required. Every worker is hired into exactly one, per position. Each carries a manager and a staffing model, position management or job management.
Company	Which legal entity employs and pays.	The payroll and statutory rollup.
Cost center	Where the cost lands in finance. See Cost Center & Company.	The bridge to the general ledger, and the key that conforms with the ERP models.
Custom & matrix	Anything else the business slices by: region, program, dotted-line teams.	Useful for reporting; not the management tree.

A worker therefore carries several organization assignments at once, one per type. Reports that mix them, headcount by cost center in one chart and by supervisory org in the next, answer different questions. State which tree a metric uses before comparing numbers.

◆ The flatten recipe— from a recursive tree to level columns and a rollup path any BI tool can use.

Workday stores the hierarchy as each org pointing at its parent. Dashboards need it the other way: fixed columns, level 1 through level N, plus the full path, so a bar chart can group by level 2 and a drill-down can walk the path. Land the org list with its parent references, then flatten once in the warehouse:

-- stg_sup_org: org_id, org_name, parent_org_id, manager_id, valid_from, valid_to
WITH RECURSIVE org_tree AS (
  SELECT org_id, org_name, parent_org_id, manager_id,
         1 AS org_level,
         org_name AS org_path
  FROM stg_sup_org
  WHERE parent_org_id IS NULL
  UNION ALL
  SELECT o.org_id, o.org_name, o.parent_org_id, o.manager_id,
         t.org_level + 1,
         t.org_path || ' > ' || o.org_name
  FROM stg_sup_org o
  JOIN org_tree t ON o.parent_org_id = t.org_id
)
SELECT * FROM org_tree

Pivot org_level into fixed columns, level_1 through your deepest level, and dim_org is done. Two working rules: cap the levels at your real depth plus one so a future reorg does not break the schema, and rebuild the flattened table whenever the org list changes rather than patching it; the recursion is cheap.

◆ The reorg trap— org changes are not evented for the workers they move.

When a supervisor moves, or a branch of the tree is reassigned, the event lands on the supervisor and the organizations involved, not on the workers underneath. An extract that watches worker events sees nothing, and every direct report keeps their old rollup in your warehouse while Workday shows the new one. The defense: pull each worker's current organization assignments on every load, effective-dated,. Rebuild membership from assignment data, not from events. The reload layering follows effective dating.

◆ How we model it: dim_org— the pivot SQL, and what the finished dimension looks like, three rows that show the whole technique.

Pivot the rollup path from the recipe above into fixed level columns, and carry the validity dates through:

-- SPLIT_PART on Snowflake, Postgres, Redshift; use SPLIT()[SAFE_OFFSET(n)] on BigQuery
SELECT org_id, org_name, manager_id,
       SPLIT_PART(org_path, ' > ', 1) AS level_1,
       SPLIT_PART(org_path, ' > ', 2) AS level_2,
       SPLIT_PART(org_path, ' > ', 3) AS level_3,
       valid_from, valid_to
FROM org_tree

The finished dimension, sample rows. On 2026-04-01 a reorg moved Field Sales West from Sales to the new Revenue Org: the old row closes, a new row opens, and the tree's history is data.

org_id	org_name	level_1	level_2	level_3	manager	valid_from	valid_to
SO_4102	Field Sales West	Acme Corp	Sales	Field Sales West	M. Chen	2024-01-01	2026-03-31
SO_4102	Field Sales West	Acme Corp	Revenue Org	Field Sales West	M. Chen	2026-04-01	9999-12-31
SO_4110	Sales Ops	Acme Corp	Sales	Sales Ops	R. Patel	2024-01-01	9999-12-31

A fact dated 2026-02-15 joins the first row; one dated 2026-05-01 joins the second. Old reports keep the rollup that was true at the time, and every people fact joins this one dimension, so headcount and payroll cost use the same tree. See the enterprise model. Sample values are illustrative, never client data.

Want the org dimension flattened and owned?

We build dim_org with the levels, the history, and the reorg-proof reload, reconciled against Workday, and you own every line.

Talk to us

Terms on this page

supervisory organization: The management tree. Every worker is hired into one, per position.
staffing model: How an org hires: position management, into defined seats, or job management, open hiring.
matrix organization: A dotted-line team alongside the management tree, for reporting, not hiring.
recursive hierarchy: Each org points at its parent. Depth is unbounded until you flatten it.
flatten: Turning the tree into fixed level columns and a path, the shape BI tools need.
recursive CTE: The SQL construct that walks the tree, WITH RECURSIVE, in one query.
rollup path: The full chain from the top org to this one, for drill-downs.
org assignment: A worker's membership in each organization type, effective-dated, per position.
as-of join: Joining a fact to the org version valid on the fact's date.
dim_org: The flattened, historized org dimension every people fact joins.