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.
- 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.
| Site | Units shipped | Escape returns | PPM, month | PPM, rolling 6 mo | PPM, YTD |
|---|---|---|---|---|---|
| Site A | 1,210 | 1 | 826 | 427 | 339 |
| Site B | 640 | 0 | 0 | 513 | 462 |
| Site C | 2,050 | 2 | 976 | 413 | 345 |
| Site D | 310 | 1 | 3,226 | 1,081 | 645 |
| Site E | 880 | 0 | 0 | 192 | 115 |
| Site F | 1,460 | 1 | 685 | 449 | 411 |
| Total | 6,550 | 5 | 763 | 436 | 354 |
| Site | Units shipped | Escape returns | PPM, month | PPM, rolling 6 mo | PPM, YTD |
|---|---|---|---|---|---|
| Site A | 1,180 | 1 | 847 | 435 | 348 |
| Site B | 720 | 1 | 1,389 | 698 | 556 |
| Site C | 1,980 | 2 | 1,010 | 424 | 354 |
| Site D | 310 | 1 | 3,226 | 1,081 | 645 |
| Site E | 910 | 0 | 0 | 185 | 111 |
| Site F | 1,390 | 0 | 0 | 353 | 357 |
| Total | 6,490 | 5 | 770 | 439 | 356 |
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] ) * 1000000What it answers: is customer PPM getting better, once you look past one-month noise?
Single-month PPM swings between 132 and 916, because one return moves it by about 150 at this volume. The rolling 6-month line went from 576 to 251 before October's six returns pushed it back up to 348.
How it's built
The rolling measures sum six months of returns and six months of shipments before dividing, and they go blank after the last month with shipments instead of trailing off to zero. The trend ignores the page's month slicer so it always shows the full 24 months.
-- DAX: sum six months, then divide
Return Count 6M =
VAR MonthStart = MIN ( 'Calendar'[Date] )
VAR EndDate = MAX ( 'Calendar'[Date] )
RETURN
IF (
MonthStart <= EOMONTH ( [Date Last Data Month], 0 ),
CALCULATE (
[Return Count],
DATESINPERIOD ( 'Calendar'[Date], EndDate, -6, MONTH )
)
)What it answers: which shipment did each customer return come from? That date decides whether the return counts, and which site and profit center it belongs to.
A return notification with a defect return code, the material and its serial number.
Every document that serial number appeared on, including the delivery that shipped it.
The goods issue date, the quantity and the profit centers for the sold and built views.
Return age from the ship date. Returns within a year of shipping count toward PPM.
How it's built
The return's serial number leads through SAP's serial history to the delivery that shipped it. Returns with no shipment found still count toward the totals, and a data info page shows how many there are so the gap stays visible. The old BW query only covered one SAP system; this one reads both.
-- Snowflake SQL (simplified): each return to its shipment
SELECT q.qmnum AS return_notification,
q.matnr AS material,
o.sernr AS serial_number,
k.wadat_ist AS date_shipped,
DATEDIFF('day', k.wadat_ist, q.qmdat) AS return_age_days
FROM qmel AS q
JOIN objk AS o ON o.matnr = q.matnr
AND o.sernr = q.serialnr
JOIN ser01 AS s ON s.obknr = o.obknr
JOIN likp AS k ON k.vbeln = s.lief_nr
WHERE q.qmart = :customer_return_type;What it answers: how much hands-on time one report run takes, before automation, after step 1, and after step 2.
| Step in one run | Manual | Step 1 | Step 2 |
|---|---|---|---|
| Open workbooks and log in to BW | 10 min | Automated | Automated |
| Refresh BW queries | 50 min | Automated | Automated |
| Wait on formulas and pivots | 25 min | Automated | Automated |
| Copy into the report template | 25 min | Automated | Automated |
| Check totals | 15 min | 10 min | 5 min |
| Save and send | 10 min | 5 min | Automated |
| Total hands-on time | 2 hr 15 min | 15 min | 5 min |
The 2+ hours saved per run is real. How the time splits across the steps is an illustration.
How it's built
Step 1 is a Python scheduler that calls a VBScript for each workbook. The script opens Excel, logs in to BW through the Analysis for Excel API, refreshes every query and saves. Step 2 removes Excel entirely: Snowflake holds the logic and Power BI refreshes the dataflow on a schedule.
' refresh_bw.vbs (simplified): called by Python per workbook
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(WScript.Arguments(0))
xl.Run "SAPLogon", "BW_PROD", "100"
xl.Run "SAPExecuteCommand", "Refresh", "ALL"
wb.Save
wb.Close
xl.QuitMade-up sample data. Site names are generic. The real numbers belong to Collins Aerospace.