Data model
One structure. One canonical model. Four ERPs.
The landscape dashboard reads the SAP enterprise structure from S/4HANA and ECC R/3. The maintenance dashboards never read SAP AUFK, Oracle WIP_ENTITIES or Maximo WORKORDER directly. Adapters map each source into the same canonical entities, and the reporting layer reads those entities or the pre-aggregated KPI snapshots.
Canonical entities
11 core entitiesSwipe to see all columns →
| Entity | Purpose | Key fields |
|---|---|---|
| Functional location | Where the work happens | code, parent, plant, cost centre, criticality |
| Equipment | The asset | asset tag, manufacturer, serial, linear/measure, replacement value |
| Maintenance plan | Preventive schedule | plan code, cycle, next due, task list |
| Notification | Request or breakdown | number, equipment, priority, malfunction start/end, state |
| Work order | Execution header | number, order type, dates, actual cost, state |
| Operation | Individual step | operation number, work centre, planned/actual hours |
| Material component | Spares reserved or consumed | material, quantity, unit cost |
| Time confirmation | Labour | technician, hours, work date, final flag |
| Asset meter / reading | Usage and condition | meter type, reading, unit, reading date |
| Failure report | Reliability codes | problem, cause, remedy, failure mode, downtime |
| KPI snapshot | Pre-aggregated executive data | period, cost, RAV, MTBF, MTTR, PM compliance |
SAP enterprise structure
Before a work order is counted, the landscape dashboard resolves the company code, plant and business area behind it. The same eight structure objects are read from S/4HANA and ECC R/3, with separate configurations for each system.
Swipe to see all columns →
| Object | Table | Key | Purpose |
|---|---|---|---|
| Company | T880 | RCOMP | Consolidation unit above the company codes |
| Company code | T001 | BUKRS | Legal entity, currency, chart of accounts and fiscal year |
| Plant | T001W | WERKS | Operating site, address, business area and maintenance ownership |
| Business area | TGSB | GSBER | Cross-company management segment used in financial postings |
| Controlling area | TKA01 | KOKRS | Cost accounting scope and controlling currency |
| Sales organization | TVKO | VKORG | Sales channel ownership and pricing responsibility |
| Purchasing organization | T024E | EKORG | Procurement ownership and purchasing conditions |
| Storage location | T001L | LGORT | Material storage within a plant |
Reporting star
The analytical layer is a star schema. Facts carry the grain; dimensions carry the drill paths. Power BI or the Django workspace can read the same gold tables.
Facts
- FactWorkOrder
- FactNotification
- FactPMCompliance
- FactDowntime
- FactCost
- FactRCA
Dimensions
- DimDate
- DimPlant
- DimFunctionalLocation
- DimEquipment
- DimWorkCentre
- DimStatus
- DimCatalog
Medallion flow
Raw ERP payloads are kept for replay. The silver layer conforms types, units and status maps. The gold layer is the signed-off KPI mart.
Bronze
Raw OData/REST payloads, append-only, source hash and cursor.
Silver
Canonical entities, deduplicated by external id, status mapped to core state.
Gold
Star schema, KPI snapshots, RLS-ready plant and org-unit columns.
Field ownership
Bidirectional integration only works when ownership is explicit. The connector never overwrites a field the CMMS owns.
ERP owns
- Master data: functional locations, equipment, materials
- Status, costs and confirmations
- Maintenance plans and task lists
CMMS owns
- Permit to work, isolation and LOTO
- Checklists, RCA, CAPA and QA findings
- Local notes, photos and shift handover