SAP EWM Warehouse Operations Analytics data model
Kimball warehouse for SAP EWM operations: a task-confirmation-grain fact (/SCWM/ORDIM_C) carrying task quantity, duration, travel distance, and pick/putaway/replenishment/exception flags for throughput, dock-to-stock, and labor-productivity analysis, plus a periodic inventory-position snapshot (/SCWM/AQUA + /SCWM/QUAN) at product × bin × stock-type grain for bin/stock utilization and inventory-accuracy reporting. The storage-bin dimension (/SCWM/LAGP) is role-played as source bin, destination bin, and the snapshot bin; product is SCD2; warehouse tasks (tanum), warehouse orders (who), and handling units (huident) are degenerate dimensions.
SAP Extended Warehouse Management (EWM) operations analytics migrated from SAP EWM / S/4HANA Embedded EWM into Snowflake. Models a confirmed warehouse-task fact at task-confirmation grain (/SCWM/ORDIM_C, with open tasks in /SCWM/ORDIM_O, grouped into warehouse orders /SCWM/WHO and handling units /SCWM/HUHDR) and a periodic inventory-position snapshot at product × bin × stock-type grain (/SCWM/AQUA available stock + /SCWM/QUAN quant) over conformed storage-bin (/SCWM/LAGP), product (SCD2), warehouse (lgnum), process-type, activity-area (aaarea), resource, stock-type (/SCWM/STOCK_IW01-04), and owner-party dimensions. 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
- 102
- columns
- 15
- relationships
- 2
- 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
Task-confirmation-grain warehouse-task fact
SAP EWM records each confirmed movement as a warehouse task in /SCWM/ORDIM_C (open tasks in /SCWM/ORDIM_O); declaring the fact at the one-row-per-confirmed-task grain preserves duration, travel distance, and quantity so pick/putaway productivity, throughput, and dock-to-stock cycle time aggregate to any resource, process type, or activity area.
Periodic inventory-position snapshot fact
Bin and stock utilization and inventory accuracy need a level (not additive-over-time) measure of on-hand, available, blocked, and quality-inspection stock; a periodic snapshot at product × bin × stock-type grain sourced from /SCWM/AQUA available stock and /SCWM/QUAN quant is the canonical Kimball pattern for inventory position.
Storage-bin dimension role-played for source/destination/snapshot
A warehouse task moves stock between a source bin (vlpla) and a destination bin (nlpla), and the inventory snapshot sits at one bin; modelling /SCWM/LAGP once and role-playing it via three foreign keys (source_bin_key, dest_bin_key, storage_bin_key) keeps one conformed bin dimension while supporting movement and position analysis.
SCD2 on product
EWM product master attributes (product group, storage condition, hazardous and batch-management flags) change over time; type-2 history preserves the as-was product attributes required for comparable utilization and throughput trend reporting.
SAP EWM business keys as dimension business keys
Warehouse number (lgnum), storage bin (lgpla), warehouse task (tanum), and activity area (aaarea) are the natural keys operators reconcile against the source system; surfacing them as business keys (with tanum/who/huident as degenerate dimensions on the fact) keeps the mart traceable back to SAP EWM.
Tables in the SAP EWM Warehouse Operations 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 (2)
fct_warehouse_task
Grain: one row per confirmed warehouse task (SAP /SCWM/ORDIM_C)
Task-confirmation-grain fact for throughput, dock-to-stock, and pick/putaway productivity.
| Column | Type | Keys |
|---|---|---|
| warehouse_task_key | bigint | PK |
| creation_date_key | bigint | |
| confirmation_date_key | bigint | |
| product_key | bigint | |
| warehouse_key | bigint | |
| source_bin_key | bigint | |
| dest_bin_key | bigint | |
| process_type_key | bigint | |
| activity_area_key | bigint | |
| resource_key | bigint | |
| warehouse_task_id | varchar | |
| warehouse_order_id | varchar | |
| handling_unit_id | varchar | |
| task_quantity | decimal | |
| unit_of_measure | varchar | |
| weight | decimal | |
| volume | decimal | |
| planned_duration_seconds | decimal | |
| actual_duration_seconds | decimal | |
| travel_distance | decimal | |
| exception_count | integer | |
| is_pick | boolean | |
| is_putaway | boolean | |
| is_replenishment | boolean | |
| is_exception | boolean |
fct_inventory_snapshot
Grain: one row per product × bin × stock type per snapshot date (SAP /SCWM/AQUA + /SCWM/QUAN)
Periodic-snapshot fact for bin/stock utilization and inventory-accuracy reporting.
| Column | Type | Keys |
|---|---|---|
| inventory_snapshot_key | bigint | PK |
| snapshot_date_key | bigint | |
| product_key | bigint | |
| warehouse_key | bigint | |
| storage_bin_key | bigint | |
| stock_type_key | bigint | |
| owner_party_key | bigint | |
| quantity_on_hand | decimal | |
| available_quantity | decimal | |
| blocked_quantity | decimal | |
| quality_inspection_quantity | decimal | |
| bin_capacity_used_pct | decimal | |
| days_in_bin | integer | |
| valuation_amount | decimal | |
| currency | varchar |
Dimension tables (9)
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 |
dim_product
Grain: one row per product per SCD2 version (SAP EWM product matnr)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_number | varchar | BK |
| description | varchar | |
| ean_gtin | varchar | |
| product_group | varchar | |
| base_unit | varchar | |
| net_weight | decimal | |
| volume | decimal | |
| is_hazardous | boolean | |
| storage_condition | varchar | |
| is_batch_managed | boolean |
dim_warehouse
Grain: one row per EWM warehouse number (lgnum)
| Column | Type | Keys |
|---|---|---|
| warehouse_key | bigint | PK |
| warehouse_number | varchar | BK |
| description | varchar | |
| plant | varchar | |
| supply_chain_unit | varchar | |
| location | varchar | |
| country | varchar |
dim_storage_bin
Grain: one row per storage bin (SAP /SCWM/LAGP lgpla) — role-played as source/dest/snapshot bin
| Column | Type | Keys |
|---|---|---|
| storage_bin_key | bigint | PK |
| storage_bin | varchar | BK |
| storage_type | varchar | |
| storage_section | varchar | |
| bin_type | varchar | |
| aisle | varchar | |
| stack | varchar | |
| level | varchar | |
| activity_area | varchar | |
| is_fixed_bin | boolean |
dim_process_type
Grain: one row per warehouse process type
| Column | Type | Keys |
|---|---|---|
| process_type_key | bigint | PK |
| warehouse_process_type | varchar | BK |
| description | varchar | |
| category | varchar | |
| is_outbound | boolean |
dim_activity_area
Grain: one row per EWM activity area (aaarea)
| Column | Type | Keys |
|---|---|---|
| activity_area_key | bigint | PK |
| activity_area | varchar | BK |
| description | varchar | |
| warehouse_number | varchar |
dim_resource
Grain: one row per warehouse resource (operator / equipment)
| Column | Type | Keys |
|---|---|---|
| resource_key | bigint | PK |
| resource | varchar | BK |
| resource_type | varchar | |
| operator_name | varchar | PII |
| resource_group | varchar | |
| is_active | boolean |
dim_stock_type
Grain: one row per EWM stock type (SAP /SCWM/STOCK_IW01-04 categories)
| Column | Type | Keys |
|---|---|---|
| stock_type_key | bigint | PK |
| stock_type | varchar | BK |
| description | varchar | |
| stock_category | varchar | |
| availability_group | varchar | |
| is_available | boolean |
dim_owner_party
Grain: one row per stock owner / party-entitled-to-dispose
| Column | Type | Keys |
|---|---|---|
| owner_party_key | bigint | PK |
| party | varchar | BK |
| name | varchar | PII |
| party_role | varchar | |
| is_external | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_warehouse_task.creation_date_key → dim_date (many-to-one)
- fct_warehouse_task.confirmation_date_key → dim_date (many-to-one)
- fct_warehouse_task.product_key → dim_product (many-to-one)
- fct_warehouse_task.warehouse_key → dim_warehouse (many-to-one)
- fct_warehouse_task.source_bin_key → dim_storage_bin (many-to-one)
- fct_warehouse_task.dest_bin_key → dim_storage_bin (many-to-one)
- fct_warehouse_task.process_type_key → dim_process_type (many-to-one)
- fct_warehouse_task.activity_area_key → dim_activity_area (many-to-one)
- fct_warehouse_task.resource_key → dim_resource (many-to-one)
- fct_inventory_snapshot.snapshot_date_key → dim_date (many-to-one)
- fct_inventory_snapshot.product_key → dim_product (many-to-one)
- fct_inventory_snapshot.warehouse_key → dim_warehouse (many-to-one)
- fct_inventory_snapshot.storage_bin_key → dim_storage_bin (many-to-one)
- fct_inventory_snapshot.stock_type_key → dim_stock_type (many-to-one)
- fct_inventory_snapshot.owner_party_key → dim_owner_party (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.