---
title: "Data foundations: integration, quality and tools"
description: "Controls runs on data Every metric in this course depends on data from several systems: the schedule tool, the ERP/finance system, procurement…"
url: https://optimizeall.com/learn/project-controls-with-ai/data-foundations
updated: 2026-10-05
---

Project Controls in the AI Era · Reporting, dashboards and data foundations · lesson 18 of 22 · 13 min

# Data foundations: integration, quality and tools

## Controls runs on data

Every metric in this course depends on data from several systems: the schedule tool, the ERP/finance system, procurement, timesheets, document control and the risk register. Integration and quality determine whether reports are trusted and whether AI can be used safely.

## The integration backbone

Three structures tie data together:

1. **WBS / control account codes** used identically across schedule, cost and risk.
2. **Organisational breakdown structure (OBS)** showing who is responsible; the intersection of WBS and OBS defines control accounts (the responsibility assignment matrix).
3. **Calendar and period definitions** so every system closes on the same data date.

## A typical data flow

```
Schedule tool  --> activities, dates, PV (time-phased budget), progress
ERP / finance  --> actual costs, accruals, commitments
Procurement    --> POs, delivery status
Timesheets     --> labour hours by code
Risk register  --> risks with WBS links and ranges
        \             |             /
         --> Controls data model (by control account and period) --> EVM, forecasts, dashboards
```

The **controls data model** is the single place where these join by code and period. It can be a database, a data warehouse or, for small projects, a well-structured spreadsheet.

## Data quality dimensions

| Dimension | Question | Example check |
|---|---|---|
| Completeness | Is everything there? | Every control account has PV, EV and AC this period |
| Accuracy | Is it right? | Sample actuals traced to invoices; progress to evidence |
| Timeliness | Is it current? | All sources closed at the same data date |
| Consistency | Do systems agree? | Sum of control account budgets = BAC in both schedule and ERP |
| Validity | Does it follow rules? | Codes exist in the WBS; dates in logical order |
| Lineage | Where did it come from? | Every figure traceable to a source record |

## Monthly data quality checklist

- Budgets in the schedule reconcile to the approved cost baseline.
- Actuals reconcile to the finance ledger (with documented timing differences).
- No costs posted to closed or invalid codes.
- Accruals posted for work done but not invoiced.
- Progress updates have evidence for milestone claims.
- Schedule has no open ends or out-of-sequence progress beyond tolerance.
- Change log approved changes reflected in the baseline.

## Tooling in 2026

Organisations use a mix of enterprise scheduling tools, ERP systems, dedicated project controls platforms, business-intelligence dashboards and, increasingly, AI assistants built into these tools or accessed through approved enterprise AI services. Tool choice matters less than: consistent coding, a defined data model, clear ownership of each data source, and automated reconciliation checks.

## Data governance basics

- **Data owners:** each source has a named owner responsible for its quality.
- **Access control:** commercial data (rates, margins, claims) is sensitive; restrict access appropriately.
- **Retention:** keep period snapshots so history can be reconstructed. This is vital for claims and audits.
- **Privacy:** timesheets and personnel data are personal data; follow applicable data protection laws (for example the UK GDPR, the UAE's federal data protection law, Saudi Arabia's PDPL and sector rules elsewhere) and company policy.

## Worked example

*Illustrative.* A fictional EPC contractor in the UAE found that its CPI was swinging wildly month to month. Investigation showed three causes: accruals were not posted consistently, two cost codes existed in the ERP that were not in the WBS, and the schedule closed on the 25th while finance closed at month-end. After aligning the calendar, mapping codes and introducing an accrual checklist, CPI became stable and credible, and later a forecasting model trained on this cleaner data performed far better than an earlier pilot.

## Common mistakes

- Different coding in each system.
- Manual copy-paste between systems every month without checks.
- No snapshot history.
- Treating data quality as IT's problem rather than the controls team's.

## Why this matters for AI

AI models amplify whatever is in the data. Inconsistent codes, missing accruals and subjective progress will produce confident but wrong predictions. Investing in data foundations is the single most important prerequisite for AI in project controls.

## Getting started on a small project

You do not need an enterprise platform to apply these principles. A small team can run a well-structured spreadsheet with one tab per source, a shared code list, a period column and a reconciliation tab that checks budget totals and actuals against finance. The discipline of consistent codes and dates matters far more than the tool, and it makes a later move to better systems much easier.

## Hands-on: an automated reconciliation in Python

```python
import pandas as pd

sched = pd.read_csv("p6_export.csv", dtype={"ca_code": str})   # ca_code, period, budget, PV, EV
erp = pd.read_csv("erp_export.csv", dtype={"ca_code": str})     # ca_code, period, budget, AC
ledger_total = 6_250_000                                        # from finance, same data date

codes = sched[["ca_code"]].drop_duplicates().merge(
    erp[["ca_code"]].drop_duplicates(), on="ca_code", how="outer", indicator=True)
orphans_erp = codes.loc[codes["_merge"] == "right_only", "ca_code"].tolist()
orphans_sched = codes.loc[codes["_merge"] == "left_only", "ca_code"].tolist()

latest = sched["period"].max()
checks = {
    "orphan ERP codes": not orphans_erp,
    "schedule codes without cost home": not orphans_sched,
    "budget totals agree": abs(sched.query("period == @latest")["budget"].sum()
                               - erp.query("period == @latest")["budget"].sum()) < 1,
    "AC reconciles to ledger": abs(erp.query("period == @latest")["AC"].sum() - ledger_total) < 1_000,
    "same data date": sched["period"].max() == erp["period"].max(),
}
for name, ok in checks.items():
    print(f"{'PASS' if ok else 'FAIL'}  {name}")
print("Orphan ERP codes:", orphans_erp)
```

Tolerances are illustrative; document timing differences (for example accruals posted after the ledger close) rather than widening tolerances. The same joins can be built in Power Query (Merge Queries → Full Outer) if your team works in Excel or Power BI.

## Monthly data quality scorecard

| Dimension | Check | Result | Owner |
|---|---|---|---|
| Completeness | Every CA has PV, EV, AC | | Controls |
| Accuracy | 10 actuals traced to invoices | | Cost controller |
| Timeliness | All sources closed at data date | | Each data owner |
| Consistency | Budgets = BAC in schedule and ERP | | Controls |
| Validity | No postings to closed or invalid codes | | Finance |
| Lineage | Snapshot saved with source file names | | Controls |

## How to measure success

- All reconciliation checks pass before report production starts.
- Number of orphan codes and manual adjustments falling month on month.
- A snapshot exists for every period, reproducible on request.

## Video lecture: Data foundations: integration, quality and tools

Lecture coming soon · 9 chapters · about 8 minutes. Read the full transcript below.

1. Controls runs on data
2. Why it matters
3. The concept: the integration backbone
4. The six quality dimensions
5. Worked example one: a code mismatch
6. Worked example two: a UAE EPC contractor
7. Watch me do it: automated reconciliation
8. Governance and privacy
9. Recap and try this now

## Lecture transcript

### 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.

### 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.

### 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.

### 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.

### 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.

### 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.

### 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.

### 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.

### 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.

## Key takeaways

- A shared WBS/control account code, OBS and common data date are the integration backbone.
- Assess data on completeness, accuracy, timeliness, consistency, validity and lineage.
- Run a monthly reconciliation checklist and keep period snapshots.
- Clean, governed data is the most important prerequisite for trustworthy AI in controls.

## Try it

Map the data sources for a project you know: list each system, its owner, what it provides, its close date and the code that links it to the WBS.

- [Previous: Reports and dashboards that drive decisions](https://optimizeall.com/learn/project-controls-with-ai/reports-that-drive-decisions)
- [Next: Performance analysis in practice: a full monthly cycle](https://optimizeall.com/learn/project-controls-with-ai/performance-analysis-in-practice)
- [All lessons of Project Controls in the AI Era](https://optimizeall.com/learn/project-controls-with-ai)