Architecture
Data Lineage - source to delivery
Sources
- ERP (ODBC connector)
- Payroll / HR system
- Historical Archive DB
- Plan & Forecast Files
Ingestion
- Scheduled pipelines
- Incremental extract by period
- Schema drift checks
Curated Warehouse
- Conformed dimensions
- Date · Subsidiary · Customer · Item
- LOB · Location · Department · Account
Semantic Model
- Certified measures
- TTM / prior-year time intelligence
- Row-level security
Delivery
- Power BI app
- Team chat channel
- Scheduled PDF / email
Last 24 hours
Refresh Status
| Source | Last refresh | Rows | Status |
|---|---|---|---|
| ERP | Mar 8, 2026 6:04 AM | 4,182,664 | Success |
| Payroll system | Mar 8, 2026 6:11 AM | 18,402 | Success |
| Historical Archive | Mar 8, 2026 2:30 AM | 2,914,308 | Success |
| Plan & Forecast Files | Feb 27, 2026 4:12 PM | 6,240 | Warning · manual upload pending |
Historical Data Bridge
Comparability across the ERP cutover
Pre-cutover subsidiary history is extracted from the legacy Northfield and Cascade systems, conformed to the current chart of accounts, customer master and subsidiary structure, and stored in a governed archive database. Every trailing-twelve and prior-year measure in this application reads across that archive and ERP as one continuous series, so the September conversion never breaks a comparison.
21 of the 44 requested KPIs depend on this archive - it gates more than half the wishlist and belongs in Phase 1.
28 certified measures
KPI Dictionary
| KPI | Definition | Source system | Slice dimensions | Owner |
|---|---|---|---|---|
| TTM Revenue | Sum of net sales across the trailing twelve accounting periods, all subsidiaries eliminated to group. | ERP + Historical Archive | Subsidiary · LOB · Location · Customer · Month | D. Stauffer (CFO) |
| TTM Adjusted EBITDA | Sum of P&L accounts less interest, taxes, depreciation and amortization, less management/BOD fees and non-recurring addbacks. | ERP + Historical Archive | Subsidiary · Month | D. Stauffer (CFO) |
| TTM Adj. EBITDA Margin | TTM Adjusted EBITDA ÷ TTM Net Sales. | ERP + Historical Archive | Subsidiary · Month | D. Stauffer (CFO) |
| MTD Revenue | Net sales posted to the current open accounting period. | ERP | Subsidiary · LOB · Location · Customer | Controller |
| MTD Adjusted EBITDA | Current-period operating income plus depreciation, amortization, interest and approved addbacks. | ERP | Subsidiary | Controller |
| Cash on Hand | Ending balance of all operating and depository cash accounts at period close. | ERP | Subsidiary · Bank account | Treasury |
| MTD / QTD / T3M Sales Orders | Value of sales orders entered in the period on an order-date basis (not ship date). | ERP (transaction where type = SalesOrd) | Subsidiary · LOB · Location · Customer | VP Sales |
| Orders to Date | Modeled definition - open plus shipped order value year to date by subsidiary and location. Definition pending CFO confirmation. | ERP | Subsidiary · Location | VP Sales |
| Total Backlog by Ship Month | Open sales order lines not yet fulfilled, bucketed by expected ship date. | ERP (open SalesOrd lines, expectedShipDate) | Ship month · Subsidiary · LOB | Operations |
| Revenue at Risk | Annual budget less the sum of recognized TTM revenue and open backlog. | ERP + Plan files | Subsidiary · LOB | FP&A |
| New Customers Gained (TTM) | Count of customers with a first invoiced transaction in the trailing twelve months and no order history prior. | ERP + Historical Archive | Subsidiary · Market segment · State | VP Sales |
| Customers Lost (TTM) | Customers with orders in the prior twelve months and none in the trailing twelve months. | ERP + Historical Archive | Subsidiary · Market segment | VP Sales |
| TTM New Customer Orders | Modeled definition - order value from customers with no orders in the prior twelve months. Definition pending CFO confirmation. | ERP + Historical Archive | Subsidiary · Segment | VP Sales |
| Gross Margin % | Gross margin dollars ÷ net sales, after indirect cost of sales and overhead absorption. | ERP | Subsidiary · LOB | Controller |
| DSO | Net accounts receivable ÷ (TTM revenue ÷ 365). | ERP + Historical Archive | Subsidiary · Customer | Controller |
| Days Inventory on Hand | Inventory value ÷ (TTM cost of goods sold ÷ 365). | ERP + Historical Archive | Subsidiary · Location · Item class | Operations |
| DPO | Accounts payable ÷ (TTM cost of goods sold ÷ 365). | ERP + Historical Archive | Subsidiary · Vendor | Controller |
| Fixed Charge Coverage Ratio | TTM Adjusted EBITDA less unfinanced capex and cash taxes ÷ fixed charges (interest plus scheduled principal). Threshold placeholder 1.20x. | ERP + Debt schedule | Group only | D. Stauffer (CFO) |
| Senior Leverage | Senior funded debt ÷ TTM Adjusted EBITDA. Threshold placeholder 3.00x. | ERP + Debt schedule | Group only | D. Stauffer (CFO) |
| Total Leverage | Total funded debt including subordinated notes ÷ TTM Adjusted EBITDA. Threshold placeholder 3.50x. | ERP + Debt schedule | Group only | D. Stauffer (CFO) |
| Total Headcount | Active employees at month end, excluding contractors. | Payroll system | Subsidiary · Department · Location | HR |
| TTM Turnover Rate | Average trailing-12 headcount loss ÷ average trailing-12 month-end headcount. | Payroll system | Subsidiary · Department | HR |
| TTM Revenue per Head | TTM revenue ÷ average trailing-12 month-end headcount. | ERP + Payroll system | Subsidiary | D. Stauffer (CFO) |
| TTM Adj. EBITDA per Head | TTM Adjusted EBITDA ÷ average trailing-12 month-end headcount. | ERP + Payroll system | Subsidiary | D. Stauffer (CFO) |
| T3M Tariffs Paid ÷ Revenue | Trailing three months tariff charges (gross of amounts absorbed) ÷ trailing three months revenue. | ERP (tariff accounts) | Subsidiary · Vendor country | Operations |
| TTM SG&A ÷ Revenue | Trailing twelve months selling, general and administrative expense ÷ TTM revenue. | ERP + Historical Archive | Subsidiary · Department | Controller |
| Free Cash Flow (TTM) | TTM cash from operations less capital expenditures, indirect method built in the semantic model. | ERP + Historical Archive | Group · Subsidiary | Treasury |
| Return on Invested Capital | TTM Adjusted EBITDA ÷ invested capital. Invested capital definition pending CFO confirmation. | ERP | Group only | D. Stauffer (CFO) |