Engineering insight
From departmental spreadsheets to dependable operational reporting
Build traceable operational reporting from departmental spreadsheets with shared definitions, data quality checks, reconciliation and visible freshness.
In this note
Two departments can report different totals from the same operational system without either making a calculation error. One counts requests received during the month; another counts requests completed during the month. Both label the result “monthly volume.”
Connecting their spreadsheets to a dashboard will make the disagreement refresh faster. Dependable reporting starts by agreeing on what the numbers represent, where they come from and how a reader can investigate a discrepancy.
Define the decision before the dashboard
Choose an operational question with a clear owner. For example: which locations have a growing maintenance backlog, and what information does a supervisor need to investigate it?
For each proposed measure, record its definition, unit, reporting period, inclusion rules and accountable owner. Specify how cancelled, duplicated, reopened and incomplete records are treated. Establish the reporting timezone and whether historical figures may change after corrections.
A short metric dictionary can be enough for an initial release. The value comes from resolving ambiguity before that ambiguity is embedded in several reports.
Map the sources and their limitations
List the source systems, relevant exports, identifiers, refresh schedules and access restrictions. Identify which source owns each important field. A spreadsheet maintained by a department may contain useful local knowledge, but that does not automatically make it authoritative for every attribute.
Retain the source reference and extraction time with imported records. Decide what raw information must be retained to investigate issues, subject to the organization's approved access and retention requirements.
Prefer the smallest data collection that supports the decision. A backlog report rarely needs every attachment, personal detail or free-text comment associated with a request.
An illustrative reporting model
Consider three facilities departments exporting service requests and a finance team exporting supplier invoices. Managers want to compare open work, completed work and maintenance spending by location. This example is hypothetical and represents no client result.
First, define the level of detail in each dataset. A service request, an invoice line and a daily backlog snapshot are different observations. Joining them directly can multiply rows and inflate totals.
A reporting model could maintain:
- Request records with stable request and location identifiers.
- Request status history, or daily snapshots where historical backlog is required.
- Invoice lines linked to requests only where a reliable relationship exists.
- Shared location and date definitions.
- Separate measures for incoming work, completed work and spending.
Microsoft's star-schema guidance (opens in a new tab) explains how facts and dimensions support reporting, and why a consistent level of detail matters. The appropriate model still depends on the questions and source data available.
If invoice lines cannot be reliably associated with requests, report spending by location separately. Making that limitation visible is more useful than inventing a relationship.
Build checks that explain a disagreement
Validation should cover both record structure and operational meaning. Check required identifiers, duplicate keys, permitted status values and relationships to known locations. Treat an unfamiliar location code as an exception to investigate, rather than silently dropping it from the report.
Reconciliation provides a second layer. For a defined period, compare imported record counts and relevant totals with an agreed source report. Account explicitly for exclusions, rejected records and late updates.
In the illustrative model, a supervisor should be able to move from a backlog total to the contributing requests. A missing request should lead to an identifiable import, validation or definition issue. Correcting the dashboard manually would hide that issue from the next refresh.
Show freshness and correction status
Display when source data was last updated and when the report was successfully processed. These are different timestamps. A pipeline can run successfully against an export that has stopped changing.
Freshness checks should follow the business's decision frequency. dbt's source-freshness documentation (opens in a new tab) provides one implementation example and distinguishes freshness checks from ordinary model builds. Equivalent controls can be implemented in other tooling.
Agree on what readers see when a source is late or validation fails. Options include retaining a clearly dated previous result, marking affected measures unavailable, or publishing with a visible limitation. The choice depends on the consequence of acting on incomplete information.
Deliver one report with an operating model
A useful first scope connects a limited set of sources to one decision. It should include a metric dictionary, source-to-report mapping, transformation definitions, validation rules, reconciliation evidence and named responsibilities for exceptions.
Run the new report alongside the existing process for agreed reporting cycles. Investigate differences before removing the old dependency. Preserve useful spreadsheet analysis where it supports exploration, while moving shared recurring calculations into a controlled model.
Acceptance should cover traceability, agreed totals, freshness behaviour, access and recovery after a failed refresh. Attractive charts are valuable when the underlying figures can be explained.
Explore Data Engineering & Integration and Analytics & Decision Support, or Discuss a Project to scope a reporting foundation around a concrete operational decision.