SAP Plant Maintenance Analytics data model
Kimball warehouse for SAP PM: an accumulating maintenance-order snapshot with created/released/completed milestone date roles and planned vs actual cost and labor, a transaction-grain notification fact (QMEL) with breakdown duration, and a periodic equipment-downtime fact computing MTBF, MTTR, and availability. Order/notification numbers (aufnr/qmnum) are degenerate dimensions; equipment master is SCD2; SAP fiscal year/period drives period reporting.
SAP Plant Maintenance (PM) analytics migrated from ECC / S/4HANA into Snowflake. Covers the maintenance-order lifecycle (AUFK/AFIH, accumulating snapshot created → released → completed), maintenance notifications (QMEL), and an equipment-downtime reliability snapshot yielding MTBF/MTTR and availability, over conformed equipment (EQUI, SCD2), functional location (IFLOT), work center (CRHD), plant (T001W), and order/notification-type dimensions with SAP fiscal-period accounting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 120
- columns
- 16
- relationships
- 1
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Accumulating snapshot for the maintenance-order lifecycle
A PM order (AUFK/AFIH) passes through well-defined milestones (created → released → started → technically completed → closed); an accumulating snapshot with a date-role foreign key per milestone captures lead time, response time, and repair time in one row.
Separate notification transaction fact
QMEL notifications (malfunctions, requests) occur at higher volume and a different grain than orders; a transaction-grain notification fact with damage/cause codes and breakdown duration captures failure events independent of whether an order was raised.
Periodic reliability snapshot for MTBF/MTTR
Mean-time-between-failures and mean-time-to-repair are derived ratios over a period that are awkward to compute on raw events; a periodic snapshot at equipment/period grain pre-aggregates breakdown and repair counts to give stable reliability KPIs and availability.
SCD2 on the equipment master
EQUI attributes (functional-location installation, criticality ABC, responsible plant) change as assets are moved and reclassified; type-2 history preserves the as-was technical-object structure required for comparable reliability trend analysis.
Tables in the SAP Plant Maintenance Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_maintenance_order
Grain: one row per maintenance order (AUFK/AFIH, accumulating snapshot created→released→completed)
Accumulating snapshot tracking the PM order lifecycle; supplies MTTR repair time.
| Column | Type | Keys |
|---|---|---|
| maintenance_order_key | bigint | PK |
| equipment_key | bigint | |
| functional_location_key | bigint | |
| work_center_key | bigint | |
| plant_key | bigint | |
| order_type_key | bigint | |
| fiscal_period_key | bigint | |
| created_date_key | bigint | |
| released_date_key | bigint | |
| scheduled_start_date_key | bigint | |
| actual_start_date_key | bigint | |
| completed_date_key | bigint | |
| closed_date_key | bigint | |
| maintenance_order | varchar | |
| priority | varchar | |
| planner_group | varchar | |
| planned_labor_hours | decimal | |
| actual_labor_hours | decimal | |
| planned_cost | decimal | |
| actual_cost | decimal | |
| material_cost | decimal | |
| labor_cost | decimal | |
| lead_time_days | integer | |
| response_time_hours | decimal | |
| repair_time_hours | decimal | |
| order_status | varchar | |
| is_breakdown | boolean |
fct_notification
Grain: one row per maintenance notification (QMEL)
Transaction-grain notification fact — malfunction and request events.
| Column | Type | Keys |
|---|---|---|
| notification_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| equipment_key | bigint | |
| functional_location_key | bigint | |
| plant_key | bigint | |
| notification_type_key | bigint | |
| completion_date_key | bigint | |
| notification_number | varchar | |
| reference_order | varchar | |
| priority | varchar | |
| damage_code | varchar | |
| cause_code | varchar | |
| breakdown_indicator | boolean | |
| breakdown_duration_hours | decimal | |
| response_time_hours | decimal | |
| notification_count | integer |
fct_equipment_downtime
Grain: one row per equipment per fiscal period (downtime / reliability periodic snapshot)
Periodic snapshot of equipment reliability — MTBF/MTTR and availability.
| Column | Type | Keys |
|---|---|---|
| fiscal_period_key | bigint | |
| equipment_key | bigint | |
| functional_location_key | bigint | |
| plant_key | bigint | |
| operating_hours | decimal | |
| downtime_hours | decimal | |
| planned_downtime_hours | decimal | |
| breakdown_count | integer | |
| repair_count | integer | |
| total_repair_hours | decimal | |
| mtbf_hours | decimal | |
| mttr_hours | decimal | |
| availability_pct | decimal | |
| maintenance_cost | decimal |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_month | smallint | |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| is_business_day | boolean | |
| is_period_end | boolean |
dim_fiscal_period
Grain: one row per SAP fiscal year/period (gjahr/monat)
| Column | Type | Keys |
|---|---|---|
| fiscal_period_key | bigint | PK |
| fiscal_period_code | varchar | BK |
| fiscal_year | smallint | |
| fiscal_period | smallint | |
| fiscal_variant | varchar | |
| period_start_date | date | |
| period_end_date | date | |
| is_special_period | boolean | |
| period_status | varchar |
dim_equipment
Grain: one row per equipment per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| equipment_key | bigint | PK |
| equipment_number | varchar | BK |
| equipment_description | varchar | |
| equipment_category | varchar | |
| technical_object_type | varchar | |
| manufacturer | varchar | |
| model_number | varchar | |
| serial_number | varchar | |
| acquisition_date | date | |
| functional_location | varchar | |
| plant | varchar | |
| abc_indicator | varchar | |
| is_active | boolean |
dim_functional_location
Grain: one row per functional location (IFLOT)
| Column | Type | Keys |
|---|---|---|
| functional_location_key | bigint | PK |
| functional_location | varchar | BK |
| functional_location_label | varchar | |
| description | varchar | |
| location_category | varchar | |
| superior_functional_location | varchar | |
| plant | varchar | |
| cost_center | varchar |
dim_work_center
Grain: one row per work center (CRHD)
| Column | Type | Keys |
|---|---|---|
| work_center_key | bigint | PK |
| work_center | varchar | BK |
| work_center_name | varchar | |
| plant | varchar | |
| work_center_category | varchar | |
| responsible_person | varchar | PII |
| capacity_category | varchar |
dim_plant
Grain: one row per plant
| Column | Type | Keys |
|---|---|---|
| plant_key | bigint | PK |
| plant | varchar | BK |
| plant_name | varchar | |
| city | varchar | |
| country | varchar | |
| company_code | varchar |
Lookup tables (2)
dim_order_type
Grain: one row per SAP maintenance order type
| Column | Type | Keys |
|---|---|---|
| order_type_key | bigint | PK |
| order_type | varchar | BK |
| order_type_name | varchar | |
| maintenance_category | varchar | |
| is_planned | boolean |
dim_notification_type
Grain: one row per SAP PM notification type
| Column | Type | Keys |
|---|---|---|
| notification_type_key | bigint | PK |
| notification_type | varchar | BK |
| notification_type_name | varchar | |
| notification_category | varchar | |
| priority_default | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_maintenance_order.equipment_key → dim_equipment (many-to-one)
- fct_maintenance_order.functional_location_key → dim_functional_location (many-to-one)
- fct_maintenance_order.work_center_key → dim_work_center (many-to-one)
- fct_maintenance_order.plant_key → dim_plant (many-to-one)
- fct_maintenance_order.order_type_key → dim_order_type (many-to-one)
- fct_maintenance_order.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_notification.date_key → dim_date (many-to-one)
- fct_notification.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_notification.equipment_key → dim_equipment (many-to-one)
- fct_notification.functional_location_key → dim_functional_location (many-to-one)
- fct_notification.plant_key → dim_plant (many-to-one)
- fct_notification.notification_type_key → dim_notification_type (many-to-one)
- fct_equipment_downtime.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_equipment_downtime.equipment_key → dim_equipment (many-to-one)
- fct_equipment_downtime.functional_location_key → dim_functional_location (many-to-one)
- fct_equipment_downtime.plant_key → dim_plant (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.