SAP Inventory Analytics data model
Kimball warehouse for SAP inventory: transaction-grain material movements with material-document numbers (mblnr) as degenerate dimensions, a daily stock periodic snapshot combining MARD quantities and MBEW valuation (standard/moving-average price), and a periodic slow-moving fact with turns and obsolescence provisioning. Conformed MM-IM dimensions; SAP fiscal year/period drives period reporting.
SAP Materials Management inventory (MM-IM) analytics migrated from ECC / S/4HANA into Snowflake. Covers transaction-grain material movements (MSEG/MKPF, all movement types), a daily stock snapshot built from MARD quantities and MBEW valuation, and a slow-moving / obsolescence analysis over conformed material (MARA/MAKT, SCD2), plant (T001W), storage location (T001L), movement type, and batch 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.
- 10
- tables
- 100
- columns
- 15
- relationships
- 0
- 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
Transaction-grain movement fact at material-document line level
SAP MM-IM records every stock change as an MSEG line under an MKPF header; declaring the fact at that atomic grain captures all movement types (101/201/261/601) and supports drill-across to any inventory rollup.
Daily stock periodic snapshot combining MARD and MBEW
On-hand position (MARD quantities) and valuation (MBEW standard / moving-average price) are semi-additive balances needed daily for availability and balance-sheet inventory reporting independent of movement volume.
Separate slow-moving / obsolescence fact
Inventory health (days since last movement, turns, obsolescence provision) is a derived periodic accumulation used for IFRS/GAAP write-down decisions and is kept separate from raw movements and the daily position snapshot.
SCD2 on the material master
MARA/MARC/MBEW attributes (ABC indicator, MRP type, valuation class, price control) change over time; type-2 history preserves the as-was classification required for comparable inventory and valuation trend analysis.
Tables in the SAP Inventory 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_material_movement
Grain: one row per material-document movement line (MSEG/MKPF)
Transaction-grain inventory movement fact — every stock change.
| Column | Type | Keys |
|---|---|---|
| material_movement_key | bigint | PK |
| date_key | bigint | |
| fiscal_period_key | bigint | |
| material_key | bigint | |
| plant_key | bigint | |
| storage_location_key | bigint | |
| movement_type_key | bigint | |
| batch_key | bigint | |
| material_document | varchar | |
| material_doc_item | varchar | |
| reference_document | varchar | |
| movement_quantity | decimal | |
| entry_unit | varchar | |
| movement_value | decimal | |
| currency | varchar | |
| is_goods_receipt | boolean | |
| is_goods_issue | boolean |
fct_stock_snapshot
Grain: one row per material per storage location per day (periodic snapshot, from MARD/MBEW)
Daily stock periodic snapshot — quantities and valuation, semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| fiscal_period_key | bigint | |
| material_key | bigint | |
| plant_key | bigint | |
| storage_location_key | bigint | |
| unrestricted_stock_qty | decimal | |
| quality_inspection_qty | decimal | |
| blocked_stock_qty | decimal | |
| in_transit_qty | decimal | |
| base_unit_of_measure | varchar | |
| standard_price | decimal | |
| moving_average_price | decimal | |
| total_stock_value | decimal | |
| currency | varchar |
fct_slow_moving
Grain: one row per material per plant per fiscal period (slow-moving / obsolescence analysis)
Periodic accumulation supporting slow-moving and obsolete-stock reporting.
| Column | Type | Keys |
|---|---|---|
| fiscal_period_key | bigint | |
| material_key | bigint | |
| plant_key | bigint | |
| last_movement_date_key | bigint | |
| on_hand_quantity | decimal | |
| on_hand_value | decimal | |
| days_since_last_movement | integer | |
| days_supply_remaining | integer | |
| annual_consumption_qty | decimal | |
| turns_ratio | decimal | |
| slow_moving_category | varchar | |
| obsolescence_provision | 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_material
Grain: one row per material per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| material_number | varchar | BK |
| material_description | varchar | |
| material_type | varchar | |
| material_group | varchar | |
| base_unit_of_measure | varchar | |
| division | varchar | |
| mrp_type | varchar | |
| abc_indicator | varchar | |
| valuation_class | varchar | |
| price_control | varchar | |
| is_batch_managed | boolean | |
| is_active | boolean |
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 | |
| valuation_area | varchar |
dim_storage_location
Grain: one row per storage location
| Column | Type | Keys |
|---|---|---|
| storage_location_key | bigint | PK |
| storage_location | varchar | BK |
| storage_location_name | varchar | |
| plant | varchar | |
| is_warehouse_managed | boolean |
dim_batch
Grain: one row per material batch (MCHA / MCH1)
| Column | Type | Keys |
|---|---|---|
| batch_key | bigint | PK |
| batch_number | varchar | BK |
| material_number | varchar | |
| plant | varchar | |
| date_of_manufacture | date | |
| shelf_life_expiry_date | date | |
| batch_status | varchar | |
| vendor_batch | varchar |
Lookup tables (1)
dim_movement_type
Grain: one row per SAP inventory movement type
| Column | Type | Keys |
|---|---|---|
| movement_type_key | bigint | PK |
| movement_type | varchar | BK |
| movement_type_name | varchar | |
| debit_credit_indicator | varchar | |
| movement_category | varchar | |
| is_reversal | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_material_movement.date_key → dim_date (many-to-one)
- fct_material_movement.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_material_movement.material_key → dim_material (many-to-one)
- fct_material_movement.plant_key → dim_plant (many-to-one)
- fct_material_movement.storage_location_key → dim_storage_location (many-to-one)
- fct_material_movement.movement_type_key → dim_movement_type (many-to-one)
- fct_material_movement.batch_key → dim_batch (many-to-one)
- fct_stock_snapshot.date_key → dim_date (many-to-one)
- fct_stock_snapshot.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_stock_snapshot.material_key → dim_material (many-to-one)
- fct_stock_snapshot.plant_key → dim_plant (many-to-one)
- fct_stock_snapshot.storage_location_key → dim_storage_location (many-to-one)
- fct_slow_moving.fiscal_period_key → dim_fiscal_period (many-to-one)
- fct_slow_moving.material_key → dim_material (many-to-one)
- fct_slow_moving.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.