One Always-Current Commercial and Financial View for a Multi-Branch Staffing Group
Placements, commissions and financials lived in three places, so benchmarking branches meant manual consolidation. We agreed the measures first, then built an Azure pipeline, a governed model and Power BI dashboards.
- Service
- AI Business Applications
- Industry
- Professional Services & Staffing
- Region
- United States
- Engagement
- Lab-type ODC (dedicated team)
Outcomes
- 3 sources → 1 governed model
- Commissions tied to placements
- Branch benchmarking on demand
Stack
Python · Flask · Azure · SQL · Power BI
Written byOriental Nippon Team
A US staffing and recruitment group runs several branch offices, each with its own placements, revenue, margin and sales team. Performance data lived in the staffing platform, commissions were tracked somewhere else, and financial results came through a third route.
Comparing two offices meant stitching three sources together by hand, and no one had agreed what "branch profitability" actually meant.
The problem
- Three sources, assembled manually. Every benchmark was a reconstruction.
- Commissions disconnected. Commission calculations were separate from the placement data that drives them.
- Slow decisions. Targets and resource allocation waited on a manual consolidation.
- No shared definition. Branch profitability meant different things to different people.
| Before | After | |
|---|---|---|
| Data sources | 3, combined by hand | 1 scheduled pipeline |
| Measure definitions | Informal | ~12 agreed, documented measures |
| Commission view | Separate spreadsheet | Dashboard with drill-through |
| Freshness | When someone consolidated | Always current |
What we built
An ingestion pipeline
Python and Flask pull operational data from the staffing platform's API into Azure on a schedule, with monitoring and failure alerts.
A modelled reporting layer
Documented definitions for placements, revenue, margin and profitability, with tests.
Decision dashboards
Commission views for managers and branch financials for leadership in Power BI, with drill-through to the underlying records.
Governance
An owner and a change process for each measure.
Technology and the role it played
| Technology | Role in this project |
|---|---|
| Python and Flask | API integration with the staffing platform |
| Azure | Cloud data store and scheduling |
| SQL | Warehouse layer and tested business logic |
| Power BI | Commission and branch financial dashboards |
How we delivered it
Before writing any pipeline, we sat with leadership and agreed the dozen or so numbers they actually make decisions on.
- Agree the measures. Definitions, ownership and drill-through needs.
- Ingestion. From the operational platform, scheduled and monitored.
- Modelling. Warehouse layer with tested, documented business logic.
- Dashboards for decisions. Commission views for managers, financial views for leadership.
- Govern. An owner and change process per measure, so definitions don't drift.
01Define the measures
2 wks
Agreed definitions, ownership, drill-through requirements
02Ingestion
3–4 wks
API pipeline into cloud storage, scheduling, alerting
03Modelling
3–4 wks
Warehouse layer, business logic, tests
04Dashboards
3–4 wks
Commission and branch financial views
05Governance and hand-over
2 wks
Metric dictionary, change process, documentation
Risks and how we handled them
| Risk | How we handled it |
|---|---|
| Dashboards built on contested numbers | The dozen key measures agreed with leadership before any pipeline |
| Silent pipeline failures | Monitoring and failure alerting on every scheduled load |
| Definitions drifting over time | An owner and change process for each measure |
Outcome
Leadership has one always-current view across branches. Benchmarking, target setting and commission decisions now use the same numbers instead of three reconstructions of them.
The documented measures are what decide whether this view is still trustworthy a year from now.
What the client keeps
- Pipeline code, in their own repository
- Warehouse schema and models
- The metric dictionary
- Editable dashboards
- The data-quality test suite
Sources and further reading
- What is Power BI?, Microsoft Learn
- Conformed dimensions, Kimball Group
- Azure Well-Architected Framework, Microsoft Learn
Facing something similar? This is the kind of work our AI, Data Science & Automation team delivers every day. Talk to us about your stack.
