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
| Source column | Model destination | Notes |
|---|---|---|
| PlantCode | DimPlant[PlantCode], FactInventoryMovement[PlantKey], FactPurchaseDelivery[PlantKey] | SAP WERKS; resolved to the PlantKey surrogate key in silver. |
| MaterialNo | DimMaterial[MaterialNo], FactInventoryMovement[MaterialKey], FactPurchaseDelivery[MaterialKey] | SAP MATNR; leading zeros stripped, resolved to MaterialKey. |
| MovementType | FactInventoryMovement[MovementType] | SAP BWART (e.g. 101, 261, 601); mapped to a Receipt/Issue/Transfer/Adjustment class in silver. |
| PostingDate | FactInventoryMovement[PostingDateKey], DimDate[Date] | Converted to a yyyymmdd integer key. |
| Qty | FactInventoryMovement[Qty], FactPurchaseDelivery[DeliveredQty] | Signed by debit/credit indicator for movements; delivered quantity for PO receipts (movement type 101). |
| UnitCost | FactInventoryMovement[UnitCost], FactInventoryMovement[MovementValue] | Standard cost in EUR; MovementValue = Qty x UnitCost, computed in silver. |
| SupplierID | DimSupplier[SupplierID], FactPurchaseDelivery[SupplierKey] | SAP LIFNR; resolved to the SupplierKey surrogate key. |
| PO_Number | FactPurchaseDelivery[PONumber] | Renamed to PascalCase; kept as a degenerate key for distinct PO line counts. |
| DeliveryDate | FactPurchaseDelivery[DeliveryDateKey] | Actual goods receipt date, converted to a yyyymmdd key. |
| PromisedDate | FactPurchaseDelivery[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.
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 alerts | Ready by 06:00 CET daily; failures alert the BI support Teams channel and the data engineering on-call. |
|---|---|
| Storage mode | Direct Lake |
| History | 3 fiscal years plus current year, covering 12 months shown and prior-year comparison. |
| Environments | Dev, Test and Prod Fabric workspaces |
| Deployment | Fabric deployment pipelines with deployment rules for Lakehouse connections |
| Source control | Notebooks, 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
- DateKeyINT
- DateDATE
- FiscalYearNVARCHAR(7)sort by FiscalYearNumber
- FiscalYearNumberINT
- FiscalMonthNVARCHAR(10)sort by FiscalMonthNumber
- FiscalMonthNumberINT
- YearMonthNVARCHAR(8)sort by YearMonthNumber
- YearMonthNumberINT
- IsCompleteMonthBIT
- MaterialKeyINT
- MaterialNoNVARCHAR(18)Business key
- MaterialDescriptionNVARCHAR(80)
- MaterialTypeNVARCHAR(30)
- MaterialGroupNVARCHAR(40)
- ABCClassNVARCHAR(1)
- 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)
- SnapshotDateKeyINT→ DimDate[DateKey]
- PlantKeyINT→ DimPlant[PlantKey]
- MaterialKeyINT→ DimMaterial[MaterialKey]
- StockQtyDECIMAL(18,3)
- StockValueDECIMAL(18,2)
- DaysSinceLastIssueINT
- AgeingBandNVARCHAR(20)sort by AgeingBandOrder
- AgeingBandOrderINT
- PONumberNVARCHAR(20)Degenerate dimension
- POLineINTDegenerate 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
- MonthStartDateKeyINT→ DimDate[DateKey]
- PlantKeyINT→ DimPlant[PlantKey]
- TurnsTargetDECIMAL(6,2)
- OTIFTargetDECIMAL(5,4)
- PlantKeyINT
- PlantCodeNVARCHAR(4)Business key
- PlantNameNVARCHAR(60)
- CountryNVARCHAR(40)
- RegionNVARCHAR(40)
- 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.
| From (many) | To (one) | Cardinality | Filter direction | Active |
|---|---|---|---|---|
| FactInventoryMovement.PostingDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactInventoryMovement.PlantKey | DimPlant.PlantKey | many-to-one | Single | Yes |
| FactInventoryMovement.MaterialKey | DimMaterial.MaterialKey | many-to-one | Single | Yes |
| FactInventorySnapshot.SnapshotDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactInventorySnapshot.PlantKey | DimPlant.PlantKey | many-to-one | Single | Yes |
| FactInventorySnapshot.MaterialKey | DimMaterial.MaterialKey | many-to-one | Single | Yes |
| FactPurchaseDelivery.PromisedDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactPurchaseDelivery.DeliveryDateKey | DimDate.DateKey | many-to-one | Single | Inactive |
| FactPurchaseDelivery.SupplierKey | DimSupplier.SupplierKey | many-to-one | Single | Yes |
| FactPurchaseDelivery.PlantKey | DimPlant.PlantKey | many-to-one | Single | Yes |
| FactPurchaseDelivery.MaterialKey | DimMaterial.MaterialKey | many-to-one | Single | Yes |
| FactTarget.MonthStartDateKey | DimDate.DateKey | many-to-one | Single | Yes |
| FactTarget.PlantKey | DimPlant.PlantKey | many-to-one | Single | Yes |
3. DAX measures
An excerpt: the first 6 of 26 measures. Download the .dax file for all of them.
Inventory Value =
VAR LastDt = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactInventorySnapshot ) ) )
RETURN CALCULATE ( SUM ( FactInventorySnapshot[StockValue] ), LastDt )Inventory Value PY =
CALCULATE ( [Inventory Value], SAMEPERIODLASTYEAR ( DimDate[Date] ) )Inventory Value YoY % =
VAR Cur = [Inventory Value]
VAR PY = [Inventory Value PY]
RETURN IF ( PY > 0, DIVIDE ( Cur - PY, PY ) )Average Inventory Value =
AVERAGEX ( VALUES ( DimDate[YearMonthNumber] ), CALCULATE ( [Inventory Value], ENDOFMONTH ( DimDate[Date] ) ) )Consumption Value =
SUM ( FactInventoryMovement[ConsumptionValue] )Months In Period =
DISTINCTCOUNT ( DimDate[YearMonthNumber] )4. KPIs and RAG rules
| KPI | Target | Direction | Green (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.5 | 7.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
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]
- Raw Material
- Semi-Finished
- Finished Good
- MRO Spare
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Headline KPIs | kpi strip | values: [Inventory Value], [Inventory Turns], [Supplier OTIF %], [Slow Moving Stock %] tooltip: [Inventory Value YoY %] |
| Are turns, OTIF and stock health on target? |
| Inventory Value Trend vs Last Year | line | axis: DimDate[YearMonth] values: [Inventory Value], [Inventory Value PY] |
| Is working capital in stock rising or falling? |
| Inventory Turns by Plant | bar | axis: DimPlant[PlantName] values: [Inventory Turns] tooltip: [Inventory Turns Target] |
| Which plants turn stock slowest? |
| Supplier OTIF % Trend | line | axis: DimDate[YearMonth] values: [Supplier OTIF %], [Supplier OTIF % PY] |
| Is supplier reliability improving? |
| Stock Value by Material Type | donut | legend: DimMaterial[MaterialType] values: [Inventory Value] |
| 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]
| Name | Actual | vs Target |
|---|---|---|
| Stuttgart | £1461K | 106.7% |
| Lyon | £1211K | 88.4% |
| Brno | £1474K | 107.6% |
| Katowice | £1423K | 103.9% |
| Turin | £1463K | 106.8% |
| Eindhoven | £1401K | 102.3% |
| Name | Actual | vs Target |
|---|---|---|
| 100045872 | £1131K | 82.6% |
| 100046211 | £1171K | 85.5% |
| 200013907 | £1185K | 86.5% |
| 300008854 | £1161K | 84.8% |
| 100051230 | £1354K | 98.8% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Stock Value by Ageing Band | stacked column | axis: FactInventorySnapshot[AgeingBand] legend: DimMaterial[MaterialType] values: [Inventory Value] |
| How much stock has not moved recently? |
| Days of Supply by Material Group | bar | axis: DimMaterial[MaterialGroup] values: [Days of Supply] tooltip: [Inventory Value] |
| Which product families hold too many days of stock? |
| Plant by Material Type Turns | matrix | rows: DimPlant[PlantName] columns: DimMaterial[MaterialType] values: [Inventory Turns], [Inventory Turns PY] |
| Which plant and material combinations turn stock too slowly? |
| Top Slow-Moving Materials | table | values: DimMaterial[MaterialNo], DimMaterial[MaterialDescription], DimPlant[PlantName], DimMaterial[ABCClass], [Slow Moving Stock Value], [Days of Supply] |
| 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]
| Name | Actual | vs Target |
|---|---|---|
| Rheinmetall Castings | £1188K | 86.7% |
| Bosch Rexroth | £1113K | 81.2% |
| SKF Group | £1426K | 104.1% |
| ArcelorMittal | £1365K | 99.6% |
| Schneider Electric | £1315K | 96.0% |
| Visual | Type | Fields | Formatting and interaction | Answers |
|---|---|---|---|---|
| Lowest OTIF Suppliers | bar | axis: DimSupplier[SupplierName] values: [Supplier OTIF %] tooltip: [PO Lines Due] |
| Which suppliers miss OTIF most? |
| On Time vs In Full by Supplier | scatter | x: [On Time %] y: [In Full %] size: [PO Lines Due] details: DimSupplier[SupplierName] |
| Is failure driven by lateness or short shipment? |
| OTIF by Supplier Category | column | axis: DimSupplier[SupplierCategory] values: [Supplier OTIF %] tooltip: [Supplier OTIF % PY] |
| Which procurement categories underperform? |
| Supplier Scorecard | table | values: DimSupplier[SupplierName], [PO Lines Due], [On Time %], [In Full %], [Supplier OTIF %], [Average Delivery Delay Days] |
| 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
| # | Assumption or question | Impact if different |
|---|---|---|
| 1 | Fiscal year equals calendar year, so SAMEPERIODLASTYEAR is valid. | Prior-year measures must use DimDate fiscal period offsets instead. |
| 2 | Consumption 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. |
| 3 | On 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. |
| 4 | Targets 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. |
| 5 | Stock is valued at standard cost and excludes consignment and in-transit stock. | Snapshot logic must add special stock types or moving average price. |
| 6 | The 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.