All samples
Sample · Manufacturing · Inventory & Supply Chain

Plant Inventory & Supplier Performance Cockpit

A Power BI report on a Fabric Lakehouse that combines SAP MM goods movements and purchase order deliveries to track inventory value, turns and supplier OTIF by plant and material. It gives supply chain leaders one governed view of stock health and supplier reliability against targets of 8 turns and 95% OTIF.

Audience: Supply chain directors, plant managers, inventory controllers and procurement category managers. Generated by Blueprint BI on 2026-11-19 and shown exactly as generated.

What was entered

These are the only inputs. Everything below was generated from them.

Industry
Manufacturing
Report type
Inventory & Supply Chain
BI tool
Power BI
Currency
EUR (€)
Data sources
SAP, SQL Server
Columns
PlantCode, MaterialNo, MovementType, PostingDate, Qty, UnitCost, SupplierID, PO_Number, DeliveryDate, PromisedDate
KPIs
Inventory Turns (target 8), Supplier OTIF % (target 95%)
Notes
None
Style and pages
Corporate Clean, 3 pages

Scope

Purpose, default period and where each input column lands in the model.

Purpose
How much capital is tied up in stock by plant and material, how fast it turns, and which suppliers deliver on time and in full.
Audience
Supply chain directors, plant managers, inventory controllers and procurement category managers
Default period
Last 12 complete fiscal months, refreshed daily by 06:00 CET
Columns provided
10 columns supplied, and where each one lands in the model.
Source columnModel destinationNotes
PlantCodeDimPlant[PlantCode], FactInventoryMovement[PlantKey], FactPurchaseDelivery[PlantKey]SAP WERKS; resolved to the PlantKey surrogate key in silver.
MaterialNoDimMaterial[MaterialNo], FactInventoryMovement[MaterialKey], FactPurchaseDelivery[MaterialKey]SAP MATNR; leading zeros stripped, resolved to MaterialKey.
MovementTypeFactInventoryMovement[MovementType]SAP BWART (e.g. 101, 261, 601); mapped to a Receipt/Issue/Transfer/Adjustment class in silver.
PostingDateFactInventoryMovement[PostingDateKey], DimDate[Date]Converted to a yyyymmdd integer key.
QtyFactInventoryMovement[Qty], FactPurchaseDelivery[DeliveredQty]Signed by debit/credit indicator for movements; delivered quantity for PO receipts (movement type 101).
UnitCostFactInventoryMovement[UnitCost], FactInventoryMovement[MovementValue]Standard cost in EUR; MovementValue = Qty x UnitCost, computed in silver.
SupplierIDDimSupplier[SupplierID], FactPurchaseDelivery[SupplierKey]SAP LIFNR; resolved to the SupplierKey surrogate key.
PO_NumberFactPurchaseDelivery[PONumber]Renamed to PascalCase; kept as a degenerate key for distinct PO line counts.
DeliveryDateFactPurchaseDelivery[DeliveryDateKey]Actual goods receipt date, converted to a yyyymmdd key.
PromisedDateFactPurchaseDelivery[PromisedDateKey]Supplier-confirmed date from the PO schedule line, converted to a yyyymmdd key.

Business rules

  • On-time delivery. A PO line is on time when its final receipt falls between PromisedDate minus 2 days and PromisedDate. Calculated in: Silver layer, FactPurchaseDelivery load
  • In-full delivery. A PO line is in full when cumulative received quantity is between 98% and 102% of ordered quantity. Calculated in: Silver layer, FactPurchaseDelivery load
  • OTIF line. A PO line that is both on time and in full; open lines past their promised date count as failures. Calculated in: Gold layer, daily
  • Inventory value snapshot. Closing stock value per plant, material and day equals cumulative movement value up to that day. Calculated in: Gold layer FactInventorySnapshot, daily

1. Architecture

From source systems to the semantic model and the people who use it.

1Sources
SAP S/4HANA MM
SAP S/4HANA (MATDOC, EKKO/EKPO, EKET) · Daily 01:00 CET
Supplier & Plant Master
SQL Server · Daily 01:30 CET
Inventory Targets
SQL Server · Monthly
2Ingestion
Fabric Data Factory (SAP CDC connector and SQL Server copy activity)
Incremental load on change timestamp watermark; full reload of master data
3Storage & transform
Bronze
Fabric Lakehouse Delta tables
Silver
Fabric Lakehouse with Spark notebooks
Gold
Fabric Lakehouse star schema
4Semantic model
Power BI semantic model in Microsoft Fabric
Direct Lake
5Consumers
Supply chain leadership
Power BI App
Plant managers
Power BI App on mobile
Procurement
subscribed weekly PDF of supplier page

Data flow detail

Ingestion
Pipelines land raw SAP and SQL Server extracts in bronze as Delta tables.
Bronze
Raw source extracts kept as loaded for audit and replay.
Silver
Cleansed, typed and conformed data with movement classes and delivery flags.
Gold
Kimball facts and dimensions, including the daily inventory snapshot.
Semantic model
Direct Lake model over gold Delta tables with all measures in _Measures.
Caveat: Calculated columns are not supported; derive all columns in the gold layer.
Caveat: Queries fall back to DirectQuery if row-level security uses SQL endpoint or guardrails are exceeded.
Caveat: Keep the snapshot table V-Order optimised and partitioned by month to stay within guardrails.

Governance & security

  • Row-level security by plant from the SQL Server user-to-plant mapping table.
  • Data quality checks: orphan materials, negative stock, missing promised dates fail the gold load.
  • Refresh monitoring via Fabric monitoring hub with Teams alerts.
  • Certified semantic model with Purview lineage and sensitivity label Internal.
  • Measure and column descriptions maintained in the model.

Source detail

  • SAP S/4HANA MM: Goods movements, purchase orders, schedule lines and material master.
  • Supplier & Plant Master: Curated supplier categories, plant regions and the RLS security mapping.
  • Inventory Targets: Inventory turns and OTIF targets by plant and fiscal month.
Refresh and alertsReady by 06:00 CET daily; failures alert the BI support Teams channel and the data engineering on-call.
Storage modeDirect Lake
History3 fiscal years plus current year, covering 12 months shown and prior-year comparison.
EnvironmentsDev, Test and Prod Fabric workspaces
DeploymentFabric deployment pipelines with deployment rules for Lakehouse connections
Source controlNotebooks, semantic model and report stored as PBIP in Azure DevOps Git

2. Data model

Star schema with grain, keys and relationships.

Galaxy (star schema with multiple facts and conformed dimensions)

4 fact · 4 dimension tables · 13 relationships · measures on _Measures

Key Business key Foreign key Degenerate Numeric Hidden Sort by
DimDateDate tabledimension
Grain: One row per calendar day
  • DateKeyINT
  • DateDATE
  • FiscalYearNVARCHAR(7)
    sort by FiscalYearNumber
  • FiscalYearNumberINT
  • FiscalMonthNVARCHAR(10)
    sort by FiscalMonthNumber
  • FiscalMonthNumberINT
  • YearMonthNVARCHAR(8)
    sort by YearMonthNumber
  • YearMonthNumberINT
  • IsCompleteMonthBIT
DimMaterialSCD type 2dimension
Grain: One row per material
  • MaterialKeyINT
  • MaterialNoNVARCHAR(18)
    Business key
  • MaterialDescriptionNVARCHAR(80)
  • MaterialTypeNVARCHAR(30)
  • MaterialGroupNVARCHAR(40)
  • ABCClassNVARCHAR(1)
FactInventoryMovementfact
Grain: One row per SAP material document line
  • PostingDateKeyINT
    → DimDate[DateKey]
  • PlantKeyINT
    → DimPlant[PlantKey]
  • MaterialKeyINT
    → DimMaterial[MaterialKey]
  • MaterialDocumentNVARCHAR(20)
    Degenerate dimension
  • MovementTypeNVARCHAR(3)
  • MovementClassNVARCHAR(20)
  • QtyDECIMAL(18,3)
  • UnitCostDECIMAL(18,4)
  • MovementValueDECIMAL(18,2)
  • ConsumptionValueDECIMAL(18,2)
FactInventorySnapshotfact
Grain: One row per plant, material and day with non-zero stock
  • SnapshotDateKeyINT
    → DimDate[DateKey]
  • PlantKeyINT
    → DimPlant[PlantKey]
  • MaterialKeyINT
    → DimMaterial[MaterialKey]
  • StockQtyDECIMAL(18,3)
  • StockValueDECIMAL(18,2)
  • DaysSinceLastIssueINT
  • AgeingBandNVARCHAR(20)
    sort by AgeingBandOrder
  • AgeingBandOrderINT
FactPurchaseDeliveryfact
Grain: One row per PO schedule line
  • PONumberNVARCHAR(20)
    Degenerate dimension
  • POLineINT
    Degenerate dimension
  • PromisedDateKeyINT
    → DimDate[DateKey]
  • DeliveryDateKeyINT
    → DimDate[DateKey]
  • SupplierKeyINT
    → DimSupplier[SupplierKey]
  • PlantKeyINT
    → DimPlant[PlantKey]
  • MaterialKeyINT
    → DimMaterial[MaterialKey]
  • OrderedQtyDECIMAL(18,3)
  • DeliveredQtyDECIMAL(18,3)
  • IsOnTimeINT
  • IsInFullINT
  • IsOTIFINT
FactTargetfact
Grain: One row per plant and fiscal month
  • MonthStartDateKeyINT
    → DimDate[DateKey]
  • PlantKeyINT
    → DimPlant[PlantKey]
  • TurnsTargetDECIMAL(6,2)
  • OTIFTargetDECIMAL(5,4)
DimPlantSCD type 1dimension
Grain: One row per SAP plant
  • PlantKeyINT
  • PlantCodeNVARCHAR(4)
    Business key
  • PlantNameNVARCHAR(60)
  • CountryNVARCHAR(40)
  • RegionNVARCHAR(40)
DimSupplierSCD type 1dimension
Grain: One row per supplier
  • SupplierKeyINT
  • SupplierIDNVARCHAR(10)
    Business key
  • SupplierNameNVARCHAR(80)
  • SupplierCategoryNVARCHAR(40)
  • SupplierCountryNVARCHAR(40)

Grain note. FactInventoryMovement has no relationship to DimSupplier, so its measures are only meaningful by the other dimensions.

Grain note. FactInventorySnapshot has no relationship to DimSupplier, so its measures are only meaningful by the other dimensions.

Grain note. FactTarget has no relationship to DimMaterial, DimSupplier, so its measures are only meaningful by the other dimensions.

Relationships
From (many)To (one)CardinalityFilter directionActive
FactInventoryMovement.PostingDateKeyDimDate.DateKeymany-to-oneSingleYes
FactInventoryMovement.PlantKeyDimPlant.PlantKeymany-to-oneSingleYes
FactInventoryMovement.MaterialKeyDimMaterial.MaterialKeymany-to-oneSingleYes
FactInventorySnapshot.SnapshotDateKeyDimDate.DateKeymany-to-oneSingleYes
FactInventorySnapshot.PlantKeyDimPlant.PlantKeymany-to-oneSingleYes
FactInventorySnapshot.MaterialKeyDimMaterial.MaterialKeymany-to-oneSingleYes
FactPurchaseDelivery.PromisedDateKeyDimDate.DateKeymany-to-oneSingleYes
FactPurchaseDelivery.DeliveryDateKeyDimDate.DateKeymany-to-oneSingleInactive
FactPurchaseDelivery.SupplierKeyDimSupplier.SupplierKeymany-to-oneSingleYes
FactPurchaseDelivery.PlantKeyDimPlant.PlantKeymany-to-oneSingleYes
FactPurchaseDelivery.MaterialKeyDimMaterial.MaterialKeymany-to-oneSingleYes
FactTarget.MonthStartDateKeyDimDate.DateKeymany-to-oneSingleYes
FactTarget.PlantKeyDimPlant.PlantKeymany-to-oneSingleYes

3. DAX measures

An excerpt: the first 6 of 26 measures. Download the .dax file for all of them.

Validated: 26 measures, 0 errors, 5 warnings
Inventory Value
Closing stock value in EUR on the last date with data.
Inventory
Inventory Value =
VAR LastDt = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactInventorySnapshot ) ) )
RETURN CALCULATE ( SUM ( FactInventorySnapshot[StockValue] ), LastDt )
Inventory Value PY
Closing stock value in the same period last year.
Time Intelligence
Inventory Value PY =
CALCULATE ( [Inventory Value], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
Inventory Value YoY %
Change in closing stock value against last year.
Time Intelligence
Inventory Value YoY % =
VAR Cur = [Inventory Value]
VAR PY = [Inventory Value PY]
RETURN IF ( PY > 0, DIVIDE ( Cur - PY, PY ) )
Average Inventory Value
Average of month-end stock values in the period.
Inventory
Average Inventory Value =
AVERAGEX ( VALUES ( DimDate[YearMonthNumber] ), CALCULATE ( [Inventory Value], ENDOFMONTH ( DimDate[Date] ) ) )
Consumption Value
EUR value of issues to production and sales, used as COGS.
Inventory
Consumption Value =
SUM ( FactInventoryMovement[ConsumptionValue] )
Months In Period
Number of months in the current filter context.
Inventory
Months In Period =
DISTINCTCOUNT ( DimDate[YearMonthNumber] )
Download all 26 measures

4. KPIs and RAG rules

KPITargetDirectionGreen (1)Amber (2)Red (3)Example
Inventory Turns
Annualised consumption value / average month-end inventory value.
[Inventory Turns]
RAG: [Inventory Turns RAG]
8.0 Higher≥ 8.0≥ 6.5< 6.57.1 +0.4
Amber
Supplier OTIF %
PO lines delivered on time and in full / PO lines due.
[Supplier OTIF %]
RAG: [Supplier OTIF RAG]
95% Higher≥ 95.0%≥ 90.0%< 90.0%88.6% -2.1 pts
Red
Slow Moving Stock %
Stock value with no issue in 180+ days / total stock value.
[Slow Moving Stock %]
RAG: [Slow Moving Stock RAG]
10% Lower≤ 10.0%≤ 15.0%> 15.0%8.7% -1.2 pts
Green

How RAG is shown

1 Green#15803D2 Amber#B453093 Red#B91C1C

Each KPI has a RAG measure returning 1, 2 or 3 (BLANK when there is no value), so the thresholds live in one place. In Power BI, use conditional formatting with Format style "Rules" based on the RAG measure: 1 green, 2 amber, 3 red, and an icon set on the same rule. Status is never shown by colour alone: every colour is paired with its icon shape or word.

5. Report pages

Page layouts with the theme applied. Charts show placeholder shapes; the KPI cards use Claude's sample values.

Page 1: Executive Summary

Purpose
Show stock value, turns and supplier OTIF against target at a glance.
Audience
Supply chain director and plant managers
Default filter
Last 12 complete fiscal months
Slicers
DimDate[FiscalYear] (dropdown) · DimPlant[Region] (buttons): RLS restricts plants; region buttons show only permitted regions.
Title label
[Selected Period Label]
Plant Inventory & Supplier Performance Cockpit
Executive Summary
Last 12 complete fiscal months
Inventory Turns Amber
7.1
+0.4Target 8.0
Supplier OTIF % Red
88.6%
-2.1 ptsTarget 95%
Slow Moving Stock % Green
8.7%
-1.2 ptsTarget 10%
Inventory Value Trend vs Last Year
2024-072024-092024-112025-01
Inventory Turns by Plant
Stuttgart
104%
Lyon
95%
Brno
89%
Katowice
89%
Turin
73%
Eindhoven
73%
Supplier OTIF % Trend
2024-072024-092024-112025-01
Stock Value by Material Type
28%
  • Raw Material
  • Semi-Finished
  • Finished Good
  • MRO Spare
Visual specification: Executive Summary
VisualTypeFieldsFormatting and interactionAnswers
Headline KPIskpi strip
values: [Inventory Value], [Inventory Turns], [Supplier OTIF %], [Slow Moving Stock %]
tooltip: [Inventory Value YoY %]
  • Status icon and word from each KPI RAG measure
  • Prior-year change shown under each value
Are turns, OTIF and stock health on target?
Inventory Value Trend vs Last Yearline
axis: DimDate[YearMonth]
values: [Inventory Value], [Inventory Value PY]
  • Prior year as dashed grey line
  • Data labels in €M on last point
  • Filter: DimDate[IsCompleteMonth] = True
Is working capital in stock rising or falling?
Inventory Turns by Plantbar
axis: DimPlant[PlantName]
values: [Inventory Turns]
tooltip: [Inventory Turns Target]
  • Bar colour from [Inventory Turns RAG]
  • Constant line at 8.0 labelled Target
  • Sorted ascending
  • Drill-through to Inventory Health
Which plants turn stock slowest?
Supplier OTIF % Trendline
axis: DimDate[YearMonth]
values: [Supplier OTIF %], [Supplier OTIF % PY]
  • Constant line at 95% labelled Target
  • Markers on each month
Is supplier reliability improving?
Stock Value by Material Typedonut
legend: DimMaterial[MaterialType]
values: [Inventory Value]
  • Data labels as percentage and €M
Where is stock capital concentrated?

Page 2: Inventory Health

Purpose
Find excess, slow-moving and ageing stock by plant and material.
Audience
Inventory controllers and plant managers
Default filter
Latest complete month-end
Slicers
DimPlant[PlantName] (dropdown): Limited by plant RLS. · DimMaterial[MaterialType] (list) · DimMaterial[ABCClass] (buttons)
Drill-through from
Executive Summary
Title label
[Selected Period Label]
Plant Inventory & Supplier Performance Cockpit
Inventory Health
Latest complete month-end
Stock Value by Ageing Band
0-90 day91-180 d181-365 365+ day
Days of Supply by Material Group
Bearings
91%
Hydraulic Valves
89%
Steel Coil
83%
Fasteners
79%
Electrical Drives
77%
Packaging
75%
Plant by Material Type Turns
NameActualvs Target
Stuttgart£1461K106.7%
Lyon£1211K88.4%
Brno£1474K107.6%
Katowice£1423K103.9%
Turin£1463K106.8%
Eindhoven£1401K102.3%
Top Slow-Moving Materials
NameActualvs Target
100045872£1131K82.6%
100046211£1171K85.5%
200013907£1185K86.5%
300008854£1161K84.8%
100051230£1354K98.8%
Visual specification: Inventory Health
VisualTypeFieldsFormatting and interactionAnswers
Stock Value by Ageing Bandstacked column
axis: FactInventorySnapshot[AgeingBand]
legend: DimMaterial[MaterialType]
values: [Inventory Value]
  • Data labels in €k
  • Bands sorted by AgeingBandOrder
How much stock has not moved recently?
Days of Supply by Material Groupbar
axis: DimMaterial[MaterialGroup]
values: [Days of Supply]
tooltip: [Inventory Value]
  • Sorted descending
  • Constant line at 45 days
  • Filter: Top 10 by [Days of Supply]
Which product families hold too many days of stock?
Plant by Material Type Turnsmatrix
rows: DimPlant[PlantName]
columns: DimMaterial[MaterialType]
values: [Inventory Turns], [Inventory Turns PY]
  • Background colour and icon from [Inventory Turns RAG]
  • Targets omitted: FactTarget is not related to DimMaterial
  • Totals shown on rows and columns
Which plant and material combinations turn stock too slowly?
Top Slow-Moving Materialstable
values: DimMaterial[MaterialNo], DimMaterial[MaterialDescription], DimPlant[PlantName], DimMaterial[ABCClass], [Slow Moving Stock Value], [Days of Supply]
  • Data bars on [Slow Moving Stock Value]
  • Sorted descending by value
  • Filter: Top 25 by [Slow Moving Stock Value]
Which materials should be reviewed for write-down or redeployment?

Page 3: Supplier Performance

Purpose
Rank suppliers by OTIF and expose late or short deliveries.
Audience
Procurement category managers and buyers
Default filter
Last 12 complete fiscal months by promised date
Slicers
DimSupplier[SupplierCategory] (dropdown) · DimPlant[PlantName] (dropdown): Limited by plant RLS. · DimDate[FiscalYear] (dropdown)
Title label
[Selected Period Label]
Plant Inventory & Supplier Performance Cockpit
Supplier Performance
Last 12 complete fiscal months by promised date
Lowest OTIF Suppliers
Rheinmetall Castings
110%
Bosch Rexroth
104%
SKF Group
88%
ArcelorMittal
87%
Schneider Electric
86%
Würth Industrie
83%
On Time vs In Full by Supplier
OTIF by Supplier Category
CastingsElectricSteel & FastenerPackagin
Supplier Scorecard
NameActualvs Target
Rheinmetall Castings£1188K86.7%
Bosch Rexroth£1113K81.2%
SKF Group£1426K104.1%
ArcelorMittal£1365K99.6%
Schneider Electric£1315K96.0%
Visual specification: Supplier Performance
VisualTypeFieldsFormatting and interactionAnswers
Lowest OTIF Suppliersbar
axis: DimSupplier[SupplierName]
values: [Supplier OTIF %]
tooltip: [PO Lines Due]
  • Bar colour and status label from [Supplier OTIF RAG]
  • Constant line at 95%
  • Sorted ascending
  • Filter: Bottom 10 by [Supplier OTIF %] where [PO Lines Due] >= 20
Which suppliers miss OTIF most?
On Time vs In Full by Supplierscatter
x: [On Time %]
y: [In Full %]
size: [PO Lines Due]
details: DimSupplier[SupplierName]
  • Reference lines at 95% on both axes
Is failure driven by lateness or short shipment?
OTIF by Supplier Categorycolumn
axis: DimSupplier[SupplierCategory]
values: [Supplier OTIF %]
tooltip: [Supplier OTIF % PY]
  • Column colour and data label status from [Supplier OTIF RAG]
  • Constant line at 95% labelled Target
Which procurement categories underperform?
Supplier Scorecardtable
values: DimSupplier[SupplierName], [PO Lines Due], [On Time %], [In Full %], [Supplier OTIF %], [Average Delivery Delay Days]
  • Icon and text status from [Supplier OTIF RAG]
  • Sorted ascending by OTIF
How does each supplier score on every delivery measure?

6. AI-designed page

Page 1 as drawn by the AI Design Studio (a Pro feature), unedited.

Assumptions and open questions

Confirm these with the business before build. Each one changes the model or measures if the answer differs.
#Assumption or questionImpact if different
1Fiscal year equals calendar year, so SAMEPERIODLASTYEAR is valid.Prior-year measures must use DimDate fiscal period offsets instead.
2Consumption value (issues to production and sales at standard cost) is an acceptable COGS proxy for turns.Source COGS from SAP FI-CO and add a finance fact for the turns numerator.
3On time means delivered up to 2 days early and not late against the supplier-confirmed date.The IsOnTime flag window in silver changes, restating OTIF history.
4Targets are set per plant and fiscal month only, so target measures are never sliced by material or supplier.FactTarget needs material or supplier keys and new relationships.
5Stock is valued at standard cost and excludes consignment and in-transit stock.Snapshot logic must add special stock types or moving average price.
6The plant RLS mapping comes from the SQL Server security table keyed by Entra ID UPN.RLS roles must be rebuilt against a different security source.

Recommendations

  • Partition FactInventorySnapshot by month and keep only month-end rows beyond 13 months to control Direct Lake size.
  • Use incremental Delta merges on MATDOC change timestamps rather than full reloads.
  • Document every measure and business rule in the model and publish a data dictionary page in the App.
  • Agree the OTIF tolerance window with procurement and record it in the targets table.
  • Run a pilot with two plants before rollout and track App usage metrics for adoption.
  • Use Tabular Editor Best Practice Analyzer in the Git pipeline to enforce naming and hidden keys.

Make one for your own report

Start free