SAP Customer Service (CS) data model
Third-normal-form mirror of the SAP CS service-management tables as they land from the source ERP. Each SAP table becomes its own entity with a warehouse surrogate key plus the genuine SAP business key(s); technical objects model their hierarchies via self-referencing keys, and notification/order/operation/confirmation child tables reference their headers and master data via foreign keys. Customer name/city and the confirming employee number are PII-masked.
Normalized replica of SAP ECC/S4HANA Customer Service / service management source tables for a warehouse staging/replication layer: the installed base of functional locations (IFLOT/IFLOTX) and equipment (EQUI/EQKT) with measurement points (IMPTT); the service process of notification (QMEL) → service order (AUFK/AFIH) → operation (AFVC) → confirmation (AFRU); and service contracts (VBAK/VEDA) with items and equipment warranties — keyed by the real SAP business keys (tplnr, equnr, qmnum, aufnr, vbeln) and anchored to customer (KNA1), plant (T001W), work center (CRHD) and material (MARA) master tables. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 15
- tables
- 125
- columns
- 21
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Installed base modeled as functional location plus equipment with self-references
SAP CS represents the installed base as functional locations (IFLOT) and equipment (EQUI), each forming a hierarchy via a superior functional location and superordinate equipment; capturing those parent keys as self-references preserves the structural tree without re-keying every technical object.
Notification → order → operation → confirmation kept as separate entities
SAP CS drives service through a notification (QMEL) that spawns a service order (AUFK/AFIH) broken into operations (AFVC) that are time-confirmed (AFRU); replicating that chain 1:1 keeps the staging layer lossless and lets MTBF/MTTR and service-order cost reconcile against the source documents.
Service contracts split into header and item with SLA fields
SAP stores service contracts as a sales document (VBAK) with contract data (VEDA) and items covering specific equipment/material; modeling the header and item separately with SLA response/resolution fields supports entitlement checks, SLA compliance, and contract-renewal reporting.
Equipment-centric measurement points retained for condition data
SAP CS records condition and counter readings against measurement points (IMPTT) attached to a technical object; keeping measurement points as their own entity tied to equipment preserves the source granularity needed for condition monitoring and counter-based maintenance.
SAP business keys preserved in the replica layer
Because this is a replication/staging layer, retaining the real SAP business keys (tplnr, equnr, qmnum, aufnr, vbeln) alongside the warehouse surrogate keys keeps every row reconcilable against the source ERP and avoids ambiguity when cross-referencing back to SAP transactions.
Tables in the SAP Customer Service (CS) 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.
Tables (10)
functional_location
Grain: one row per functional location (tplnr)
SAP IFLOT/IFLOTX — functional location and its description.
| Column | Type | Keys |
|---|---|---|
| functional_location_key | bigint | PK |
| plant_key | bigint | |
| tplnr | varchar | BK |
| pltxt | varchar | |
| fltyp | varchar | |
| eqart | varchar | |
| abckz | varchar | |
| parent_funcloc_key | bigint | |
| installation_date | date |
equipment
Grain: one row per piece of equipment (equnr)
SAP EQUI/EQKT — equipment master and its description.
| Column | Type | Keys |
|---|---|---|
| equipment_key | bigint | PK |
| material_key | bigint | |
| functional_location_key | bigint | |
| customer_key | bigint | |
| equnr | varchar | BK |
| eqktx | varchar | |
| sernr | varchar | |
| eqtyp | varchar | |
| datab | date | |
| installation_date | date | |
| parent_equipment_key | bigint | |
| is_in_service | boolean |
measurement_point
Grain: one row per measurement point (point)
SAP IMPTT — measurement point on a technical object.
| Column | Type | Keys |
|---|---|---|
| measurement_point_key | bigint | PK |
| equipment_key | bigint | |
| point | varchar | BK |
| mptyp | varchar | |
| atnam | varchar | |
| indct | varchar | |
| decimal_places | integer | |
| measurement_unit | varchar |
warranty
Grain: one row per equipment warranty assignment
SAP equipment warranty (BGMK/warranty counter) — modeled per equipment.
| Column | Type | Keys |
|---|---|---|
| warranty_key | bigint | PK |
| equipment_key | bigint | |
| warranty_id | varchar | BK |
| warranty_type | varchar | |
| warranty_start | date | |
| warranty_end | date | |
| is_active | boolean |
service_notification
Grain: one row per service notification (qmnum)
SAP QMEL — service-relevant quality/service notification.
| Column | Type | Keys |
|---|---|---|
| service_notification_key | bigint | PK |
| equipment_key | bigint | |
| functional_location_key | bigint | |
| customer_key | bigint | |
| qmnum | varchar | BK |
| qmart | varchar | |
| qmtxt | varchar | |
| erdat | date | |
| priok | varchar | |
| problem_code | varchar | |
| required_start | date | |
| required_end | date | |
| notification_status | varchar |
service_order
Grain: one row per service order (aufnr)
SAP AUFK + AFIH — service order header and maintenance order header.
| Column | Type | Keys |
|---|---|---|
| service_order_key | bigint | PK |
| service_order_type_key | bigint | |
| equipment_key | bigint | |
| functional_location_key | bigint | |
| service_notification_key | bigint | |
| customer_key | bigint | |
| aufnr | varchar | BK |
| ktext | varchar | |
| gstrp | date | |
| gltrp | date | |
| planned_cost | decimal | |
| actual_cost | decimal | |
| currency | varchar | |
| order_status | varchar |
service_order_operation
Grain: one row per service order operation (aufpl, aplzl)
SAP AFVC — operations within the service order.
| Column | Type | Keys |
|---|---|---|
| service_order_operation_key | bigint | PK |
| service_order_key | bigint | |
| work_center_key | bigint | |
| aufpl | varchar | BK |
| aplzl | varchar | BK |
| vornr | varchar | |
| ltxa1 | varchar | |
| arbei | decimal | |
| planned_duration | decimal | |
| actual_duration | decimal |
service_confirmation
Grain: one row per order confirmation (rueck, rmzhl)
SAP AFRU — order completion confirmations.
| Column | Type | Keys |
|---|---|---|
| service_confirmation_key | bigint | PK |
| service_order_key | bigint | |
| rueck | varchar | BK |
| rmzhl | varchar | BK |
| operation_number | varchar | |
| actual_work | decimal | |
| confirmation_date | date | |
| employee_id | varchar | PII |
| is_final_confirmation | boolean |
service_contract
Grain: one row per service contract header (vbeln)
SAP VBAK + VEDA — service contract document and contract data.
| Column | Type | Keys |
|---|---|---|
| service_contract_key | bigint | PK |
| customer_key | bigint | |
| vbeln | varchar | BK |
| contract_type | varchar | |
| vbegdat | date | |
| venddat | date | |
| sla_response_time | decimal | |
| sla_resolution_time | decimal | |
| contract_value | decimal | |
| currency | varchar | |
| is_auto_renew | boolean | |
| contract_status | varchar |
service_contract_item
Grain: one row per service contract item (vbeln, posnr)
SAP VBAP + VEDA item — service contract item and contract data.
| Column | Type | Keys |
|---|---|---|
| service_contract_item_key | bigint | PK |
| service_contract_key | bigint | |
| equipment_key | bigint | |
| material_key | bigint | |
| vbeln | varchar | BK |
| posnr | varchar | BK |
| covered_service | varchar | |
| response_sla | decimal |
Lookup tables (5)
material
Grain: one row per material master general record
SAP MARA/MAKT — material master, referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| matnr | varchar | BK |
| maktx | varchar | |
| mtart | varchar | |
| matkl | varchar |
customer
Grain: one row per customer master general record
SAP KNA1 — customer master (general), referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| kunnr | varchar | BK |
| name1 | varchar | PII |
| land1 | varchar | |
| ort01 | varchar | PII |
plant
Grain: one row per plant / maintenance planning plant
SAP T001W — plant/branch check table.
| Column | Type | Keys |
|---|---|---|
| plant_key | bigint | PK |
| werks | varchar | BK |
| name1 | varchar | |
| land1 | varchar |
work_center
Grain: one row per work center
SAP CRHD — work center header, referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| work_center_key | bigint | PK |
| arbid | varchar | BK |
| arbpl | varchar | |
| ktext | varchar | |
| werks | varchar |
service_order_type
Grain: one row per service order type
SAP TCO* / T003O — order type customizing, referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| service_order_type_key | bigint | PK |
| auart | varchar | BK |
| description | varchar | |
| order_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- functional_location.plant_key → plant (many-to-one)
- equipment.material_key → material (many-to-one)
- equipment.functional_location_key → functional_location (many-to-one)
- equipment.customer_key → customer (many-to-one)
- measurement_point.equipment_key → equipment (many-to-one)
- warranty.equipment_key → equipment (many-to-one)
- service_notification.equipment_key → equipment (many-to-one)
- service_notification.functional_location_key → functional_location (many-to-one)
- service_notification.customer_key → customer (many-to-one)
- service_order.service_order_type_key → service_order_type (many-to-one)
- service_order.equipment_key → equipment (many-to-one)
- service_order.functional_location_key → functional_location (many-to-one)
- service_order.service_notification_key → service_notification (many-to-one)
- service_order.customer_key → customer (many-to-one)
- service_order_operation.service_order_key → service_order (many-to-one)
- service_order_operation.work_center_key → work_center (many-to-one)
- service_confirmation.service_order_key → service_order (many-to-one)
- service_contract.customer_key → customer (many-to-one)
- service_contract_item.service_contract_key → service_contract (many-to-one)
- service_contract_item.equipment_key → equipment (many-to-one)
- service_contract_item.material_key → material (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.