Case Study · Data & Analytics

PMO Performance & EVM Architecture

A governed, reusable reference architecture for portfolio performance intelligence — spanning BQE Core, Smartsheet, and Power BI.

Power BI Smartsheet BQE Core DAX / Power Query EVM Analytics PMO Operations
📊
My Role

Lead Architect & Implementation Owner

I led the full design and implementation of this PMO analytics system — from architecting the data flow across BQE Core, Smartsheet, and Power BI, to building the EVM calculation layer, designing the governance model, and delivering the executive dashboards. Requirements were gathered directly from project managers and finance stakeholders, and all technical and structural decisions were made and executed independently. The solution reduced project expense by 7% and is actively used across a 30-project portfolio.

System Architecture EVM Metric Design Smartsheet Build Power BI Dashboard Governance Framework Stakeholder Requirements DAX / Power Query
01 — Overview

System at a Glance

🏦
Financial Source
BQE Core
Actuals / Spend (AC)
📋
System of Record
Smartsheet
PM-governed plan + progress
📊
Analytics Engine
Power BI (DAX)
CPI / SPI / EAC / ETC
🔐
Governance
Update Requests
Scheduled · Restricted fields
02 — Architecture Doc

Reference Architecture

Purpose

A reusable reference architecture for a governed PMO performance system that:

  • Ingests financial actuals/spend from BQE Core (Actual Cost inputs)
  • Collects PM-owned plan dates and execution progress through governed Smartsheet Update Requests
  • Calculates EVM-style metrics in Smartsheet and Power BI (Power Query + DAX)
  • Delivers operational Smartsheet dashboards and executive Power BI dashboards

Logical Layers

LayerPurposeComponents
Financial SourceProvide actuals/spend and financial truth (AC)BQE Core: actual cost/spend; financial attributes
Integration / SyncLoad and refresh actuals into Smartsheet keyed by Project IDAPI/ODBC/export+load; scheduled sync; idempotent updates
System of RecordCentral PMO tracker with governed plan/progress fieldsSmartsheet master tracker; reference/lookup sheets
Governed InputsCollect PM-owned dates and progress with accountabilityUpdate Requests; restricted fields; cadence rules; auditability
EVM CalculationCompute PV/EV/AC and derived KPIsSmartsheet formulas; Power Query (M); DAX measures
Visibility LayerOperational + executive views and exception managementSmartsheet reports/dashboards; Power BI dashboards

Data Model — Smartsheet PMO Tracker

  • Primary key: Project ID (joins BQE Core actuals to Smartsheet plan/progress)
  • Financial fields (from BQE Core): actual cost/spend and related financial metrics
  • PM-governed plan fields: Project Start Date and Project End Date via Update Requests
  • Execution fields: phase completion updates and deployment progress
  • Traceability: last updated date, owner fields, notes/evidence links

Governance & Update Workflows

  • Controlled input: PMs provide updates through Smartsheet Update Requests, not broad sheet edit access
  • Cadence rules: weekly/bi-weekly or stage-based scheduling to maintain data freshness
  • Field-level governance: plan fields (start/end dates) are restricted as baseline-defining inputs
  • Quality controls: required fields, standardized definitions, and exception reporting for missing updates

EVM Calculation Approach

  • Inputs: PV from plan timelines · EV from phase completion · AC from BQE Core
  • Core KPIs: CPI · SPI · CV/SV (cost/schedule variance)
  • Forecasting: EAC and ETC measures in Power BI for portfolio trend analysis
  • Implementation: Smartsheet formulas · Power Query (M) · DAX measures

Dashboards & Reporting

  • Smartsheet operational dashboards: action queues, phase/deployment rollups, exception views
  • Power BI executive dashboards: CPI/SPI trend lines, EAC/ETC forecasting, variance analysis, portfolio comparisons
03 — Full Document

PDF Reference

PMO_EVM_Architecture_Documentation.pdf
04 — Visual Artifacts

Dashboards & Architecture Diagrams

PMO Executive Portfolio Dashboard
PMO_EVM_.jpg — Executive Portfolio Dashboard
PMO EVM Smartsheet Master Tracker
Master_Sheet.jpg — Smartsheet Master Tracker
PMO Performance EVM Architecture Workflow
PMO_EVM_Workflow_Diagram.png — Workflow Diagram
05 — Flow Diagram

Live Architecture Diagram

flowchart TD A[BQE Core\nActuals/Spend AC] --> B[Integration / Sync\nload + refresh] B --> C[Smartsheet PMO Tracker\nSystem of Record] D[PMs / Owners] -->|Update Requests\nStart/End Dates governed| C D -->|Update Requests\nPhase + Deployment progress| C C --> E[Smartsheet Formula Layer\nOperational KPIs] C --> F[Power Query M\nData shaping + joins] F --> G[DAX Measures\nEVM KPIs CPI/SPI/EAC/ETC] G --> H[Power BI Dashboards\nExecutive / Portfolio Views] C --> I[Smartsheet Reports\nAction queues + exceptions] I --> J[Smartsheet Dashboards\nOperational visibility]
06 — Implementation

Implementation Checklist