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.
- 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
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.
| Site | Customer 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 A | 408, on target | 571, on target | 1.1%, on target | 1, on target | 0, on target | 2, on target |
| Site B | 1,447, off target | 2,125, off target | 2.4%, off target | 3, watch | 1, on target | 5, off target |
| Site C | 161, on target | 360, on target | 0.7%, on target | 0, on target | 0, on target | 0, on target |
| Site D | 1,154, off target | 2,653, off target | 2.5%, off target | 2, on target | 1, on target | 3, watch |
| Site E | 323, on target | 656, on target | 1.2%, on target | 0, on target | 0, on target | 1, on target |
| Site F | 1,023, off target | 2,343, off target | 2.7%, off target | 2, on target | 2, watch | 6, off target |
| Portfolio | Customer 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 |
|---|---|---|---|---|---|---|
| Portfolio 1 | 862, off target | 1,243, on target | 1.6%, on target | 4, on target | 1, on target | 7, off target |
| Portfolio 2 | 455, on target | 1,006, on target | 1.3%, on target | 2, on target | 1, on target | 3, on target |
| Portfolio 3 | 840, off target | 1,907, off target | 2.3%, off target | 2, on target | 2, on target | 7, off target |
| Business unit | Customer 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 |
|---|---|---|---|---|---|---|
| Business unit 1 | 657, watch | 1,128, on target | 1.5%, on target | 6, on target | 2, on target | 10, off target |
| Business unit 2 | 840, off target | 1,907, off target | 2.3%, off target | 2, on target | 2, on target | 7, off target |
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;What it answers: has this site entered every metric it owns this month? Switch to after the deadline to see how a late change works.
How it's built
An Ignition Perspective screen that shows each person only the sites and metrics assigned to them, with pick lists bound to the dimension tables so typing can't invent a site or metric. Values write to the fact table. After business day 6 a scheduled job locks the month, and late changes go to a pending table until an admin approves them.
-- T-SQL (planned job): lock a month after business day 6
UPDATE f
SET f.IsLocked = 1,
f.LockDate = SYSDATETIME()
FROM dbo.Fact_QualityMetrics AS f
JOIN dbo.Dim_Calendar AS c ON c.DateKey = f.DateKey
WHERE f.IsLocked = 0
AND CAST(SYSDATETIME() AS date) > c.BD6_Date;What it answers: who still owes numbers this month, and what has gone overdue. Step through the window to see it fill in.
| Site | Scrap | Rework | Repair | COS | Shipments | Defective pcs |
|---|---|---|---|---|---|---|
| Site A | Done | Done | Done | Done | Pending | Done |
| Site B | Pending | Pending | Pending | Pending | Pending | Pending |
| Site C | Done | Done | Done | Done | Done | Done |
| Site D | Pending | Pending | Pending | Pending | Pending | Pending |
| Site E | Done | Done | Pending | Pending | Pending | Pending |
| Site F | Pending | Pending | Pending | Pending | Pending | Pending |
The window opened yesterday with a reminder to everyone who owes a value.
| Site | Scrap | Rework | Repair | COS | Shipments | Defective pcs |
|---|---|---|---|---|---|---|
| Site A | Done | Done | Done | Done | Done | Done |
| Site B | Done | Done | Done | Done | Pending | Pending |
| Site C | Done | Done | Done | Done | Done | Done |
| Site D | Done | Pending | Pending | Pending | Pending | Done |
| Site E | Done | Done | Done | Done | Done | Done |
| Site F | Pending | Pending | Pending | Pending | Pending | Done |
Incomplete items have had a daily reminder since business day 3, and metric owners are copied from today.
| Site | Scrap | Rework | Repair | COS | Shipments | Defective pcs |
|---|---|---|---|---|---|---|
| Site A | Done | Done | Done | Done | Done | Done |
| Site B | Done | Done | Done | Done | Done | Overdue |
| Site C | Done | Done | Done | Done | Done | Done |
| Site D | Done | Done | Done | Done | Overdue | Done |
| Site E | Done | Done | Done | Done | Done | Done |
| Site F | Done | Done | Overdue | Overdue | Overdue | Done |
The window has closed and October is locked. Overdue alerts go out daily, and any change now needs an approved late edit.
How it's built
Each metric has an owner, a provider and a backup at a site, portfolio or business level, and assignment resolves from the most specific scope to the least. The tracker joins those assignments to the month's submissions. Reminders go out on business day 1 and daily to incomplete items from day 3, with owners copied from day 5.
-- T-SQL (planned): who still owes this month's values
SELECT s.SiteName, m.MetricName,
CASE WHEN f.Metric_Value IS NOT NULL THEN 'Submitted'
WHEN CAST(SYSDATETIME() AS date) > c.BD6_Date
THEN 'Overdue'
ELSE 'Pending' END AS SubmissionStatus
FROM dbo.Bridge_MetricAssignment AS a
JOIN dbo.Dim_Site AS s ON s.SiteKey = a.SiteKey
JOIN dbo.Dim_Metric AS m ON m.MetricKey = a.MetricKey
JOIN dbo.Dim_Calendar AS c ON c.DateKey = @MonthKey
LEFT JOIN dbo.Fact_QualityMetrics AS f
ON f.SiteKey = a.SiteKey
AND f.MetricKey = a.MetricKey
AND f.DateKey = @MonthKey;What it answers: where every number lives. The star schema has 18 tables: 6 facts, 8 dimensions and 4 bridges. Reference and customer data are loaded; the fact tables wait on the migration of past months.
- Fact_QualityMetricsWaiting on migrationOne metric value for one site or business level and month
- Fact_SupplierMetricsWaiting on migrationOne site's supplier escapes, defects and shipments for a month
- Fact_CustomerSatisfactionWaiting on migrationMonthly quality and delivery scores per key customer
- Fact_CustomerForecastWaiting on migrationForecast scores for future months, with the method used
- Fact_QualityGoalsWaiting on migrationTargets stored as a frozen Plan and a rolling Forecast
- Fact_ChangeLogWaiting on migrationEvery audited change, with old and new values and a reason
- Dim_CalendarLoadedOne day, with each month's business-day-6 deadline
- Dim_HolidayLoadedCompany holidays for the business-day count
- Dim_SiteLoaded39 sites, with cost-of-sales type and reporting flags
- Dim_OrgLoadedPortfolios, business units and the SBU, dated
- Dim_MetricLoaded21 metric definitions, with polarity and window
- Dim_BusinessLevelLoadedSite, Portfolio, BU or SBU
- Dim_CustomerLoadedKey customers
- Dim_ContactLoadedPeople who own or provide data
- Bridge_SiteOrgLoadedA site's portfolio membership for a date range
- Bridge_CustomerAssignmentLoadedOne customer at one site for one year, with goals
- Bridge_CustomerContactLoadedRoles people hold on a customer assignment
- Bridge_MetricAssignmentWaiting on migrationOwner, provider and backup by metric and scope
How it's used
Constraints carry the rules: foreign keys on every link, one scope per value, and fixed lists for cost-of-sales type, business level and goal method. Five stored procedures rebuild, reset and reload the schema. Every load runs in one transaction and rolls back on any error, and the reset refuses to run once a fact table holds data.
-- T-SQL: a value belongs to a site or a business level,
-- never both and never neither
ALTER TABLE dbo.Fact_QualityMetrics
ADD CONSTRAINT CK_QualityMetrics_OneScope CHECK (
(SiteKey IS NOT NULL AND LevelKey IS NULL)
OR (SiteKey IS NULL AND LevelKey IS NOT NULL)
);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.
- Provisioning and approvalsCloud database, security review and privileged access. About five months, mostly waiting.Done
- 1. Database foundationSchema, 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
- 2. Ignition submission portalThe monthly forms for site, supplier, enterprise and customer satisfaction data, with the late-edit path.Next
- 3. Admin tools, testing and cutoverOne pilot month run side by side with SharePoint, then Power BI switches over and the old sources go read-only for 90 days.Planned
- 4. Forecasting modelsGoals already store a Plan and a Forecast. Next is forecasting with Prophet and scikit-learn instead of manual multipliers.Planned