Modernizing a Legacy Manpower Management System
A Data Governance & Quality Framework for Federal HR Data
Situation
A federal defense agency relied on a legacy manpower management system — originally built in 2002 on the .NET framework using Visual Basic, C#, and SQL Server — to manage four critical HR functions: authorized organizational positions, position management, civilian and military personnel records, and personnel-to-position assignments. The system had migrated across multiple infrastructure environments over its lifespan, from on-premises servers to DISA milCloud to Microsoft Azure, accumulating significant technical debt and data quality degradation at each transition.
With the system approaching end of life and a modernization to Microsoft Power Apps underway, a comprehensive assessment revealed that the underlying data was not fit for purpose. Years of ad hoc data entry, absent validation controls, and manual imports from authoritative external systems had produced a data environment that could not reliably support the agency’s HR decision-making — let alone the AI-enabled analytics the organization was pursuing.
Challenge
The assessment identified six compounding data quality failures across the system’s 77 tables:
- Poor Referential Integrity: Primary/foreign key relationships were not uniformly enforced. Data entry screens accepted free-text values with no validation against reference tables, creating orphaned and inconsistent records.
- Poor Data Validation: Fields lacked business rule enforcement beyond basic character limits and data types. The same values were captured as text in some tables and integers in others, breaking downstream joins and aggregations.
- Unmanaged Records Accumulation: System data extended back to 2003 — 13 years beyond the applicable 10-year retention period. Legacy records, many corrupted during prior database and cloud migrations, degraded application performance and analytical reliability.
- Poor Data Normalization: Duplicative data across tables created mismatches and placed unnecessary re-entry burden on users, compounding error rates over time.
- Disconnected Authoritative Sources: The TDA reconciliation process alone consumed well over 100 staff-hours annually, with thousands of positions manually reviewed to determine mapping — a workflow that remained chronically out of sync with authoritative sources.
- Insufficient User Training: Users received no formal training on data entry standards or business processes, producing systematic gaps and errors particularly in fields owned by adjacent stakeholders such as resource management staff.
Without governance controls at the policy, ingestion, and analytics layers, data quality problems were invisible to leadership and self-reinforcing at the user level — a pattern consistent with root causes of failed AI and analytics initiatives across the federal enterprise.
Our Approach
Our team applied a three-layer data governance framework — Data & Application, Policy & Governance, and Analytics & Product — grounded in VAULTIS principles (Visible, Accessible, Understandable, Linked, Trustworthy, Interoperable, Secure) and a Capability Maturity Model assessment to baseline current-state and prioritize remediation investment.
Data & Application Layer
Working within the Power Apps modernization effort, we redesigned the data model from 77 tables to 61 — eliminating redundancy and normalizing structure across the system. Thirteen choice columns replaced open text-entry fields, and 70 lookup relationships were established to enforce referential integrity at the point of entry, with more than half concentrated on the four core tables (Personnel, Position, Position Assignment, and Official TDA) that govern the system’s primary HR functions. Business rules were implemented to detect and prevent entry errors at the point of submission. Automated data purging routines enforced retention schedules, and automated reconciliation pipelines were specified to keep the system synchronized with authoritative external sources of record.
Policy & Governance Layer
Subject matter experts, data engineering staff, and data owners collaborated to identify and curate datasets of institutional importance, classify data by sensitivity and use case, and define quality parameters for each domain. ETL pipeline processes were augmented with automated quality checks that proactively flag records of questionable integrity, generate exception reports, and route issues for steward review. PII safeguards and ontological classification were applied to enable AI agents to interpret data accurately without requiring manual instruction from analytics staff.
Analytics & Product Layer
Information products were tied to organization-approved foundational datasets with transparent documentation of derivation methodology, confidence levels, and limitations. Role-based training programs equipped users to identify and correct common data entry errors in their specific workflows. Data steward assignments ensured analytics products are maintained throughout their lifecycle and reviewed by data owners prior to executive distribution. The refined metamodel — established through the 70 lookup relationships across core tables — positions the system for direct integration with Vantage and confident joining with Army enterprise datasets as downstream AI and analytics requirements emerge.
Results
Reduced data entry error surface
61 tables · 13 choice columns · 70 lookup relationships
The revised data model features 61 tables — down from 77 in the legacy .NET application. Thirteen choice columns replaced open text-entry fields, and 70 lookup relationships were established to enforce referential integrity and eliminate manual data entry. More than half of those relationships were implemented on the four core tables — Personnel, Position, Position Assignment, and Official TDA — that form the foundation of the system. These changes directly promote reliable reporting and create the metamodel AI requires to understand how tables relate.
13 years of non-compliant data removed
Records back to 2003 remediated against 10-year retention standard
System data extended back to 2003 — 13 years beyond the applicable 10-year retention period. Automated purging routines were designed to remove this backlog from the core Position, Position Assignment, and TDA tables, reducing database size, improving application performance, and eliminating the analytical distortion caused by decades of accumulated legacy and corrupted records.
100+ annual staff-hours eliminated
TDA reconciliation: from weeks of manual review to 15 minutes
The previous TDA reconciliation process required well over 100 staff-hours annually — manually reviewing thousands of positions to determine which should be mapped to the Official TDA versus excluded as over-hire, temporary, or term positions. The redesigned business process imports the TDA directly and automatically relates those positions to the agency’s position records, as the workflow was always intended to function. End-to-end TDA import and reconciliation now executes in approximately 15 minutes annually. The next sprint will replace the Excel import with a direct Vantage integration.
AI-ready data model established
Reliable join capability to Army enterprise datasets via refined metamodel
Dataverse-to-Vantage integration is on the roadmap for a future sprint. The foundational investment made here — refining the data model, establishing ontological relationships, and enforcing referential integrity across the four core tables — means G1 and G8 users will be able to confidently join this HR data with other Army enterprise datasets for new analyses. Specific use cases are still emerging, but the metamodel established by the 70 lookup relationships provides the structural foundation AI agents require to interpret and traverse the data reliably.
“Data governance is not a technology problem — it is the orchestration of people, processes, tools, and culture to achieve high levels of confidence in the organization’s most operationally critical asset. Sustainable data quality begins at the point of entry and extends through every layer of the analytics stack.”