Data foundations: integration, quality and tools

0:00 Controls runs on data

Here's a mystery from a real-shaped example. A contractor's cost performance index was swinging wildly: nought point nine one month, one point one the next, then nought point eight five. The project hadn't changed that much. The data had. In this lecture you'll learn why every controls metric depends on data from several systems, how a common code structure and calendar tie them together, the six dimensions of data quality and a monthly checklist to test them, and the governance basics every controls team needs. Then we'll look at why all of this is the single biggest prerequisite for using AI in project controls. By the end, you'll be able to map the data sources for any project and spot where the numbers are likely to go wrong.

0:56 Why it matters

Why does it matter? Because no single system gives you earned value. Planned value and progress come from the schedule. Actual costs, accruals and commitments come from the ERP or finance system. Hours come from timesheets. Risks come from the register. Every metric we've covered is a join across those sources. If one is late, miscoded or inconsistent, the metric is wrong, and people stop trusting the report. And there's a newer reason. AI models amplify whatever is in your data. Inconsistent codes, missing accruals and subjective progress don't just produce wrong reports. They produce confident, well-written, wrong predictions. So data foundations aren't IT housekeeping. They're controls.

1:43 The concept: the integration backbone

Three structures tie the data together. First, WBS and control account codes, used identically in the schedule, the cost system and the risk register. Second, the organisational breakdown structure, the OBS, showing who's responsible. Where the WBS and OBS intersect, you get control accounts, and that grid is your responsibility assignment matrix. Third, a common calendar and period definition, so every system closes on the same data date. Think of it like a shared language and a shared clock. If two systems speak different codes, you can't join them. If they close on different dates, you're comparing this month's costs with last week's progress. Then you need one place where everything joins by code and period: the controls data model. That can be a data warehouse, a database or, on a small project, a well-structured spreadsheet.

2:42 The six quality dimensions

Data quality has six dimensions, and each needs a concrete check. Completeness: is everything there? Every control account has planned value, earned value and actual cost this period. Accuracy: is it right? Sample actuals are traced to invoices, and progress to evidence. Timeliness: is it current? All sources closed at the same data date. Consistency: do the systems agree? The sum of control account budgets equals the budget at completion in both the schedule and the ERP. Validity: does it follow the rules? Codes exist in the WBS, and dates are in a logical order. And lineage: where did it come from? Every figure is traceable to a source record. Here's the key idea: each of these can be tested automatically, every month, before anyone builds a chart.

3:38 Worked example one: a code mismatch

A simple example. The schedule says total control account budgets add up to twelve million. The ERP says eleven point seven million. Which is right? Neither, yet. A quick join between the two code lists shows two ERP cost codes that don't exist in the WBS at all, holding three hundred thousand of budget between them. Someone created them for a subcontract package and never mapped them. So actual costs posted to those codes never reached any control account, which quietly flattered the CPI of the account doing the work. Map the codes, reconcile the totals and re-run earned value. That's a ten-minute check that can change your whole performance story.

4:26 Worked example two: a UAE EPC contractor

Now the realistic example from the lesson. A fictional EPC contractor in the UAE found its CPI swinging wildly from month to month. The investigation found three causes. Accruals weren't posted consistently, so some months carried two months of subcontractor cost and others none. Two cost codes existed in the ERP that weren't in the WBS. And the schedule closed on the twenty-fifth, while finance closed at month-end, so progress and cost were measured over different periods. The fixes were unglamorous: align the calendar, map the codes, introduce an accrual checklist. CPI became stable and credible. And here's the part I want you to remember. A forecasting model trained later on the cleaner data performed far better than an earlier pilot built on the messy data. Same algorithm. Better data.

5:23 Watch me do it: automated reconciliation

Let me show you the reconciliation I run before every report. In a notebook, I load the schedule export and the ERP export, both with a control account code and a period. I do an outer join on the code, with an indicator column. Anything that appears only in the ERP is an orphan cost code: money that never reaches a control account. Anything only in the schedule is an account with budget but no cost system home. Then two sum checks: total budget in each system, and total actual cost in the controls model against the finance ledger, with documented timing differences. The notebook prints pass or fail for each check. If anything fails, I fix the data before I build a single chart. The code is in the lesson text, and the same logic works in Power Query if you prefer Excel.

6:26 Governance and privacy

Some governance basics. Every data source needs a named owner, responsible for its quality. Commercial data, like rates, margins and claims, is sensitive, so restrict access appropriately. Keep period snapshots, so you can reconstruct exactly what the numbers were on any date. That's vital for claims, audits and for training any predictive model fairly. And remember that timesheets and personnel data are personal data. Follow the applicable data protection laws, for example the UK GDPR, the UAE's federal data protection law and Saudi Arabia's Personal Data Protection Law, as well as sector rules and your company's policy. The common mistakes: different coding in each system, monthly copy-paste with no checks, no snapshot history, and treating data quality as IT's problem instead of the controls team's.

7:20 Recap and try this now

Let's recap. Every controls metric joins data from several systems, so integration is everything: identical WBS codes, a clear OBS, a common calendar and one controls data model that joins by code and period. Test the six dimensions of quality, completeness, accuracy, timeliness, consistency, validity and lineage, automatically, every month, before you build charts. Put governance around it: named owners, access control, snapshots and lawful handling of personal data. And remember that data foundations are the single most important prerequisite for AI in project controls. Your try-this-now: map the data sources for a project you know. For each system, list its owner, what it provides, its close date and the code that links it to the WBS. Then circle the weakest link.