Back to work projects

Work project at Raytheon Technologies. Completed.

Compliance and audit dashboards

On the Mission Assurance Analytics & Tools team, I built the Tableau and Power BI dashboards that tracked regulatory compliance and audit items: how old the open ones were, which were past due, and which were at risk of slipping. Python scripts I wrote loaded the data from SharePoint lists and Jira into the team's database.

[#]
dashboards in Tableau and Power BI
3
sources feeding one database: SharePoint lists, Jira and a corrective action system
[#]
open compliance and audit items tracked
My role
Senior compliance data analyst and application administrator
Timeline
2021 – 2023
Tools
Tableau, Power BI, Python (pandas, SQLAlchemy), SQL Server, SharePoint, Jira

What the dashboards tracked

Mission Assurance follows compliance and audit items until they're closed: audit findings, corrective actions and reviews of the procedures that govern the work. The dashboards showed trends in three things: how long items had been open, which were past due, and which were close to their due date with no recent progress.

Getting the data into one place

The items lived in SharePoint lists, Jira and a corrective action system. I wrote Python scripts that pulled the SharePoint and Jira data into the team's SQL Server database, and I improved the data flows coming in from the corrective action system, so the dashboards could read everything from one place.

I also cleaned up what was already there. I tuned the stored procedures behind the dashboards and retired duplicate and outdated datasets, so each number had one source.

Sample views

Rebuilt with made-up data: an aging matrix, the past-due trend, the at-risk list, and the scripts that load it all.

What it answers: where is the old backlog? Switch between item types and organizations to see who's carrying it.

Open items by age
By item type, end of September
Open items by age and item type
Item type0–30 days31–6061–9091–180Over 180TotalOver 90 days
Audit findings422815949813%
Corrective actions654026181116018%
Procedure reviews302212637312%
Total1379053331833115%

Corrective actions have the oldest backlog: 18% of them are more than 90 days old, against 15% overall.

How it's built

A view in SQL Server works out each open item's age and bucket, so Tableau and Power BI both use the same cutoffs. The shading scales with the count, and the last column re-divides the two oldest buckets by the row total rather than averaging anything.

-- T-SQL (simplified): age and bucket for every open item
CREATE OR ALTER VIEW rpt.open_item_aging AS
SELECT item_id, item_type, org, due_on,
       a.age_days,
       CASE WHEN a.age_days <= 30  THEN '0-30'
            WHEN a.age_days <= 60  THEN '31-60'
            WHEN a.age_days <= 90  THEN '61-90'
            WHEN a.age_days <= 180 THEN '91-180'
            ELSE 'Over 180' END AS age_bucket
FROM dbo.compliance_items
CROSS APPLY (SELECT DATEDIFF(day, opened_on,
             CAST(GETDATE() AS date)) AS age_days) AS a
WHERE closed_on IS NULL;

Made-up sample data and simplified logic. Organization names and item IDs are generic. The real numbers belong to Raytheon.

The other half of the job

I was also the application administrator for Method Park Stages, the process management tool that held the organization's procedures. I supported its users and kept its documentation current across the development and production instances. Before each new release, I ran regression tests and worked through fixes with the vendor and our server, database and network teams, then let the procedure owners know about changes and maintenance windows.

Outside the dashboards, I used SAP BEx, Crystal Reports and Python to track the weekend automation of Quality Notification processing, and I tested Power BI Cloud Server and Snowflake early in their adoption.