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.
- 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.
| Item type | 0–30 days | 31–60 | 61–90 | 91–180 | Over 180 | Total | Over 90 days |
|---|---|---|---|---|---|---|---|
| Audit findings | 42 | 28 | 15 | 9 | 4 | 98 | 13% |
| Corrective actions | 65 | 40 | 26 | 18 | 11 | 160 | 18% |
| Procedure reviews | 30 | 22 | 12 | 6 | 3 | 73 | 12% |
| Total | 137 | 90 | 53 | 33 | 18 | 331 | 15% |
Corrective actions have the oldest backlog: 18% of them are more than 90 days old, against 15% overall.
| Organization | 0–30 days | 31–60 | 61–90 | 91–180 | Over 180 | Total | Over 90 days |
|---|---|---|---|---|---|---|---|
| Org A | 35 | 20 | 10 | 5 | 2 | 72 | 10% |
| Org B | 22 | 18 | 12 | 9 | 6 | 67 | 22% |
| Org C | 40 | 25 | 14 | 8 | 4 | 91 | 13% |
| Org D | 18 | 12 | 9 | 6 | 4 | 49 | 20% |
| Org E | 22 | 15 | 8 | 5 | 2 | 52 | 13% |
| Total | 137 | 90 | 53 | 33 | 18 | 331 | 15% |
Org B has the oldest backlog: 22% of its open items 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;What it answers: is the late backlog shrinking, and how much of each month's open workload is past due?
In this sample, past-due items peaked at 83 in December, 22% of open items. By September they were down to 46, or 14%. The open total fell from 371 to 331 over the same months, so nearly all of the drop was late items getting closed.
How it's built
Each bar comes from a month-end snapshot, so it shows what was open at the time, not what's still open today. Stacking past due inside the open total keeps both questions on one chart: the bar's height is the workload, and the blue part is how much of it is late.
-- T-SQL (simplified): month-end snapshot
INSERT INTO rpt.open_item_snapshot
(snapshot_month, item_id, item_type,
org, is_past_due)
SELECT EOMONTH(GETDATE()), item_id, item_type, org,
CASE WHEN due_on < CAST(GETDATE() AS date)
THEN 1 ELSE 0 END
FROM dbo.compliance_items
WHERE closed_on IS NULL;What it answers: which items are about to go past due? An item is at risk when it's due within 30 days and nobody has updated it in 14 days or more.
| Item | Type | Org | Due | Last update | Status |
|---|---|---|---|---|---|
| CA-1187 | Corrective action | Org B | in 6 days | 21 days ago | At risk |
| PR-0219 | Procedure review | Org A | in 9 days | 30 days ago | At risk |
| CA-1178 | Corrective action | Org E | in 14 days | 19 days ago | At risk |
| CA-1150 | Corrective action | Org B | in 18 days | 16 days ago | At risk |
| Item | Type | Org | Due | Last update | Status |
|---|---|---|---|---|---|
| PR-0224 | Procedure review | Org C | 11 days ago | 40 days ago | Past due |
| CA-1203 | Corrective action | Org D | 4 days ago | 15 days ago | Past due |
| Item | Type | Org | Due | Last update | Status |
|---|---|---|---|---|---|
| AF-0361 | Audit finding | Org B | in 3 days | 1 day ago | On track |
| CA-1187 | Corrective action | Org B | in 6 days | 21 days ago | At risk |
| PR-0219 | Procedure review | Org A | in 9 days | 30 days ago | At risk |
| AF-0342 | Audit finding | Org C | in 12 days | 3 days ago | On track |
| CA-1178 | Corrective action | Org E | in 14 days | 19 days ago | At risk |
| CA-1150 | Corrective action | Org B | in 18 days | 16 days ago | At risk |
| AF-0357 | Audit finding | Org E | in 25 days | 2 days ago | On track |
| CA-1211 | Corrective action | Org A | in 27 days | 9 days ago | On track |
4 of the 8 items due in the next 30 days haven't been updated in two weeks or more, and Org B owns 2 of them. Another 2 are already past due.
How it's built
The status is one calculated field, so the table, the counts and the filter buttons all use the same definition of at risk. Last-update dates older than two weeks are bolded so the reason an item is flagged is visible in the row itself.
// Tableau calculated field: Status
IF ISNULL([Closed On]) AND [Due On] < TODAY()
THEN "Past due"
ELSEIF ISNULL([Closed On])
AND DATEDIFF('day', TODAY(), [Due On]) <= 30
AND DATEDIFF('day', [Last Updated], TODAY()) >= 14
THEN "At risk"
ELSE "On track"
ENDWhat it answers: how does an item get from the system where someone logged it to the dashboard a leader is looking at?
Where the items were logged, each with its own fields and IDs.
Scripts page through each list and Jira project and write the rows to staging tables.
Stored procedures merge staging into one table of items with shared field names. Views add age, buckets and status.
Both read the same views, so a number means the same thing in either tool.
How it's built
Each script does the same few things: page through the source, keep only the columns the dashboards use, rename them to the shared names, write them to a staging table, then call a stored procedure that merges staging into the main table. Adding a source meant writing one more small script, not changing the dashboards.
# load_list.py (simplified): one SharePoint list
import pandas as pd
from sqlalchemy import create_engine, text
rows, url = [], f"{SITE}/_api/web/lists/getbytitle('{LIST}')/items"
while url:
page = session.get(url, headers=JSON_HEADERS).json()
rows += page["value"]
url = page.get("odata.nextLink")
df = pd.DataFrame(rows)[list(COLUMNS)].rename(columns=COLUMNS)
engine = create_engine(DB_URL)
with engine.begin() as con:
df.to_sql("stg_items", con, schema="stage",
if_exists="replace", index=False)
con.execute(text("EXEC dbo.usp_merge_items @source = 'sharepoint'"))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.