From Spreadsheets to Workforce Planning Intelligence

Challenge

Workforce planning and delivery reporting depended on two different views of the same work: planned capacity in spreadsheets and actual effort in Jira exports.

Bringing those views together required more than combining files. Employee identities needed to match across sources. Work dates had to align with the selected reporting period. Calendar weeks and delivery sprints needed a consistent relationship. Repeated uploads and replacement exports also had to be distinguished from new information.

Without a shared processing model, each reporting cycle carried the same preparation burden before managers could compare allocation, capacity, and recorded effort.

Approach

The solution separates file submission, business processing, data storage, and reporting. A Vue upload experience provides the entry point. Azure Functions process the files and apply server-side checks. Azure SQL holds the import history, mapping data, and reporting model. Power BI consumes the prepared data.

Three engineering decisions shape the solution:

Make imports repeatable. Content hashes identify files that have already been processed. Reporting-period checks catch mismatches between the period selected by the user and dates in the Jira export. Batch tracking records processing state, while superseding older snapshots distinguishes replacement data from a new reporting period.

Resolve differences between the sources explicitly. Employee identity mappings connect planning records with Jira users, including cases where a username is missing. Shared calendar and sprint mappings give planned and actual facts a consistent basis for comparison.

Keep source records separate from reporting data. Raw imports, reference mappings, curated facts, and reporting views have distinct responsibilities. This preserves the source context while preparing a model that reports can reuse.

Infrastructure definitions, deployment workflows, and operating documentation accompany the application so that environment setup and support are part of the delivery.

Solution Architecture

Planning spreadsheets       Jira exports
          \                    /
           Vue upload experience
                    |
              Azure Functions
       Parsing, duplicate and period checks
                    |
                Azure SQL
       Import history and source records
       Employee and calendar mappings
       Planned and actual reporting facts
                    |
                 Power BI
       Capacity and effort comparison

Bicep defines Azure infrastructure, and Azure DevOps supports infrastructure and Function delivery. Managed Identity provides the Function-to-SQL authentication path.

The reporting model separates the question a manager wants to answer from the structure of the original export. Reports can consume prepared facts and mappings instead of repeating file-specific transformations.

Outcome

The implemented foundation brings workforce plans and Jira actuals into a common reporting model with explicit import controls and processing history.

• Repeatable intake: content-based duplicate detection and period checks address specific errors before data is used downstream.
• Consistent reporting inputs: employee, week, and sprint mappings support planned-versus-actual comparisons.
• Traceable processing: batch states and superseded snapshots distinguish processing attempts and replacement imports.
• A maintainable delivery foundation: application code, database objects, infrastructure definitions, and handover documentation capture how the solution works.

These results describe implemented capabilities. Full worklog reconciliation and end-to-end deployment validation remain areas for completion; reductions in reporting effort and improvements in utilization have not yet been quantified.

The pattern is applicable wherever recurring operational reports combine spreadsheet plans with exports from a delivery system: establish reliable inputs, resolve shared identities and periods, and build reporting on the prepared model.

Technologies

• Vue for file submission and user interaction
• C# / .NET and Azure Functions for processing
• Azure SQL for import history, mappings, and reporting facts
• Power BI for analysis and reporting
• Bicep and Azure DevOps for infrastructure and Function delivery
• Managed Identity for service-to-database authentication