One model, not eight marts

Every people fact joins the same worker, the same org, the same cost center, and the same calendar. A number means one thing in every report, and each new module joins dimensions that already exist.

◆The enterprise model

Several stars sharing one dimension set is a galaxy schema: one fact per grain, dimensions defined once. That is the whole design.

RuleOne worker, one org, one calendar, joined by every fact that needs themNeverbuild per-report marts. Marts that each define headcount their own way are how dashboards start disagreeing.

◆ The facts— one table per grain: the daily state, the events, the payroll lines, the applications.

One star, up close. The fact holds the measures and the keys; each dimension around it holds the descriptions. Every other fact in the model is the same shape, joined to the same dimensions.

Fact	Grain	What it answers
fct_worker_snapshot	One row per worker per day or month.	Headcount, FTE, and cost trends at any point in time. Workday does not keep this table; you build it and keep it. See the Headcount & FTE Trend report.
fct_worker_event	One row per hire, transfer, promotion, termination, or leave.	Movement: hires, exits, internal mobility, turnover. See the Movement report.
fct_payroll_result	One row per payroll result line.	Payroll cost by organization, variance, and reconciliation to the general ledger.
fct_application_event	One row per application stage change.	Funnel conversion, time to fill, source effectiveness.
fct_time_off	One row per absence event, plus a balance snapshot.	Absence rates and PTO liability.

Facts hold the measures and the keys. The descriptive detail lives once, in the shared dimensions, so it is never duplicated or contradicted across modules.

◆ The conformed dimensions— one worker, one org, one job, one cost center, one calendar, used by every fact.

Dimension	What it is	Shared by
dim_worker	The person, as SCD2: one row per effective-dated change, with the worker type flag. See Worker.	every fact
dim_org	The supervisory organization, flattened from Workday's recursive tree into levels a BI tool can use. See Supervisory Organization.	every fact
dim_job	Job profile, family, and level. See Job Profile.	workforce, compensation, recruiting
dim_location	Location, region, country.	every fact
dim_cost_center	Cost center and company. The bridge to finance. See Cost Center & Company.	workforce, payroll, time
dim_date	Calendar, pay period, and fiscal period.	every fact
dim_pay_component	Earning and deduction codes.	payroll

Conformed means the same dimension table, the same keys, joined by every fact that needs it. Cost center 4100 is the same thing whether you reach it from a headcount report or a payroll report, so the reports reconcile instead of arguing. Because the dimensions are shared, headcount by org and payroll cost by org use the same org list, one version of the truth, and a new module joins dimensions that already exist instead of rebuilding them.

◆ Across platforms— the cost center, company, and calendar conform with the Oracle Fusion and SAP S/4HANA models.

dim_cost_center, dim_company, and dim_date carry the same keys as our SAP S/4HANA model and the Oracle Fusion model. People cost per cost center from Workday joins financial spend per cost center from the ERP, in one model, with no remapping. Fully loaded cost per department, revenue per head, and plan versus actual headcount cost all become single queries instead of reconciliation projects.

◆ How it is built and owned— extracted the reliable way, built with dbt, reconciled before go-live, on your cloud.

Step	How
Source	The Worker and Payroll Result objects, through the delivered data sources. See the object model.
Extract	RaaS or WQL, filtered by effective and entry date, with the layered reload. See the extraction pattern and effective dating.
Build	dbt models: the facts and the conformed dimensions, with tests on grain, keys, and reconciliation.
Reconcile	Headcount and payroll totals tied to Workday before go-live, and checked daily after.
Own	Your cloud, your Git, every line of code yours. No per-seat platform fee.

Want this enterprise model built and owned?

We build the facts and the conformed dimensions on your cloud, reconciled against Workday before go-live, and your team owns every line.

Talk to us

Terms on this page

fact: A table of measures and keys, one row per event or per state capture.
dimension: A descriptive lookup you slice by: worker, org, job, cost center, date.
conformed dimension: One dimension shared by every fact, so a value means the same thing everywhere.
grain: What one row of a fact represents, the level of detail.
star schema: One fact surrounded by its dimensions, the report-friendly shape.
galaxy schema: Several facts sharing conformed dimensions, the enterprise model.
SCD2: Slowly changing dimension, type 2: one row per change, with validity dates.
snapshot fact: A table capturing the state of every worker on each day or month.
Worker: Workday's hub object, covering employees and contingent workers.
Payroll Result: The object holding every payroll calculation line.
dbt: A popular open tool for building and testing the models in your warehouse.
reconciliation: Tying the model's totals to the source system, before go-live and daily after.