SAP TM Transportation & Freight Analytics data model
Kimball warehouse for SAP TM transportation: a freight-order execution fact at FO stage/item grain (/SCMTMS/D_TORROT + /SCMTMS/D_TORITM) with four role-played date dimensions (planned/actual departure and arrival), role-played source and destination locations, and weight/volume/distance/transit measures for lane, mode and on-time analysis; plus a freight-settlement cost fact at FSD charge-item grain (/SCMTMS/D_TCHRGI) carrying base, fuel-surcharge, accessorial, tax, accrued and settled charge amounts over carrier (SCD2 scorecards), charge-type and freight-agreement dimensions for freight cost per kg/shipment, carrier scorecards and accessorial analysis.
SAP Transportation Management (TM) freight analytics migrated from S/4HANA into Snowflake. Models the SAP TM execution flow — a posted SD delivery creates a Freight Unit consolidated into a Freight Order (/SCMTMS/D_TORROT root + /SCMTMS/D_TORITM items), which drives a Freight Settlement Document whose charge items (/SCMTMS/D_TCHRGI) and elements (/SCMTMS/D_TCHRGE) are calculated from Freight Agreements. Two transaction-grain facts (freight-order execution and freight settlement) share conformed carrier (SCD2), location (role-played source/destination), transportation-mode, shipping-type, charge-type, freight-agreement, company-code, and date 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.
- 10
- tables
- 103
- columns
- 17
- 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
Two transaction-grain facts at different grains (FO execution vs FSD cost)
SAP TM separates physical execution from financial settlement: the Freight Order (/SCMTMS/D_TORROT) records the move while the Freight Settlement Document (FSD + /SCMTMS/D_TCHRGI) records the agreed cost. Modelling fct_freight_order at FO stage/item grain and fct_freight_settlement at FSD charge-item grain keeps each fact additive and avoids mixing operational (transit, on-time) with financial (charge) measures that have different grains.
Role-played source and destination location dimensions for lane analysis
A freight order and each settled charge reference both an origin and a destination location (/SCMTMS/D_TORROT srclocid/deslocid). Modelling dim_location once and role-playing it via distinct source_location_key and dest_location_key foreign keys gives true origin-destination lane analytics (freight cost per lane) without duplicating the dimension.
SCD2 carrier dimension for carrier scorecards
Carrier master attributes (rating tier, preferred status, primary mode, SCAC) change over time; type-2 history preserves the as-was carrier profile so on-time and cost scorecards compare each shipment against the carrier attributes that applied when it shipped.
Freight-agreement dimension on the settlement fact
FSD charges are calculated from Freight Agreements (/SCMTMS/D_TCCMA) via charge elements (/SCMTMS/D_TCHRGE); carrying dim_freight_agreement (contract vs spot, rate basis, validity) lets analysts attribute cost to the agreement and rate basis used, and audit contracted-vs-spot freight spend.
Four role-played date dimensions capturing the FU→FO→FSD timeline
On-time performance and transit analysis need planned vs actual departure and arrival as separate role-played date keys on the freight-order fact, while settlement posts on its own settlement date. Splitting these date roles supports delay-hours and on-time KPIs that follow the SD delivery → Freight Unit → Freight Order → Freight Settlement Document flow.
Tables in the SAP TM Transportation & Freight 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_freight_order
Grain: one row per freight order stage/item (/SCMTMS/D_TORROT root + /SCMTMS/D_TORITM item)
Freight-order execution fact — lane/mode/on-time performance at FO stage/item grain.
| Column | Type | Keys |
|---|---|---|
| freight_order_key | bigint | PK |
| planned_departure_date_key | bigint | |
| actual_departure_date_key | bigint | |
| planned_arrival_date_key | bigint | |
| actual_arrival_date_key | bigint | |
| carrier_key | bigint | |
| source_location_key | bigint | |
| dest_location_key | bigint | |
| transportation_mode_key | bigint | |
| shipping_type_key | bigint | |
| company_code_key | bigint | |
| freight_order_id | varchar | |
| freight_unit_id | varchar | |
| shipment_id | varchar | |
| gross_weight | decimal | |
| net_weight | decimal | |
| volume | decimal | |
| distance | decimal | |
| stop_count | integer | |
| container_count | integer | |
| planned_transit_hours | decimal | |
| actual_transit_hours | decimal | |
| delay_hours | decimal | |
| weight_unit | varchar | |
| currency | varchar | |
| is_on_time | boolean | |
| is_ltl | boolean | |
| is_export | boolean |
fct_freight_settlement
Grain: one row per freight settlement document charge item (FSD + /SCMTMS/D_TCHRGI)
Freight-settlement cost fact — FSD charge items for carrier cost and accessorial analysis.
| Column | Type | Keys |
|---|---|---|
| freight_settlement_key | bigint | PK |
| settlement_date_key | bigint | |
| carrier_key | bigint | |
| charge_type_key | bigint | |
| source_location_key | bigint | |
| dest_location_key | bigint | |
| company_code_key | bigint | |
| freight_agreement_key | bigint | |
| fsd_id | varchar | |
| freight_order_id | varchar | |
| charge_amount | decimal | |
| base_amount | decimal | |
| fuel_surcharge_amount | decimal | |
| accessorial_amount | decimal | |
| tax_amount | decimal | |
| accrued_amount | decimal | |
| settled_amount | decimal | |
| rate | decimal | |
| billable_weight | decimal | |
| currency | varchar | |
| is_accrued | boolean | |
| is_disputed | boolean |
Dimension tables (8)
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_carrier
Grain: one row per carrier per SCD2 version (SAP TM business partner)
SCD type 2
| Column | Type | Keys |
|---|---|---|
| carrier_key | bigint | PK |
| carrier_id | varchar | BK |
| carrier_name | varchar | PII |
| scac_code | varchar | |
| primary_mode | varchar | |
| country | varchar | |
| rating | varchar | |
| is_preferred | boolean |
dim_location
Grain: one row per transportation location (role-played as source and destination)
| Column | Type | Keys |
|---|---|---|
| location_key | bigint | PK |
| location_id | varchar | BK |
| location_name | varchar | |
| location_type | varchar | |
| city | varchar | |
| region | varchar | |
| country | varchar | |
| zone | varchar | |
| latitude | decimal | |
| longitude | decimal |
dim_transportation_mode
Grain: one row per transportation mode
| Column | Type | Keys |
|---|---|---|
| transportation_mode_key | bigint | PK |
| mode | varchar | BK |
| description | varchar | |
| category | varchar |
dim_shipping_type
Grain: one row per shipping type
| Column | Type | Keys |
|---|---|---|
| shipping_type_key | bigint | PK |
| shipping_type | varchar | BK |
| description | varchar | |
| ftl_ltl | varchar | |
| is_parcel | boolean |
dim_charge_type
Grain: one row per freight charge type (SAP TM charge element)
| Column | Type | Keys |
|---|---|---|
| charge_type_key | bigint | PK |
| charge_type | varchar | BK |
| description | varchar | |
| charge_category | varchar | |
| is_accessorial | boolean |
dim_freight_agreement
Grain: one row per freight agreement (SAP TM forwarding/freight agreement)
| Column | Type | Keys |
|---|---|---|
| freight_agreement_key | bigint | PK |
| agreement_id | varchar | BK |
| carrier | varchar | |
| agreement_type | varchar | |
| rate_basis | varchar | |
| valid_from | date | |
| valid_to | date | |
| currency | varchar |
dim_company_code
Grain: one row per SAP company code
| Column | Type | Keys |
|---|---|---|
| company_code_key | bigint | PK |
| company_code | varchar | BK |
| company_name | varchar | |
| country | varchar | |
| currency | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_freight_order.planned_departure_date_key → dim_date (many-to-one)
- fct_freight_order.actual_departure_date_key → dim_date (many-to-one)
- fct_freight_order.planned_arrival_date_key → dim_date (many-to-one)
- fct_freight_order.actual_arrival_date_key → dim_date (many-to-one)
- fct_freight_order.carrier_key → dim_carrier (many-to-one)
- fct_freight_order.source_location_key → dim_location (many-to-one)
- fct_freight_order.dest_location_key → dim_location (many-to-one)
- fct_freight_order.transportation_mode_key → dim_transportation_mode (many-to-one)
- fct_freight_order.shipping_type_key → dim_shipping_type (many-to-one)
- fct_freight_order.company_code_key → dim_company_code (many-to-one)
- fct_freight_settlement.settlement_date_key → dim_date (many-to-one)
- fct_freight_settlement.carrier_key → dim_carrier (many-to-one)
- fct_freight_settlement.charge_type_key → dim_charge_type (many-to-one)
- fct_freight_settlement.source_location_key → dim_location (many-to-one)
- fct_freight_settlement.dest_location_key → dim_location (many-to-one)
- fct_freight_settlement.company_code_key → dim_company_code (many-to-one)
- fct_freight_settlement.freight_agreement_key → dim_freight_agreement (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.