> Sample blueprint generated by Blueprint BI (https://app.milestonebi.com/samples/manufacturing). Shown exactly as generated.

# 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.

## 1. Scope and inputs

**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

| 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)

## 2. Data architecture

### Data sources
| Source | System | Refresh | Notes |
|---|---|---|---|
| SAP S/4HANA MM | SAP S/4HANA (MATDOC, EKKO/EKPO, EKET) | Daily 01:00 CET | Goods movements, purchase orders, schedule lines and material master. |
| Supplier & Plant Master | SQL Server | Daily 01:30 CET | Curated supplier categories, plant regions and the RLS security mapping. |
| Inventory Targets | SQL Server | Monthly | Inventory turns and OTIF targets by plant and fiscal month. |

### Ingestion: Fabric Data Factory (SAP CDC connector and SQL Server copy activity)
Incremental load on change timestamp watermark; full reload of master data. Pipelines land raw SAP and SQL Server extracts in bronze as Delta tables.

### Layers
- **Bronze** (Fabric Lakehouse Delta tables): Raw source extracts kept as loaded for audit and replay.
- **Silver** (Fabric Lakehouse with Spark notebooks): Cleansed, typed and conformed data with movement classes and delivery flags.
- **Gold** (Fabric Lakehouse star schema): Kimball facts and dimensions, including the daily inventory snapshot.

### Semantic layer: Power BI semantic model in Microsoft Fabric (Direct Lake)
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.

| Area | Design |
|---|---|
| Refresh and alerts | Ready by 06:00 CET daily; failures alert the BI support Teams channel and the data engineering on-call. |
| 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 |

### Governance and 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.

## 3. Data model: Galaxy (star schema with multiple facts and conformed dimensions)

Measures are stored on a dedicated `_Measures` table.

### FactInventoryMovement (fact)
_Grain: One row per SAP material document line_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| PostingDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Posting date key. |
| PlantKey | INT | foreign key | → DimPlant[PlantKey]; hidden | Plant key. |
| MaterialKey | INT | foreign key | → DimMaterial[MaterialKey]; hidden | Material key. |
| MaterialDocument | NVARCHAR(20) | degenerate |  | SAP material document number and line. |
| MovementType | NVARCHAR(3) | attribute |  | SAP movement type code. |
| MovementClass | NVARCHAR(20) | attribute |  | Receipt, Issue, Transfer or Adjustment. |
| Qty | DECIMAL(18,3) | additive | hidden | Signed movement quantity in base unit. |
| UnitCost | DECIMAL(18,4) | non additive | hidden | Standard unit cost in EUR. |
| MovementValue | DECIMAL(18,2) | additive | hidden | Signed value in EUR (Qty x UnitCost). |
| ConsumptionValue | DECIMAL(18,2) | additive | hidden | Absolute EUR value of issues to production and sales (COGS proxy). |

### FactInventorySnapshot (fact)
_Grain: One row per plant, material and day with non-zero stock_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| SnapshotDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Snapshot date key. |
| PlantKey | INT | foreign key | → DimPlant[PlantKey]; hidden | Plant key. |
| MaterialKey | INT | foreign key | → DimMaterial[MaterialKey]; hidden | Material key. |
| StockQty | DECIMAL(18,3) | semi additive | hidden | Closing unrestricted stock quantity. |
| StockValue | DECIMAL(18,2) | semi additive | hidden | Closing stock value in EUR at standard cost. |
| DaysSinceLastIssue | INT | non additive | hidden | Days since the material last moved out. |
| AgeingBand | NVARCHAR(20) | attribute | sort by AgeingBandOrder | 0-90, 91-180, 181-365 or 365+ days. |
| AgeingBandOrder | INT | attribute | hidden | Sort order for AgeingBand. |

### FactPurchaseDelivery (fact)
_Grain: One row per PO schedule line_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| PONumber | NVARCHAR(20) | degenerate |  | Purchase order number. |
| POLine | INT | degenerate |  | PO item number. |
| PromisedDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Supplier promised date key (active date). |
| DeliveryDateKey | INT | foreign key | → DimDate[DateKey]; hidden | Actual delivery date key. |
| SupplierKey | INT | foreign key | → DimSupplier[SupplierKey]; hidden | Supplier key. |
| PlantKey | INT | foreign key | → DimPlant[PlantKey]; hidden | Receiving plant key. |
| MaterialKey | INT | foreign key | → DimMaterial[MaterialKey]; hidden | Material key. |
| OrderedQty | DECIMAL(18,3) | additive | hidden | Scheduled quantity. |
| DeliveredQty | DECIMAL(18,3) | additive | hidden | Received quantity. |
| IsOnTime | INT | additive | hidden | 1 when delivered within the on-time window. |
| IsInFull | INT | additive | hidden | 1 when delivered within 98-102% of ordered quantity. |
| IsOTIF | INT | additive | hidden | 1 when both on time and in full. |

### FactTarget (fact)
_Grain: One row per plant and fiscal month_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| MonthStartDateKey | INT | foreign key | → DimDate[DateKey]; hidden | First day of the fiscal month. |
| PlantKey | INT | foreign key | → DimPlant[PlantKey]; hidden | Plant key. |
| TurnsTarget | DECIMAL(6,2) | non additive | hidden | Annualised inventory turns target. |
| OTIFTarget | DECIMAL(5,4) | non additive | hidden | Supplier OTIF target. |

### DimDate (dimension · marked as date table)
_Grain: One row per calendar day_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | primary key | hidden | yyyymmdd key. |
| Date | DATE | date |  | Calendar date. |
| FiscalYear | NVARCHAR(7) | attribute | sort by FiscalYearNumber | Fiscal year label, e.g. FY2025. |
| FiscalYearNumber | INT | attribute | hidden | Fiscal year number. |
| FiscalMonth | NVARCHAR(10) | attribute | sort by FiscalMonthNumber | Fiscal month label, e.g. Jan. |
| FiscalMonthNumber | INT | attribute | hidden | Fiscal month number 1-12. |
| YearMonth | NVARCHAR(8) | attribute | sort by YearMonthNumber | Label e.g. 2025-03. |
| YearMonthNumber | INT | attribute | hidden | yyyymm number. |
| IsCompleteMonth | BIT | attribute |  | True for months fully closed. |

### DimPlant (dimension · SCD type 1)
_Grain: One row per SAP plant_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| PlantKey | INT | surrogate key | hidden | Surrogate key. |
| PlantCode | NVARCHAR(4) | business key |  | SAP plant code. |
| PlantName | NVARCHAR(60) | attribute |  | Plant name. |
| Country | NVARCHAR(40) | attribute |  | Country. |
| Region | NVARCHAR(40) | attribute |  | Operating region. |

### DimMaterial (dimension · SCD type 2)
_Grain: One row per material_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| MaterialKey | INT | surrogate key | hidden | Surrogate key. |
| MaterialNo | NVARCHAR(18) | business key |  | SAP material number. |
| MaterialDescription | NVARCHAR(80) | attribute |  | Material description. |
| MaterialType | NVARCHAR(30) | attribute |  | Raw Material, Semi-Finished, Finished Good, MRO Spare. |
| MaterialGroup | NVARCHAR(40) | attribute |  | Product family. |
| ABCClass | NVARCHAR(1) | attribute |  | ABC classification by consumption value. |

### DimSupplier (dimension · SCD type 1)
_Grain: One row per supplier_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| SupplierKey | INT | surrogate key | hidden | Surrogate key. |
| SupplierID | NVARCHAR(10) | business key |  | SAP vendor number. |
| SupplierName | NVARCHAR(80) | attribute |  | Supplier name. |
| SupplierCategory | NVARCHAR(40) | attribute |  | Procurement category. |
| SupplierCountry | NVARCHAR(40) | attribute |  | Supplier country. |

### Relationships

| 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 | No |
| 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 |

## 4. DAX measures

26 measures in 6 display folders.

### Inventory

**Inventory Value** · `€#,0`
```dax
Inventory Value =
VAR LastDt = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactInventorySnapshot ) ) )
RETURN CALCULATE ( SUM ( FactInventorySnapshot[StockValue] ), LastDt )
```
Closing stock value in EUR on the last date with data.

**Average Inventory Value** · `€#,0`
```dax
Average Inventory Value =
AVERAGEX ( VALUES ( DimDate[YearMonthNumber] ), CALCULATE ( [Inventory Value], ENDOFMONTH ( DimDate[Date] ) ) )
```
Average of month-end stock values in the period.

**Consumption Value** · `€#,0`
```dax
Consumption Value =
SUM ( FactInventoryMovement[ConsumptionValue] )
```
EUR value of issues to production and sales, used as COGS.

**Months In Period** · `#,0`
```dax
Months In Period =
DISTINCTCOUNT ( DimDate[YearMonthNumber] )
```
Number of months in the current filter context.

**Inventory Turns** · `0.0`
```dax
Inventory Turns =
VAR AnnualCons = DIVIDE ( [Consumption Value] * 12, [Months In Period] )
VAR AvgInv = [Average Inventory Value]
RETURN IF ( AvgInv > 0, DIVIDE ( AnnualCons, AvgInv ) )
```
Annualised consumption value divided by average inventory value.

**Days of Supply** · `#,0`
```dax
Days of Supply =
VAR DailyCons = DIVIDE ( [Consumption Value], COUNTROWS ( DimDate ) )
RETURN IF ( DailyCons > 0, DIVIDE ( [Inventory Value], DailyCons ) )
```
Closing stock value divided by average daily consumption.

**Slow Moving Stock Value** · `€#,0`
```dax
Slow Moving Stock Value =
VAR LastDt = LASTNONBLANK ( DimDate[Date], CALCULATE ( COUNTROWS ( FactInventorySnapshot ) ) )
RETURN CALCULATE ( SUM ( FactInventorySnapshot[StockValue] ), LastDt, FactInventorySnapshot[DaysSinceLastIssue] > 180 )
```
Closing stock value with no issue in over 180 days.

**Slow Moving Stock %** · `0.0%`
```dax
Slow Moving Stock % =
VAR Inv = [Inventory Value]
RETURN IF ( Inv > 0, DIVIDE ( [Slow Moving Stock Value], Inv ) )
```
Share of stock value with no issue in over 180 days.

### Time Intelligence

**Inventory Value PY** · `€#,0`
```dax
Inventory Value PY =
CALCULATE ( [Inventory Value], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Closing stock value in the same period last year.

**Inventory Value YoY %** · `0.0%`
```dax
Inventory Value YoY % =
VAR Cur = [Inventory Value]
VAR PY = [Inventory Value PY]
RETURN IF ( PY > 0, DIVIDE ( Cur - PY, PY ) )
```
Change in closing stock value against last year.

**Inventory Turns PY** · `0.0`
```dax
Inventory Turns PY =
CALCULATE ( [Inventory Turns], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Inventory turns in the same period last year.

**Supplier OTIF % PY** · `0.0%`
```dax
Supplier OTIF % PY =
CALCULATE ( [Supplier OTIF %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Supplier OTIF in the same period last year.

### Targets

**Inventory Turns Target** · `0.0`
```dax
Inventory Turns Target =
AVERAGE ( FactTarget[TurnsTarget] )
```
Turns target from the target table, averaged across plants and months.

**Inventory Turns vs Target** · `+0.0;-0.0`
```dax
Inventory Turns vs Target =
VAR T = [Inventory Turns Target]
VAR A = [Inventory Turns]
RETURN IF ( NOT ISBLANK ( T ) && NOT ISBLANK ( A ), A - T )
```
Turns gap to target; blank when target or actual is missing.

**Supplier OTIF Target** · `0.0%`
```dax
Supplier OTIF Target =
AVERAGE ( FactTarget[OTIFTarget] )
```
OTIF target from the target table by plant and month.

**Supplier OTIF vs Target** · `+0.0%;-0.0%`
```dax
Supplier OTIF vs Target =
VAR T = [Supplier OTIF Target]
VAR A = [Supplier OTIF %]
RETURN IF ( NOT ISBLANK ( T ) && NOT ISBLANK ( A ), A - T )
```
OTIF gap to target in percentage points.

### Supplier

**PO Lines Due** · `#,0`
```dax
PO Lines Due =
COUNTROWS ( FactPurchaseDelivery )
```
PO schedule lines due by promised date.

**OTIF Lines** · `#,0`
```dax
OTIF Lines =
SUM ( FactPurchaseDelivery[IsOTIF] )
```
PO lines delivered on time and in full.

**Supplier OTIF %** · `0.0%`
```dax
Supplier OTIF % =
VAR Due = [PO Lines Due]
RETURN IF ( Due > 0, DIVIDE ( [OTIF Lines], Due ) )
```
Share of due PO lines delivered on time and in full.

**On Time %** · `0.0%`
```dax
On Time % =
IF ( [PO Lines Due] > 0, DIVIDE ( SUM ( FactPurchaseDelivery[IsOnTime] ), [PO Lines Due] ) )
```
Share of due PO lines delivered on time.

**In Full %** · `0.0%`
```dax
In Full % =
IF ( [PO Lines Due] > 0, DIVIDE ( SUM ( FactPurchaseDelivery[IsInFull] ), [PO Lines Due] ) )
```
Share of due PO lines delivered in full.

**Average Delivery Delay Days** · `0.0`
```dax
Average Delivery Delay Days =
AVERAGEX ( FILTER ( FactPurchaseDelivery, NOT ISBLANK ( FactPurchaseDelivery[DeliveryDateKey] ) ), RELATED ( DimDate[Date] ) - LOOKUPVALUE ( DimDate[Date], DimDate[DateKey], FactPurchaseDelivery[DeliveryDateKey] ) ) * -1
```
Average days delivered after promised date; negative means early.

### Labels

**Selected Period Label** · `Text`
```dax
Selected Period Label =
VAR MinD = MIN ( DimDate[Date] )
VAR MaxD = MAX ( DimDate[Date] )
RETURN FORMAT ( MinD, "MMM yyyy" ) & " – " & FORMAT ( MaxD, "MMM yyyy" )
```
Selected period shown in page subtitles.

### RAG

**Inventory Turns RAG** · `0`
```dax
Inventory Turns RAG =
VAR _Value = [Inventory Turns]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 8, 1,    -- Green
        _Value >= 6.5, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ 8.0), 2 Amber (≥ 6.5), 3 Red (< 6.5). Drives conditional formatting for Inventory Turns.

**Supplier OTIF RAG** · `0`
```dax
Supplier OTIF RAG =
VAR _Value = [Supplier OTIF %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 0.95, 1,    -- Green
        _Value >= 0.9, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ 95.0%), 2 Amber (≥ 90.0%), 3 Red (< 90.0%). Drives conditional formatting for Supplier OTIF %.

**Slow Moving Stock RAG** · `0`
```dax
Slow Moving Stock RAG =
VAR _Value = [Slow Moving Stock %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value <= 0.1, 1,    -- Green
        _Value <= 0.15, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≤ 10.0%), 2 Amber (≤ 15.0%), 3 Red (> 15.0%). Drives conditional formatting for Slow Moving Stock %.

## 5. KPIs and RAG rules

| KPI | Definition | Value measure | RAG measure | Target | Green | Amber | Red |
|---|---|---|---|---|---|---|---|
| Inventory Turns | Annualised consumption value / average month-end inventory value. | [Inventory Turns] | [Inventory Turns RAG] | 8.0 | ≥ 8.0 | ≥ 6.5 | < 6.5 |
| Supplier OTIF % | PO lines delivered on time and in full / PO lines due. | [Supplier OTIF %] | [Supplier OTIF RAG] | 95% | ≥ 95.0% | ≥ 90.0% | < 90.0% |
| Slow Moving Stock % | Stock value with no issue in 180+ days / total stock value. | [Slow Moving Stock %] | [Slow Moving Stock RAG] | 10% | ≤ 10.0% | ≤ 15.0% | > 15.0% |

RAG measures return 1 (green #15803D), 2 (amber #B45309) or 3 (red #B91C1C). Use conditional formatting by rules on the RAG measure. Status is never shown by colour alone: pair each colour with an icon shape or the word.

## 6. Report design

### Page 1: Executive Summary
_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.

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Headline KPIs | kpi strip | values: [Inventory Value]; values: [Inventory Turns]; values: [Supplier OTIF %]; values: [Slow Moving Stock %]; tooltip: [Inventory Value YoY %] | Status icon and word from each KPI RAG measure; Prior-year change shown under each value |
| Inventory Value Trend vs Last Year | line | axis: DimDate[YearMonth]; values: [Inventory Value]; values: [Inventory Value PY] | Prior year as dashed grey line; Data labels in €M on last point; Filter: DimDate[IsCompleteMonth] = True |
| Inventory Turns by Plant | bar | 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 |
| Supplier OTIF % Trend | line | axis: DimDate[YearMonth]; values: [Supplier OTIF %]; values: [Supplier OTIF % PY] | Constant line at 95% labelled Target; Markers on each month |
| Stock Value by Material Type | donut | legend: DimMaterial[MaterialType]; values: [Inventory Value] | Data labels as percentage and €M |

### Page 2: Inventory Health
_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

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Stock Value by Ageing Band | stacked column | axis: FactInventorySnapshot[AgeingBand]; values: [Inventory Value]; legend: DimMaterial[MaterialType] | Data labels in €k; Bands sorted by AgeingBandOrder |
| Days of Supply by Material Group | bar | axis: DimMaterial[MaterialGroup]; values: [Days of Supply]; tooltip: [Inventory Value] | Sorted descending; Constant line at 45 days; Filter: Top 10 by [Days of Supply] |
| Plant by Material Type Turns | matrix | rows: DimPlant[PlantName]; columns: DimMaterial[MaterialType]; values: [Inventory Turns]; values: [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 |
| Top Slow-Moving Materials | table | values: DimMaterial[MaterialNo]; values: DimMaterial[MaterialDescription]; values: DimPlant[PlantName]; values: DimMaterial[ABCClass]; values: [Slow Moving Stock Value]; values: [Days of Supply] | Data bars on [Slow Moving Stock Value]; Sorted descending by value; Filter: Top 25 by [Slow Moving Stock Value] |

### Page 3: Supplier Performance
_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)

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Lowest OTIF Suppliers | bar | 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 |
| On Time vs In Full by Supplier | scatter | details: DimSupplier[SupplierName]; x: [On Time %]; y: [In Full %]; size: [PO Lines Due] | Reference lines at 95% on both axes |
| OTIF by Supplier Category | column | 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 |
| Supplier Scorecard | table | values: DimSupplier[SupplierName]; values: [PO Lines Due]; values: [On Time %]; values: [In Full %]; values: [Supplier OTIF %]; values: [Average Delivery Delay Days] | Icon and text status from [Supplier OTIF RAG]; Sorted ascending by OTIF |

### Theme

| Role | Colours |
|---|---|
| Data colours (in order) | #312E81, #4338CA, #4F46E5, #6366F1, #818CF8, #A5B4FC, #9CA3AF |
| Good / neutral / bad | #15803D, #B45309, #B91C1C |
| Text / background | #1F2937, #FFFFFF |
| Font | Segoe UI |

## 7. 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.
