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

# Retail Trading & Merchandising Performance

> A Power BI trading report combining SQL Server store EPOS and ERP data with Shopify online orders in a Fabric Lakehouse. It tracks Sales against target, Gross Margin % and Average Basket Value by store, channel and product hierarchy, with year-on-year comparisons. Store managers see only their stores through row-level security.

## 1. Scope and inputs

**Purpose.** How are sales, margin and basket value trending against target and last year, by channel, store and product category? Which stores and categories need action?

**Audience.** Retail directors, merchandising and buying teams, regional and store managers

**Default period.** Current month to date, compared with the same period last year; refreshed daily by 06:00.

### Columns provided

| Source column | Model destination | Notes |
|---|---|---|
| OrderID | FactSales[OrderID] | Degenerate key. Shopify IDs are prefixed 'SH-' so they cannot collide with EPOS transaction numbers. |
| OrderDate | FactSales[DateKey], DimDate[Date] | Converted to a yyyymmdd integer key. Times are truncated to the UK local date. |
| StoreCode | FactSales[StoreKey], DimStore[StoreCode] | Looked up to a surrogate key. Shopify orders map to the virtual store code 'WEB01'. |
| SKU | FactSales[ProductKey], DimProduct[SKU] | Looked up to a surrogate key. Shopify variant SKUs are matched to ERP SKUs. |
| Quantity | FactSales[Quantity] | Returns are kept as negative quantities on the original order. |
| UnitPrice | FactSales[UnitPrice], FactSales[GrossAmount] | Selling price excluding VAT. GrossAmount = Quantity × UnitPrice. |
| Discount | FactSales[DiscountAmount] | Assumed to be a line-level £ value excluding VAT. A percentage is converted to £ in Silver. |
| CustomerID | FactSales[CustomerKey], DimCustomer[CustomerID] | Anonymous EPOS sales map to the 'Unknown' member, CustomerKey -1. |
| Channel | FactSales[ChannelKey], DimChannel[ChannelName] | Standardised to Store, Online and Click & Collect. |

### Business rules
- **Net Sales.** NetSales = Quantity × UnitPrice − DiscountAmount, excluding VAT, with returns netted on the original order line. (calculated in: Gold layer, every load)
- **Cost of Sales.** CostAmount = Quantity × the ERP standard unit cost effective on the order date. (calculated in: Gold layer, every load)
- **Order (basket).** A distinct OrderID with positive net sales. Fully returned orders do not count as baskets. (calculated in: Semantic model measure [Orders])
- **Like-for-like store.** A store trading for the full current period and the same period last year, excluding refits closed for 7 or more days. (calculated in: Gold layer, DimStore[IsLikeForLike], refreshed monthly)

## 2. Data architecture

### Data sources
| Source | System | Refresh | Notes |
|---|---|---|---|
| EPOS & ERP | SQL Server | Daily 02:00 | Store transaction lines, product master, standard costs and store hierarchy. |
| Shopify Store | Shopify Admin API | Daily 02:30 | Online and click-and-collect orders, line items, discounts and customers. |
| Sales Targets | SQL Server (Finance planning schema) | Daily 02:00 | Monthly sales targets by store, signed off by Finance. |

### Ingestion: Microsoft Fabric Data Factory
Incremental load using a modified-date watermark, with a 7-day lookback for returns. Copy activities land SQL Server and Shopify data in Bronze; notebooks transform it to Silver and Gold.

### Layers
- **Bronze** (Fabric Lakehouse (Delta)): Raw, append-only copies of the source tables and Shopify JSON.
- **Silver** (Fabric Lakehouse (Delta, Spark notebooks)): Cleaned, deduplicated and conformed orders, SKUs, stores and channels.
- **Gold** (Fabric Lakehouse (Delta)): Star schema facts and dimensions with surrogate keys and derived amounts.

### Semantic layer: Power BI semantic model (Fabric) (Direct Lake)
Direct Lake over the Gold Delta tables, with a dedicated _Measures table.
- Caveat: Queries fall back to DirectQuery if RLS is defined on a SQL endpoint, so RLS is defined in the semantic model only.
- Caveat: Calculated columns are not supported, so derived columns are built in Gold.
- Caveat: Exceeding capacity guardrails on rows per table forces fallback; size the capacity at F64 or higher.

| Area | Design |
|---|---|
| Refresh and alerts | Ready by 06:00 daily; failures alert the BI support Teams channel and the on-call data engineer. |
| History | 3 complete years plus the current year, covering YoY comparisons for the default period. |
| Environments | Dev, Test and Prod Fabric workspaces |
| Deployment | Fabric deployment pipelines with Test sign-off by Merchandising |
| Source control | Semantic model and report stored as PBIP in Azure DevOps Git; notebooks under Fabric Git integration |

### Governance and security
- Row-level security on DimStore[AreaManagerUPN] limits managers to their stores.
- Data quality checks run in Silver: unmatched SKUs, unmatched stores and negative prices are logged and alerted.
- A daily reconciliation compares Gold net sales with EPOS Z-reads and Shopify payouts, with a tolerance of 0.5%.
- Sensitivity label 'Confidential' applies; customer IDs are pseudonymised.
- Refresh monitoring uses the Fabric Monitoring hub with failure alerts.

## 3. Data model: Star schema (galaxy with target fact)

Measures are stored on a dedicated `_Measures` table.

### FactSales (fact)
_Grain: One row per order line (OrderID and SKU)_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| OrderID | NVARCHAR(40) | degenerate | hidden | Order or transaction number. |
| DateKey | INT | foreign key | → DimDate[DateKey]; hidden | Order date key. |
| StoreKey | INT | foreign key | → DimStore[StoreKey]; hidden | Store surrogate key. |
| ProductKey | INT | foreign key | → DimProduct[ProductKey]; hidden | Product surrogate key. |
| CustomerKey | INT | foreign key | → DimCustomer[CustomerKey]; hidden | Customer surrogate key. |
| ChannelKey | INT | foreign key | → DimChannel[ChannelKey]; hidden | Channel surrogate key. |
| Quantity | INT | additive | hidden | Units sold, negative for returns. |
| UnitPrice | DECIMAL(18,2) | non additive | hidden | Unit selling price excluding VAT. |
| GrossAmount | DECIMAL(18,2) | additive | hidden | Quantity × UnitPrice. |
| DiscountAmount | DECIMAL(18,2) | additive | hidden | Line discount in £, excluding VAT. |
| NetSales | DECIMAL(18,2) | additive | hidden | Gross amount less discount. |
| CostAmount | DECIMAL(18,2) | additive | hidden | Standard cost of units sold. |

### FactSalesTarget (fact)
_Grain: One row per store per calendar month_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | foreign key | → DimDate[DateKey]; hidden | First day of the target month. |
| StoreKey | INT | foreign key | → DimStore[StoreKey]; hidden | Store surrogate key. |
| TargetSales | DECIMAL(18,2) | additive | hidden | Monthly net sales target in £. |

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

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| DateKey | INT | primary key | hidden | Date key in yyyymmdd format. |
| Date | DATE | date |  | Calendar date. |
| Year | INT | attribute |  | Calendar year. |
| MonthNumber | INT | attribute | hidden | Month number from 1 to 12. |
| MonthName | NVARCHAR(10) | attribute | sort by MonthNumber | Short month name. |
| YearMonthNumber | INT | attribute | hidden | Year-month in yyyymm format, used for sorting. |
| YearMonth | NVARCHAR(10) | attribute | sort by YearMonthNumber | Year-month label, e.g. Mar 2025. |
| DayOfWeekNumber | INT | attribute | hidden | Day of week, Monday = 1. |
| DayOfWeek | NVARCHAR(10) | attribute | sort by DayOfWeekNumber | Day name. |
| IsComplete | BIT | attribute |  | True for dates before today. |

### DimStore (dimension · SCD type 2)
_Grain: One row per store version_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| StoreKey | INT | surrogate key | hidden | Surrogate key. |
| StoreCode | NVARCHAR(10) | business key |  | ERP store code; WEB01 is the online store. |
| StoreName | NVARCHAR(100) | attribute |  | Store name. |
| Region | NVARCHAR(50) | attribute |  | Trading region. |
| StoreFormat | NVARCHAR(30) | attribute |  | High Street, Retail Park, Outlet or Online. |
| IsLikeForLike | BIT | attribute |  | Like-for-like flag. |
| AreaManagerUPN | NVARCHAR(100) | attribute | hidden | Area manager login, used for RLS. |
| ValidFrom | DATE | date | hidden | SCD2 start date. |
| ValidTo | DATE | date | hidden | SCD2 end date. |

### DimProduct (dimension · SCD type 1)
_Grain: One row per SKU_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| ProductKey | INT | surrogate key | hidden | Surrogate key. |
| SKU | NVARCHAR(30) | business key |  | Stock keeping unit. |
| ProductName | NVARCHAR(150) | attribute |  | Product description. |
| Department | NVARCHAR(50) | attribute |  | Merchandise department. |
| Category | NVARCHAR(50) | attribute |  | Merchandise category. |
| Brand | NVARCHAR(50) | attribute |  | Brand. |
| PriceBandSort | INT | attribute | hidden | Sort order for the price band. |
| PriceBand | NVARCHAR(20) | attribute | sort by PriceBandSort | Entry, Core or Premium. |

### DimCustomer (dimension · SCD type 1)
_Grain: One row per customer_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| CustomerKey | INT | surrogate key | hidden | Surrogate key; -1 is Unknown. |
| CustomerID | NVARCHAR(40) | business key | hidden | Pseudonymised customer ID. |
| CustomerType | NVARCHAR(20) | attribute |  | Loyalty, Registered or Anonymous. |
| HomeRegion | NVARCHAR(50) | attribute |  | Region from the postcode district. |

### DimChannel (dimension · SCD type 1)
_Grain: One row per sales channel_

| Column | Type | Role | Notes | Description |
|---|---|---|---|---|
| ChannelKey | INT | surrogate key | hidden | Surrogate key. |
| ChannelCode | NVARCHAR(10) | business key | hidden | Channel code. |
| ChannelName | NVARCHAR(30) | attribute |  | Store, Online or Click & Collect. |

### Relationships

| From (many) | To (one) | Cardinality | Filter direction | Active |
|---|---|---|---|---|
| FactSales.DateKey | DimDate.DateKey | many-to-one | single | Yes |
| FactSales.StoreKey | DimStore.StoreKey | many-to-one | single | Yes |
| FactSales.ProductKey | DimProduct.ProductKey | many-to-one | single | Yes |
| FactSales.CustomerKey | DimCustomer.CustomerKey | many-to-one | single | Yes |
| FactSales.ChannelKey | DimChannel.ChannelKey | many-to-one | single | Yes |
| FactSalesTarget.DateKey | DimDate.DateKey | many-to-one | single | Yes |
| FactSalesTarget.StoreKey | DimStore.StoreKey | many-to-one | single | Yes |

## 4. DAX measures

27 measures in 6 display folders.

### Sales

**Sales** · `£#,0`
```dax
Sales =
SUM ( FactSales[NetSales] )
```
Net sales excluding VAT, after discounts and returns.

**Gross Sales** · `£#,0`
```dax
Gross Sales =
SUM ( FactSales[GrossAmount] )
```
Sales before discounts.

**Discount Amount** · `£#,0`
```dax
Discount Amount =
SUM ( FactSales[DiscountAmount] )
```
Total discounts given.

**Discount %** · `0.0%`
```dax
Discount % =
VAR g = [Gross Sales] RETURN IF ( g <= 0, BLANK (), DIVIDE ( [Discount Amount], g ) )
```
Discount as a share of gross sales.

**Units Sold** · `#,0`
```dax
Units Sold =
SUM ( FactSales[Quantity] )
```
Net units sold.

**Orders** · `#,0`
```dax
Orders =
CALCULATE ( DISTINCTCOUNT ( FactSales[OrderID] ), FILTER ( VALUES ( FactSales[OrderID] ), CALCULATE ( SUM ( FactSales[NetSales] ) ) > 0 ) )
```
Distinct baskets with positive net sales.

**Average Basket Value** · `£#,0.00`
```dax
Average Basket Value =
VAR o = [Orders] RETURN IF ( o <= 0, BLANK (), DIVIDE ( [Sales], o ) )
```
Net sales per basket.

### Margin

**Cost of Sales** · `£#,0`
```dax
Cost of Sales =
SUM ( FactSales[CostAmount] )
```
Standard cost of units sold.

**Gross Margin** · `£#,0`
```dax
Gross Margin =
[Sales] - [Cost of Sales]
```
Sales less cost of sales.

**Gross Margin %** · `0.0%`
```dax
Gross Margin % =
VAR s = [Sales] RETURN IF ( s <= 0, BLANK (), DIVIDE ( [Gross Margin], s ) )
```
Gross margin as a share of sales.

### Time Intelligence

**Sales PY** · `£#,0`
```dax
Sales PY =
CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Sales for the same calendar period last year.

**Sales YoY %** · `+0.0%;-0.0%;0.0%`
```dax
Sales YoY % =
VAR py = [Sales PY] RETURN IF ( py <= 0, BLANK (), DIVIDE ( [Sales] - py, py ) )
```
Sales growth versus last year.

**Gross Margin % PY** · `0.0%`
```dax
Gross Margin % PY =
CALCULATE ( [Gross Margin %], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Gross margin % for the same period last year.

**Gross Margin % YoY pts** · `+0.0%;-0.0%;0.0%`
```dax
Gross Margin % YoY pts =
VAR cur = [Gross Margin %] VAR py = [Gross Margin % PY] RETURN IF ( ISBLANK ( cur ) || ISBLANK ( py ), BLANK (), cur - py )
```
Change in margin percentage points versus last year.

**Average Basket Value PY** · `£#,0.00`
```dax
Average Basket Value PY =
CALCULATE ( [Average Basket Value], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
```
Average basket value for the same period last year.

**Average Basket Value YoY %** · `+0.0%;-0.0%;0.0%`
```dax
Average Basket Value YoY % =
VAR py = [Average Basket Value PY] RETURN IF ( py <= 0, BLANK (), DIVIDE ( [Average Basket Value] - py, py ) )
```
Basket value growth versus last year.

### Targets

**Sales Target** · `£#,0`
```dax
Sales Target =
SUM ( FactSalesTarget[TargetSales] )
```
Monthly store sales target from Finance.

**Sales vs Target %** · `0.0%`
```dax
Sales vs Target % =
VAR t = [Sales Target] RETURN IF ( ISBLANK ( t ) || t <= 0, BLANK (), DIVIDE ( [Sales], t ) )
```
Sales as a share of target.

**Sales Variance** · `£#,0;-£#,0`
```dax
Sales Variance =
VAR t = [Sales Target] RETURN IF ( ISBLANK ( t ), BLANK (), [Sales] - t )
```
Sales minus target.

**Gross Margin % Target** · `0.0%`
```dax
Gross Margin % Target =
0.40
```
Company gross margin target of 40%.

**Gross Margin % Variance pts** · `+0.0%;-0.0%;0.0%`
```dax
Gross Margin % Variance pts =
VAR cur = [Gross Margin %] RETURN IF ( ISBLANK ( cur ), BLANK (), cur - [Gross Margin % Target] )
```
Margin points above or below target.

**Average Basket Value Target** · `£#,0.00`
```dax
Average Basket Value Target =
42
```
Company basket value target of £42.

**Average Basket Value Variance** · `£#,0.00;-£#,0.00`
```dax
Average Basket Value Variance =
VAR cur = [Average Basket Value] RETURN IF ( ISBLANK ( cur ), BLANK (), cur - [Average Basket Value Target] )
```
Basket value minus target.

### Labels

**Selected Period Label** · `Text`
```dax
Selected Period Label =
VAR d1 = MIN ( DimDate[Date] ) VAR d2 = MAX ( DimDate[Date] ) RETURN "Period: " & FORMAT ( d1, "d mmm yyyy" ) & " to " & FORMAT ( d2, "d mmm yyyy" )
```
Selected date range for page subtitles.

### RAG

**Sales RAG** · `0`
```dax
Sales RAG =
VAR _Value = [Sales]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 5000000, 1,    -- Green
        _Value >= 4500000, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ £5,000,000), 2 Amber (≥ £4,500,000), 3 Red (< £4,500,000). Drives conditional formatting for Sales.

**Gross Margin RAG** · `0`
```dax
Gross Margin RAG =
VAR _Value = [Gross Margin %]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 0.4, 1,    -- Green
        _Value >= 0.37, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ 40.0%), 2 Amber (≥ 37.0%), 3 Red (< 37.0%). Drives conditional formatting for Gross Margin %.

**Average Basket Value RAG** · `0`
```dax
Average Basket Value RAG =
VAR _Value = [Average Basket Value]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( _Value ), BLANK (),
        _Value >= 42, 1,    -- Green
        _Value >= 39, 2,    -- Amber
        3                    -- Red
    )
```
1 Green (≥ £42.00), 2 Amber (≥ £39.00), 3 Red (< £39.00). Drives conditional formatting for Average Basket Value.

## 5. KPIs and RAG rules

| KPI | Definition | Value measure | RAG measure | Target | Green | Amber | Red |
|---|---|---|---|---|---|---|---|
| Sales | Net sales excluding VAT after discounts and returns, against the £5M monthly target. | [Sales] | [Sales RAG] | £5M per month | ≥ £5,000,000 | ≥ £4,500,000 | < £4,500,000 |
| Gross Margin % | Net sales minus standard cost, divided by net sales. | [Gross Margin %] | [Gross Margin RAG] | 40% | ≥ 40.0% | ≥ 37.0% | < 37.0% |
| Average Basket Value | Net sales divided by distinct baskets with positive net sales. | [Average Basket Value] | [Average Basket Value RAG] | £42 | ≥ £42.00 | ≥ £39.00 | < £39.00 |

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
_Company trading position against target and last year._

- **Audience:** Retail directors and the executive team
- **Default filter:** Current month to date
- **Slicers:** DimDate[YearMonth] (dropdown): Single select; defaults to the current month.; DimChannel[ChannelName] (buttons); DimStore[Region] (dropdown): RLS limits the options to the regions a user can see.

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Trading KPIs | kpi strip | values: [Sales]; values: [Gross Margin %]; values: [Average Basket Value]; tooltip: [Sales YoY %]; tooltip: [Sales vs Target %] | Card status from [Sales RAG], [Gross Margin RAG] and [Average Basket Value RAG], shown as an icon plus the status word.; Each card shows its YoY change underneath. |
| Sales vs Last Year and Target by Month | line | axis: DimDate[YearMonth]; values: [Sales]; values: [Sales PY]; values: [Sales Target] | Sales PY shown as a dashed grey line; target as a stepped line.; Data labels on the last point only, in £M.; Filter: Last 12 complete months |
| Sales Mix by Channel | donut | legend: DimChannel[ChannelName]; values: [Sales]; tooltip: [Sales YoY %] | Data labels show the percentage of total. |
| Sales vs Target % by Region | bar | axis: DimStore[Region]; values: [Sales vs Target %]; tooltip: [Sales Variance] | Constant line at 100%.; Sorted ascending so the worst regions appear first.; Drill-through to Store Performance |
| Sales Bridge: Last Year to This Year by Department | waterfall | axis: DimProduct[Department]; values: [Sales]; tooltip: [Sales PY] | Breakdown versus Sales PY, with the starting bar labelled 'Last Year'.; Increases and decreases are labelled with signed £K values. |

### Page 2: Merchandising Performance
_Category, brand and SKU performance on sales, margin and discount._

- **Audience:** Merchandisers and buyers
- **Default filter:** Current month to date
- **Slicers:** DimProduct[Department] (dropdown); DimChannel[ChannelName] (buttons); DimProduct[PriceBand] (buttons)

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Category Trading Summary | matrix | rows: DimProduct[Department]; rows: DimProduct[Category]; values: [Sales]; values: [Sales YoY %]; values: [Gross Margin %]; values: [Gross Margin % YoY pts]; values: [Discount %]; values: [Units Sold] | Gross Margin % shows an icon and text from [Gross Margin RAG].; Sales YoY % uses a diverging font colour with +/- signs. |
| Discount vs Margin by Category | scatter | details: DimProduct[Category]; x: [Discount %]; y: [Gross Margin %]; size: [Sales] | Constant Y line at [Gross Margin % Target].; Category data labels shown. |
| Top 10 Brands by Sales | bar | axis: DimProduct[Brand]; values: [Sales]; tooltip: [Gross Margin %]; tooltip: [Sales YoY %] | Sorted descending; data labels in £K.; Filter: Top 10 by [Sales] |
| Bottom 20 SKUs by Gross Margin % | table | values: DimProduct[SKU]; values: DimProduct[ProductName]; values: DimProduct[Category]; values: [Units Sold]; values: [Sales]; values: [Discount %]; values: [Gross Margin %] | Gross Margin % shows an icon and text from [Gross Margin RAG].; Sorted ascending by Gross Margin %.; Filter: Bottom 20 by [Gross Margin %], where [Sales] > £1,000 |

### Page 3: Store Performance
_Store-level target delivery, basket value and channel trends._

- **Audience:** Regional and store managers
- **Default filter:** Current month to date
- **Slicers:** DimStore[Region] (dropdown): Filtered by RLS on DimStore[AreaManagerUPN].; DimStore[StoreName] (dropdown): Shows only stores permitted by RLS.; DimStore[IsLikeForLike] (buttons)
- **Drill-through from:** Executive Summary

| Visual | Type | Fields | Formatting and interaction |
|---|---|---|---|
| Store League Table | table | values: DimStore[StoreName]; values: DimStore[Region]; values: [Sales]; values: [Sales Target]; values: [Sales vs Target %]; values: [Sales YoY %]; values: [Average Basket Value]; values: [Gross Margin %] | Average Basket Value shows an icon and text from [Average Basket Value RAG]; Gross Margin % from [Gross Margin RAG].; Data bars on Sales vs Target %; sorted ascending. |
| Average Basket Value by Month | column | axis: DimDate[YearMonth]; values: [Average Basket Value]; tooltip: [Average Basket Value PY]; tooltip: [Orders] | Constant line at [Average Basket Value Target] (£42).; Column colour from [Average Basket Value RAG], with data labels in £0.00.; Filter: Last 12 complete months |
| Sales by Day of Week and Channel | stacked column | axis: DimDate[DayOfWeek]; legend: DimChannel[ChannelName]; values: [Sales] | Sorted by DayOfWeekNumber. |

### Theme

| Role | Colours |
|---|---|
| Data colours (in order) | #272378, #4F46E5, #7A73EC, #A6A1F3, #D2CFF9, #8C929C |
| 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 | Prior-year comparisons use the calendar year; the business may report on a 4-4-5 or 52/53-week retail calendar. | Add fiscal week, period and year columns to DimDate and rewrite the PY measures to shift by 364 days or by fiscal period. |
| 2 | Sales and UnitPrice exclude VAT, and Discount is a line-level £ amount. | Silver must strip VAT or convert a percentage discount to £, otherwise Sales and margin are overstated. |
| 3 | Sales targets are set by store and calendar month only, so target measures are not sliced by product, channel or day. | A product or channel target grain needs extra keys on FactSalesTarget; weekly targets need a DateKey per week. |
| 4 | The £5M Sales target and RAG bands apply to the company total per month; store status uses Sales vs Target %. | If the bands should apply per store, the Sales KPI must move to [Sales vs Target %] with bands of 1.00 and 0.95. |
| 5 | Click & Collect orders are attributed to the online store WEB01, not to the collecting store. | Store attribution would need a collection StoreCode from Shopify, which would shift sales between stores and Online. |
| 6 | Cost of sales uses the ERP standard cost effective on the order date, not weighted-average or landed cost. | Gold must join a costing history table, and Gross Margin % values will change. |
| 7 | The RLS mapping of area managers to stores is maintained in the ERP store master. | If HR holds the mapping, add a security bridge table fed from HR, which also allows multi-store access. |

## Recommendations
- Partition FactSales by month in Gold and use V-Order so Direct Lake stays within guardrails.
- Pre-aggregate Shopify payloads in Silver; do not land line-item JSON in Gold.
- Document every measure's description in the PBIP model and publish a data dictionary to the Power BI App.
- Use consistent naming: Fact/Dim prefixes, PascalCase columns and measure folders matching KPI names.
- Run Best Practice Analyzer and DAX query tests in the deployment pipeline before promoting to Prod.
- Drive adoption with a weekly trading subscription email to store managers, sent at 07:00 on Mondays.
