Data Warehousing for Construction: Modeled for Job Cost and Project Lifecycle

A GC modernizes its data warehouse and discovers the same problem every construction analytics program hits: the warehouse shows raw ERP transactions, but the controller's WIP schedule includes manual adjustments the warehouse doesn't have. Accrued subcontractor costs for work performed but not yet invoiced. Retainage payable and receivable positions. Revenue recognized under percentage-of-completion that depends on the cost-to-complete estimate the PM provides monthly. Each of these adjustments exists in the controller's spreadsheet but not in the ERP's transaction data. The warehouse is technically correct against the source system and functionally wrong against the financial truth.

Construction data warehousing done right builds the controller's adjustments into the model from day one. A job cost fact table that includes accrued costs alongside posted costs. Retainage as a separate dimension that tracks holdback, release, and the aging that determines cash flow impact. Change order pipeline with approved, pending, and rejected statuses that affect both revenue and cost projections. Cost-to-complete as a time-variant input that captures the PM's monthly estimate. With these in the model, the warehouse produces numbers the controller verifies and the PM trusts. Without them, it produces another system that doesn't match the WIP.