HR Analytics with Fabric and Dynamics 365 Human Resources

No more reconciling five spreadsheets. One lakehouse, one truth, real decisions.

What you’ll learn

• Why HR metrics don’t reconcile—and how a lakehouse fixes it

• The reference architecture for HR analytics in Microsoft Fabric

• How to connect Dynamics 365 HR, payroll/benefits, time & attendance, IT offboarding, and surveys

• Must-have HR dashboards (headcount, onboarding, leave, DEI, attrition) with automated refresh

• Guardrails for privacy, security, and data quality (PII minimization, RLS, sensitivity labels)

• How to add predictive insights (attrition/burnout risk) once the foundation is set

---

The problem in one sentence

Siloed HR, payroll, and time data create mismatched numbers, slow decisions, and missed risk signals.

---

Reference blueprint (Fabric + D365 HR)

Ingest & stage

• Sources: Dynamics 365 HR (employees, jobs, hires/terms, org), payroll/benefits SaaS, time/leave, IT identity/offboarding, engagement/surveys (eNPS), recruiting/ATS.

• Pipelines: Dataflows Gen2 for APIs/flat files; scheduled full + incremental loads.

• Storage: OneLake / Lakehouse layers

  • Bronze: raw snapshots (as-is)

  • Silver: cleaned/standardized (IDs, dates, codes)

  • Gold: conformed HR model for analytics

Model & serve

• Conformed entities: Employee, Job, OrgUnit, Hire, Termination, LeaveBalance, LeaveRequest, TimeEntry, CompensationBand, DiversityAttributes, AccessProvisioning, SurveyResponse.

• Keys & mapping: canonical EmployeeId (crosswalk for HR/payroll/IT GUIDs), PositionId, ManagerId, OrgPath.

• Semantic layer: certified Power BI dataset with row-level security by region/org role.

Consume & act

• Dashboards: HR control panel + role-based views (HRBP, TA, Payroll, DEI, Exec).

• Alerts & automation: Power BI/Power Automate for SLA breaches, leave anomalies, offboarding gaps.

• Predictive: AutoML/Azure ML for attrition/burnout risk and onboarding delay forecasts.

---

Must-have dashboards & metrics

1) Headcount & movement

• Active HC (FT/PT/contract), net change, joins/exits, internal mobility, vacancy ratio.

• Quality check: HR vs IT accounts (ghost users), payroll sync deltas.

2) Onboarding SLA

• Time-to-hire, time-to-start, provisioning completion (accounts, devices, apps), day-1 readiness.

• Alerts: any step > SLA; missing manager check-ins at day 7/30/60/90.

3) Leave & availability

• Accruals vs balances, planned vs unplanned, overtime/TOIL, leave spikes by team/season.

• Controls: negative balances, payroll mismatch, cost center drift.

4) Attrition & retention

• Voluntary/involuntary rates, tenure bands, regrettable loss, exit reasons.

• Leading indicators: absence surge, low eNPS, stalled promotion velocity, unmanaged overtime.

5) DEI insights

• Representation by level/org/location, hiring funnel drop-offs, pay-band distribution.

• Privacy: small-n suppression & aggregation thresholds.

6) Compliance & audit

• Offboarding SLA (account disablement), mandatory training completion, data access logs.

---

Gold-layer data model (starter set)

Facts:

• FactHeadcountSnapshot, FactHire, FactTermination, FactOnboardingStep,
  FactLeaveBalance, FactLeaveRequest, FactTimeEntry, FactCompChange, FactSurveyScore

Dims:

• DimEmployeeMasked, DimOrgUnit, DimJob, DimManager, DimGeo, DimDiversityAttr, DimCalendar

Lineage links: Employee ↔ Job/Org over time (SCD2); Hire → Provisioning → FirstLogin; Leave Accrual ↔ Payroll Posted.

---

Dataflow patterns (that stop reconciliation hell)

• ID crosswalk: build once; persist as a gold table to join HR/Payroll/IT identities.

• Code normalization: job, band, leave types, cost centers to canonical lists.

• Date conformance: UTC, fiscal calendar, effective-dated SCDs (employment, comp).

• Incremental refresh: only changed rows (hire/term events, balances).

• Quality gates: duplicate employee IDs, orphan managers, negative balances, future-dated terms.

---

Security, privacy & governance

• PII minimization: exclude DOB, SSN; use masked DimEmployee.

• RLS: by HRBP portfolio, region, or org tree; sensitivity labels on gold/semantic.

• Small-n protection: suppress DEI slices < threshold; show “insufficient data”.

• Data contracts: SLAs for freshness, schema change policy, owner + steward per table.

• Audit: log data refreshes, access to sensitive visuals, and all export events.

---

Alerts & automations (copy/paste starters)

• Onboarding breach: step > SLA ➜ notify manager + IT; auto-create ticket.

• Leave anomaly: sudden spike > X% vs baseline ➜ alert HRBP; review staffing risk.

• Offboarding gap: termed but active M365 account ➜ immediate disable + confirmation.

• Headcount mismatch: HR active ≠ Payroll paid ➜ reconciliation task to Payroll Ops.

• Overtime fatigue: OT > threshold for 3 weeks ➜ manager nudge + wellness resources.

---

Predictive add-ons (when foundation is stable)

• Attrition risk model: features = tenure, comp delta vs band, manager span, absence & OT, survey trend, promotion wait.

• Onboarding delay forecast: predict bottleneck steps by role/site/vendor.

• Burnout early warning: combine OT, weekend work, low eNPS, leave deferral.

Principles: explainable features, fairness checks, opt-in pilots, human review before action.

---

14-day quick start

Days 1–3 Connect sources (D365 HR, payroll export/API, time/leave).
Days 4–6 Build ID crosswalk; silver cleans; gold conformed entities.
Days 7–9 Publish certified semantic model with RLS; headcount + onboarding dashboards.
Days 10–11 Wire alerts: onboarding SLA, offboarding gap.
Days 12–14 Reconcile with Payroll/IT; document data contracts; exec demo & next-step backlog.

---

Common pitfalls (and fixes)

• Mismatched IDs: create & maintain the crosswalk (don’t rely on names/emails).

• Spreadsheet drift: lock certified datasets; discourage ad-hoc extracts.

• PII sprawl: mask employees in dims; apply labels + export controls.

• Over-modeling day 1: ship HC + onboarding SLAs first; iterate to leave/DEI/attrition.

• Alert fatigue: start with 3–4 high-value rules; add severities & cooldowns.

---

FAQs

Q: Can we do this without Dynamics 365 HR?
A: Yes—use Dataflows Gen2 for any HRIS/payroll/ATS exports or APIs; the lakehouse pattern stays the same.

Q: How often should data refresh?
A: Headcount/DEI daily; onboarding/leave every 1–4 hours; compliance/offboarding near real-time.

Q: How do we protect sensitive DEI data?
A: Aggregate at safe levels, apply small-n suppression, and enforce RLS + sensitivity labels.

Q: Do we need data scientists for predictions?
A: Start with AutoML and interpretable features; graduate to Azure ML as needs grow.