Back to work projects

Work project at Collins Aerospace. In progress.

Control Tower modernization

The Control Tower is how Mission Systems leadership reviews quality across every site each month. Its numbers came from SharePoint lists, Power Apps, Excel trackers and values pulled from SAP by hand. I'm replacing all of that with an Azure SQL warehouse and an Ignition portal where sites submit their numbers.

37
active sites in 7 countries report each month
21
metric definitions: 17 entered monthly and 4 calculated
4+ → 1
platforms replaced by one Azure SQL warehouse
My role
Project lead
Timeline
2026 – Present
Tools
Azure SQL Database, T-SQL, Ignition, Power BI, GitHub

The old setup

Each month, site quality leads typed their numbers into a SharePoint list, many of them pulled from SAP by hand. Supplier numbers lived in separate Excel files, cost of sales came from finance reports, and yearly goals were rebuilt by hand in a workbook. The business-day-6 deadline ran on the honor system.

Power BI did all of the math at refresh time. Working out PPM and scrap, rework and repair as a share of cost of sales in Power Query took more than 15 minutes on every hourly refresh. When five business units merged into three, realigning the goals, sources and dashboards took an estimated 45 business days of manual work.

The new setup

BeforeSharePoint lists, Power Apps, Excel, manual SAP pullsPower BI, with every calculation in Power Query
AfterIgnition portalAzure SQL star schemaSQL views: PPM, SRR %, YTDPower BI

Every value lands in one fact table, keyed by metric, month and either a site or a business level, never both. Sites roll up to portfolios, business units and the SBU through dated memberships, so history survives the next reorganization. Calculated metrics such as customer PPM, supplier PPM and scrap, rework and repair as a percent of cost of sales move out of Power Query into SQL views with rolling 6-month, 12-month and year-to-date windows.

Submissions open on business day 1 and close at the end of business day 6. Then the month locks, and a late change needs a reason and an admin's approval. Every submission and edit is logged with its old and new value. The first release also moves Customer Satisfaction: monthly quality and delivery scores for key customers, with yearly goals and forecasts. I'm working toward the Ignition CORE Professional certification for this project.

Sample views

Four parts of the project, rebuilt with made-up data: the Control Tower itself, the form sites submit through, the admin's tracker, and the database underneath. The portal screens are designed but not built yet.

What it answers: which sites, portfolios or business units are off target on which metrics. Switch the level to see the same numbers rolled up.

Quality Control Tower
October 2026
On target
22 of 36
metric and site pairs
Off target
11
more than 25% over goal, or two over for counts
Customer PPM, all sites
704
rolling 6 months, from summed parts
Control Tower metrics by site, October 2026
SiteCustomer PPM
rolling 6 mo, goal 600
Supplier PPM
rolling 12 mo, goal 1,500
SRR % of COS
year to date, goal 1.8%
Customer escapes
YTD, goal 2 per site
Major complaints
YTD, goal 1 per site
Overdue CAPAs
month end, goal 2 per site
Site A408, on target571, on target1.1%, on target1, on target0, on target2, on target
Site B1,447, off target2,125, off target2.4%, off target3, watch1, on target5, off target
Site C161, on target360, on target0.7%, on target0, on target0, on target0, on target
Site D1,154, off target2,653, off target2.5%, off target2, on target1, on target3, watch
Site E323, on target656, on target1.2%, on target0, on target0, on target1, on target
Site F1,023, off target2,343, off target2.7%, off target2, on target2, watch6, off target
On targetWatchOff target (bold)

How it's built

Power BI reads finished SQL views instead of calculating at refresh. Rolled-up PPM is recomputed from summed defects and shipments at each level, never averaged across sites, and each goal is compared over its own window: a rolling 6-month goal against rolling 6-month PPM.

-- T-SQL (planned view): rolling 6-month customer PPM
-- Roll-ups re-sum the parts instead of averaging sites.
SELECT SiteKey, DateKey,
       1000000.0
       * SUM(Defects)   OVER (PARTITION BY SiteKey ORDER BY DateKey
                              ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
       / NULLIF(
         SUM(Shipments) OVER (PARTITION BY SiteKey ORDER BY DateKey
                              ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 0)
         AS CustomerPPM_R6M
FROM dbo.vw_CustomerMonthly;

Made-up sample data. Site, portfolio and business unit names are generic.

Where it stands

About five months went to cloud provisioning, security review and access approvals before the build could start. The database is live now.

  1. Provisioning and approvals
    Cloud database, security review and privileged access. About five months, mostly waiting.
    Done
  2. 1. Database foundation
    Schema, reference data and seven years of key customer data are loaded. Left: migrating past months and goals, the PPM, SRR and year-to-date views, and the Power BI connection.
    In progress
  3. 2. Ignition submission portal
    The monthly forms for site, supplier, enterprise and customer satisfaction data, with the late-edit path.
    Next
  4. 3. Admin tools, testing and cutover
    One pilot month run side by side with SharePoint, then Power BI switches over and the old sources go read-only for 90 days.
    Planned
  5. 4. Forecasting models
    Goals already store a Plan and a Forecast. Next is forecasting with Prophet and scikit-learn instead of manual multipliers.
    Planned