Back to work projects

Work project at Collins Aerospace. In progress.

SAP BW reports out of Excel

Several recurring quality reports ran as Excel workbooks on SAP BW queries: Customer PPM, DPPM and DPMO, Defects, Product Acceptance, Task Data and RCAT, which Task Data feeds. I automated their refreshes first, and now I'm rebuilding them on Snowflake and Power BI so they run without Excel.

2+ hrs
saved every run of these reports by automating the Excel refreshes first
3
report schedules: daily, weekly and monthly
7
reports being rebuilt in Snowflake
My role
Project lead
Timeline
2024 – Present
Tools
SAP BW, Analysis for Excel, Python, VBA and VBScript, Snowflake, Power BI dataflows, DAX

Step 1: automate the Excel refreshes

Before rebuilding anything, I wrote Python and VBScript to refresh the Analysis for Excel workbooks on a schedule. That alone saved the team more than two hours per run of the reports and ended the manual work at the start of each month.

Step 2: move the reports to Snowflake

The refresh scripts still run through Excel, and nobody could see what the BW queries underneath actually did. I'm rebuilding each report as queries against Snowflake and serving it through Power BI dataflows, the same way the quality dashboards now work. Once a report is rebuilt and checked against the old one, its workbook and refresh script can be retired.

First rebuild: Customer PPM

Customer PPM is escape returns per million units shipped, on a rolling 6-month basis, and it feeds the Control Tower. The old workbook only had the sold view, and sites typed its results into the Control Tower by hand. The rebuild reads both SAP systems, counts units shipped from SAP's delivery tables less rework lines going back to the customer, and traces every customer return to the shipment it came from through serial number history.

It adds a switch between the sold view (the profit center on the sales order) and the built view (the profit center on the delivery), and a tie-out page laid out in a way the Quality leads expect compared to the old report, so the numbers copy straight across. Delivery quantities match the old report exactly. Every other difference is written up as a documented variance, with current SAP data treated as correct.

Sample views

Customer PPM rebuilt with made-up data, the query that traces returns, and what changed in the work behind each run.

What it answers: each site's month, rolling 6-month and year-to-date PPM in one table that copies into the Control Tower. Switch the basis to see how the numbers move.

Customer PPM, site summary
October 2026, sold basis
Customer PPM by site, sold basis
SiteUnits shippedEscape returnsPPM, monthPPM, rolling 6 moPPM, YTD
Site A1,2101826427339
Site B64000513462
Site C2,0502976413345
Site D31013,2261,081645
Site E88000192115
Site F1,4601685449411
Total6,5505763436354

Site B ships some units under other sites' sales orders, so its rolling PPM is 513 on the sold basis and 698 on the built basis. Leadership reports on sold; the built view shows where the product was made.

How it's built

Deliveries carry two profit centers, one from the sales order and one from the delivery, and the model keeps both relationships. A small basis table drives which one the measures use, so one slicer flips every number on the page. The total row re-divides summed returns by summed shipments instead of averaging the sites.

-- DAX: the built view uses the delivery's profit center
Delivery Qty Built =
CALCULATE (
    SUM ( Deliveries[Delivery Qty] ),
    USERELATIONSHIP (
        'Profit Centers'[Key Profit Center],
        Deliveries[Key Profit Center Built]
    )
)

Return PPM 6M =
DIVIDE ( [Return Count 6M], [Delivery Qty 6M] ) * 1000000

Made-up sample data. Site names are generic. The real numbers belong to Collins Aerospace.