Oriental Nippon Development
Back to portfolio
Case studyMulti-branch staffing & recruitment group

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.

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.
BeforeAfter
Data sources3, combined by hand1 scheduled pipeline
Measure definitionsInformal~12 agreed, documented measures
Commission viewSeparate spreadsheetDashboard with drill-through
FreshnessWhen someone consolidatedAlways 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

TechnologyRole in this project
Python and FlaskAPI integration with the staffing platform
AzureCloud data store and scheduling
SQLWarehouse layer and tested business logic
Power BICommission 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.

  1. Agree the measures. Definitions, ownership and drill-through needs.
  2. Ingestion. From the operational platform, scheduled and monitored.
  3. Modelling. Warehouse layer with tested, documented business logic.
  4. Dashboards for decisions. Commission views for managers, financial views for leadership.
  5. Govern. An owner and change process per measure, so definitions don't drift.
Delivery timelineIndicative total: 13–16 weeks
  1. 01Define the measures

    2 wks

    Agreed definitions, ownership, drill-through requirements

  2. 02Ingestion

    3–4 wks

    API pipeline into cloud storage, scheduling, alerting

  3. 03Modelling

    3–4 wks

    Warehouse layer, business logic, tests

  4. 04Dashboards

    3–4 wks

    Commission and branch financial views

  5. 05Governance and hand-over

    2 wks

    Metric dictionary, change process, documentation

Risks and how we handled them

RiskHow we handled it
Dashboards built on contested numbersThe dozen key measures agreed with leadership before any pipeline
Silent pipeline failuresMonitoring and failure alerting on every scheduled load
Definitions drifting over timeAn 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

  1. What is Power BI?, Microsoft Learn
  2. Conformed dimensions, Kimball Group
  3. 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.