# Workforce Margin & Capacity Control Tower

Synthetic Power BI portfolio dataset for **Apex Field Services Group**, a fictional multi-country frontline services provider operating across Kenya, Uganda, Rwanda, Tanzania and Zambia.

## Business Question

Are we paying for the right workforce, in the right place, at the right time, and is that workforce converting into profitable service delivery?

## Dataset Scope

- Company: Apex Field Services Group
- Countries: Kenya, Uganda, Rwanda, Tanzania, Zambia
- Sites: 85 client sites
- Employees: 1,800 frontline, technical and support employees
- Period: 18 months, from 2025-01-01 to 2026-06-30
- Model style: Power BI-ready star schema
- Data status: fully synthetic, deterministic and safe for portfolio use

## Files

| File | Grain | Rows | Purpose |
|---|---:|---:|---|
| `DimDate.csv` | 1 row per date | 546 | Calendar, month, week, fiscal and season attributes |
| `DimEmployee.csv` | 1 row per employee | 1,800 | Role, grade, salary, country, assigned site, skill and certification profile |
| `DimSite.csv` | 1 row per client site | 85 | Client, country, region, business unit, contract and SLA attributes |
| `FactWorkforceDaily.csv` | 1 row per employee per day | 982,800 | Scheduled, worked, overtime, absence, training, idle and productive hours |
| `FactWorkforceDemand.csv` | 1 row per site per day | 46,410 | Required staffing hours, required FTE, demand index and coverage indicators |
| `FactPayrollContractFinance.csv` | 1 row per site per month | 1,530 | Revenue, labour budget, payroll cost, overtime cost, benefits, penalties and margin |
| `FactWorkforceInterventions.csv` | 1 row per site for latest month | 85 | Ranked decision-action table for Page 3 intervention planning |
| `FactRoleWorkforceDetails.csv` | 1 row per site, month, role and grade | 9,234 | Privacy-safe employee/role-level drill-through detail |

## Recommended Relationships

Create these as single-direction relationships from dimensions to facts:

| From | To | Cardinality | Filter Direction |
|---|---|---|---|
| `DimDate[DateKey]` | `FactWorkforceDaily[DateKey]` | 1:* | Single |
| `DimDate[DateKey]` | `FactWorkforceDemand[DateKey]` | 1:* | Single |
| `DimDate[Date]` | `FactPayrollContractFinance[MonthStart]` | 1:* | Single |
| `DimDate[Date]` | `FactWorkforceInterventions[MonthStart]` | 1:* | Single |
| `DimDate[Date]` | `FactRoleWorkforceDetails[MonthStart]` | 1:* | Single |
| `DimEmployee[EmployeeID]` | `FactWorkforceDaily[EmployeeID]` | 1:* | Single |
| `DimSite[SiteID]` | `FactWorkforceDaily[SiteID]` | 1:* | Single |
| `DimSite[SiteID]` | `FactWorkforceDemand[SiteID]` | 1:* | Single |
| `DimSite[SiteID]` | `FactPayrollContractFinance[SiteID]` | 1:* | Single |
| `DimSite[SiteID]` | `FactWorkforceInterventions[SiteID]` | 1:* | Single |
| `DimSite[SiteID]` | `FactRoleWorkforceDetails[SiteID]` | 1:* | Single |

Note: `FactPayrollContractFinance` is intentionally stored at site-month grain to avoid duplicating monthly contract revenue across employee payroll rows. `FactWorkforceInterventions` is a derived latest-month action table for the Decision & Action Centre page. `FactRoleWorkforceDetails` supports drill-through at role/grade level without exposing names or unnecessary personal information.

## Planted Business Patterns

| Pattern | Site | What To Look For |
|---|---|---|
| Prolonged vacancies driving overtime and margin erosion | Nairobi West | High vacancy FTE, rising overtime cost, weak coverage and revenue at risk |
| High absence causing replacement pressure | Mombasa Port | Higher absence FTE, overtime cost and SLA pressure |
| Adequate headcount but insufficient skills/certifications | Kampala Data Centre | Low skill coverage, specialist overtime and penalty exposure |
| Excess workforce capacity after client demand decline | Kigali Retail Cluster | Lower demand, more idle hours and weaker cost per productive hour |
| Improvement after targeted hiring | Dar Rapid Response | Overtime pressure improves after the targeted hiring period |
| Seasonal demand peaks | Multiple sites | Higher demand index and required staffing hours in March, April, August, November and December |
| Country cost profiles | All countries | Salary structures vary by country through KES-equivalent cost multipliers |

## Recommended Report Pages

### 1. Executive Workforce Economics

Purpose: show where workforce cost helps or hurts business performance.

Recommended visuals:
- KPI cards: Workforce Cost, Labour Cost % Revenue, Labour Margin, Budget Variance, Productive Hours %, Overtime Cost, Revenue at Risk, Coverage %
- Monthly combo chart: Revenue and workforce cost as columns, labour margin % as line
- Business-unit performance matrix with conditional formatting
- Margin waterfall: budget margin to actual margin, using overtime, absence cover, vacancies, SLA penalties and training/ramp cost

### 2. Workforce Capacity & Cost Pressure

Purpose: show where demand exceeds available capacity and where cost pressure is hiding.

Recommended visuals:
- Capacity vs demand heatmap by site and week
- Scatterplot: vacancy rate on X-axis, overtime cost on Y-axis, revenue as bubble size
- Paid-hours bridge: scheduled hours to productive hours
- Skill coverage view by country, site and business unit

### 3. Decision & Action Centre

Purpose: rank workforce interventions instead of only reporting problems.

Recommended visuals:
- Intervention table: priority, site, problem, financial effect and suggested action
- What-if hiring vs overtime simulator
- Top sites by revenue at risk
- Sites with coverage below SLA threshold

## Example Intervention Rules

| Condition | Priority | Suggested Action |
|---|---|---|
| Coverage below SLA and overtime dependency above 12% | Critical | Hire, redeploy or reduce contract risk immediately |
| Skill coverage below 85% with high revenue at risk | High | Cross-train or certify priority staff |
| Idle hours above 15% while demand is declining | Medium | Redeploy excess FTE to constrained sites |
| Absence FTE above 2 with rising overtime | High | Attendance intervention and replacement pool planning |

## Power BI Import Notes

1. Import all six CSV files.
2. Set data types before building measures.
3. Mark `DimDate` as the date table using `DimDate[Date]`.
4. Create the relationships listed above.
5. Use `FactPayrollContractFinance` for revenue, cost, margin and budget views.
6. Use `FactWorkforceDaily` and `FactWorkforceDemand` for capacity, attendance, productivity and coverage analysis.
7. Keep the data labelled synthetic in the report footer or documentation page.