Global Supply Chain & OTIF Core - Semantic Model Documentation

1. Executive Overview & Health Summary

Owner: clara.vance@northwind-global.example • Upstream Connectors: SAP HANA, Snowflake

Refresh: Completed

5

Tables (5 Import / 0 DQ)

22

Columns (0 Calculated)

7

DAX Measures

4

Relationships (0 Bi-Di)

15

Power Query Steps

2

Bound Reports

Healthy model architectureZero refresh errors, non-production endpoints, bidirectional filter traps, or local file dependencies detected.

2. Data Model & Star Schema

Fact tables (blue) vs. Dimension tables (teal) • Hover or click any table to highlight relationships

flowchart LR
    classDef factNode fill:#e8f1fd,stroke:#0b63ce,stroke-width:2px,color:#0f172a;
    classDef dimNode fill:#e6f6f4,stroke:#0f766e,stroke-width:1.5px,color:#0f172a;
    classDef standaloneNode fill:#f8fafc,stroke:#94a3b8,stroke-width:1px,color:#334155;
    T1["[DIM] Dim Calendar (3 cols, 0 meas)"]
    T2["[DIM] Dim Customer (4 cols, 0 meas)"]
    T3["[DIM] Dim Material (3 cols, 0 meas)"]
    T4["[DIM] Dim Plant (4 cols, 0 meas)"]
    T5["[FACT] Fact Deliveries (8 cols, 7 meas)"]
    T5 -->|"DeliveryDate = Date (*:1)"| T1
    T5 -->|"CustomerKey = CustomerKey (*:1)"| T2
    T5 -->|"PlantCode = PlantCode (*:1)"| T4
    T5 -->|"MaterialKey = MaterialKey (*:1)"| T3
    class T1 dimNode;
    class T2 dimNode;
    class T3 dimNode;
    class T4 dimNode;
    class T5 factNode;

Many / Fact Side	Cardinality	One / Dimension Side	Filter Direction	State
Fact Deliveries.`DeliveryDate`	`* : 1`	Dim Calendar.`Date`	Single	Active
Fact Deliveries.`CustomerKey`	`* : 1`	Dim Customer.`CustomerKey`	Single	Active
Fact Deliveries.`PlantCode`	`* : 1`	Dim Plant.`PlantCode`	Single	Active
Fact Deliveries.`MaterialKey`	`* : 1`	Dim Material.`MaterialKey`	Single	Active

3. Data Sources & Tables Blueprint

Storage modes, source endpoints, business descriptions, visual Power Query pipelines, and column definitions

Table	Role	Mode	Connector	Endpoint / Database	Description	Cols	Measures	PQ Steps
Dim Calendar	Dimension	`import`	Snowflake	Snowflake: northwind.eu-central-1.snowflakecomputing.com	Shared corporate fiscal & ISO calendar dimension (2022-2028).	3	0	4
Dim Customer	Dimension	`import`	Snowflake	Snowflake: northwind.eu-central-1.snowflakecomputing.com	Harmonized B2B customer accounts and service-level tiers.	4	0	3
Dim Material	Dimension	`import`	SAP HANA	SAP HANA: pHanaServer	Product hierarchy, material group, and ABC velocity classification.	3	0	2
Dim Plant	Dimension	`import`	SAP HANA	SAP HANA: pHanaServer	Manufacturing plants and distribution hubs with RLS ownership mapping.	4	0	2
Fact Deliveries	Fact	`import`	SAP HANA	SAP HANA: pHanaServer	Outbound delivery lines from SAP S/4HANA via HANA Calculation View CV_SD_DELIVERY_OTIF.	8	7	4

Dim Calendar : Shared corporate fiscal & ISO calendar dimension (2022-2028).

Dimension3 cols4 PQ steps

Column	Data Type	Origin / Source Column	Format	Description
Date Key	`dateTime`	Date	yyyy-mm-dd	-
FiscalYear	`string`	FiscalYear	-	-
YearMonth	`string`	YearMonth	-	-

Drill-Down Hierarchies:

Fiscal Calendar: FiscalYear → YearMonth → Date

Power Query Transformation Pipeline (4 steps)

1. SourceSnowflake.Databases→2. DatabaseExpression→3. Selected ViewExpression→4. Filtered Active YearsTable.SelectRows

View Power Query M Script - Partition Dim Calendar (import)

let
    Source = Snowflake.Databases("northwind.eu-central-1.snowflakecomputing.com", "WH_ENTERPRISE"),
    Database = Source{[Name="EDW_CORE"]}[Data],
    #"Selected View" = Database{[Name="DIM_CALENDAR_ISO"]}[Data],
    #"Filtered Active Years" = Table.SelectRows(#"Selected View", each [FiscalYear] >= "FY22")
in
    #"Filtered Active Years"

Dim Customer : Harmonized B2B customer accounts and service-level tiers.

Dimension4 cols3 PQ steps

Column	Data Type	Origin / Source Column	Format	Description
CustomerKey Key	`int64`	CustomerKey	-	-
CustomerName	`string`	CustomerName	-	-
ServiceTier	`string`	ServiceTier	-	-
Country	`string`	Country	-	-

Power Query Transformation Pipeline (3 steps)

1. SourceSnowflake.Databases→2. ViewExpression→3. Renamed ColumnsTable.RenameColumns

View Power Query M Script - Partition Dim Customer (import)

let
    Source = Snowflake.Databases("northwind.eu-central-1.snowflakecomputing.com", "WH_ENTERPRISE"),
    View = Source{[Name="DIM_CUSTOMER_MDM"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(View, {{"CUST_ID", "CustomerKey"}})
in
    #"Renamed Columns"

Dim Material : Product hierarchy, material group, and ABC velocity classification.

Dimension3 cols2 PQ steps

Column	Data Type	Origin / Source Column	Format	Description
MaterialKey Key	`string`	MaterialKey	-	-
MaterialDescription	`string`	MaterialDescription	-	-
ProductFamily	`string`	ProductFamily	-	-

Power Query Transformation Pipeline (2 steps)

1. SourceSapHana.Database→2. ViewExpression

View Power Query M Script - Partition Dim Material (import)

let
    Source = SapHana.Database(pHanaServer),
    View = Source{[Name="CV_MD_MATERIAL"]}[Data]
in
    View

Dim Plant : Manufacturing plants and distribution hubs with RLS ownership mapping.

Dimension4 cols2 PQ steps

Column	Data Type	Origin / Source Column	Format	Description
PlantCode Key	`string`	PlantCode	-	-
PlantName	`string`	PlantName	-	-
Region	`string`	Region	-	-
RegionEmail Hidden	`string`	RegionEmail	-	-

Power Query Transformation Pipeline (2 steps)

1. SourceSapHana.Database→2. ViewExpression

View Power Query M Script - Partition Dim Plant (import)

let
    Source = SapHana.Database(pHanaServer),
    View = Source{[Name="CV_MD_PLANT_NETWORK"]}[Data]
in
    View

Fact Deliveries : Outbound delivery lines from SAP S/4HANA via HANA Calculation View CV_SD_DELIVERY_OTIF.

Fact8 cols4 PQ steps

Column	Data Type	Origin / Source Column	Format	Description
DeliveryDate Key	`dateTime`	DeliveryDate	-	-
CustomerKey Key	`int64`	CustomerKey	-	-
PlantCode Key	`string`	PlantCode	-	-
MaterialKey Key	`string`	MaterialKey	-	-
DeliveredQty	`double`	DeliveredQty	-	-
NetValueEUR	`decimal`	NetValueEUR	-	-
IsOnTime	`int64`	IsOnTime	-	-
IsInFull	`int64`	IsInFull	-	-

Power Query Transformation Pipeline (4 steps)

1. SourceSapHana.Database→2. NavigationExpression→3. Filtered CompletedTable.SelectRows→4. Typed ColumnsTable.TransformColumnTypes

View Power Query M Script - Partition Fact Deliveries (import)

let
    Source = SapHana.Database(pHanaServer),
    Navigation = Source{[Name="CV_SD_DELIVERY_OTIF"]}[Data],
    #"Filtered Completed" = Table.SelectRows(Navigation, each [DeliveryStatus] = "C"),
    #"Typed Columns" = Table.TransformColumnTypes(#"Filtered Completed", {{"NetValueEUR", Currency.Type}})
in
    #"Typed Columns"

Shared Power Query Parameters & Functions (1)

pHanaServer (Production SAP HANA Calculation View endpoint.)

"hana-ent-prod.northwind.internal:30015" meta [IsParameterQuery=true, Type="Text"]

4. DAX Measure Catalog

Auto-formatted & organized by Table → Display Folder → Base Aggregations first, Derived KPIs second

Measure	Host Table	Display Folder	Tier	Format	Reads	Used By	DAX Preview
Delivery Lines	Fact Deliveries	01. Base Volumes	Base	`#,##0`	0	2	`COUNTROWS('Fact Deliveries')`
Shipped Net Value	Fact Deliveries	01. Base Volumes	Base	`#,##0.00 "EUR"`	1	0	`SUM('Fact Deliveries'[NetValueEUR])`
Shipped Quantity	Fact Deliveries	01. Base Volumes	Base	`#,##0`	1	0	`SUM('Fact Deliveries'[DeliveredQty])`
OTIF %	Fact Deliveries	02. Service Level KPIs	Tier 2	`0.0%`	2	2	`DIVIDE([OTIF Lines], [Delivery Lines], 0)`
OTIF Lines	Fact Deliveries	02. Service Level KPIs	Tier 1	`#,##0`	3	1	`CALCULATE([Delivery Lines], 'Fact Deliveries'[IsOnTime] = 1, 'Fact Deliveries'[IsInFull] = 1)`
OTIF % vs PY	Fact Deliveries	03. Time Intelligence	Tier 3	`+0.0%;-0.0%;0.0%`	2	0	`VAR CurrentOTIF = [OTIF %] VAR PriorOTIF = CALCULATE([OTIF %], SAMEPERIODLASTYEAR('Dim Calendar'[Date])) RETUR...`
OTIF % YTD	Fact Deliveries	03. Time Intelligence	Tier 3	`0.0%`	2	0	`CALCULATE([OTIF %], DATESYTD('Dim Calendar'[Date]))`

5. Interactive End-to-End Live Lineage

Click any source, table, column, DAX measure, or report to trace live upstream & downstream impact

6. Row-Level Security (RLS), Downstream Reports & Access

1 RLS role(s) • 2 bound report(s)

RLS Role: Regional Supply Director read

Table Dim Plant

'Dim Plant'[RegionEmail] == USERPRINCIPALNAME()

Downstream Report	Report Workspace	Report Type
Executive OTIF & Carrier Performance Cross-Workspace Thin Report	Supply Chain & Procurement Analytics	`PowerBIReport`
Warehouse Dispatch Daily Monitor Cross-Workspace Thin Report	Supply Chain & Procurement Analytics	`PowerBIReport`

Principal	Type	Access Right
Supply Chain Analysts	`Group`	ReadExplore
Clara Vance	`User`	ReadWriteReshareExplore