# Data model: Sample Outdoor Co. executive dashboard

> **Sample project, placeholder data.**

Star schema, two fact tables sharing conformed dimensions. Built so it can be loaded into Power BI or Looker Studio as is; the HTML dashboard uses the same tables pre-aggregated.

![ERD](build/erd.png)

## Relationships
| From (many) | To (one) | Key | Direction |
|---|---|---|---|
| fact_sales | dim_date | order_date → date | single |
| fact_sales | dim_product | product_id | single |
| fact_sales | dim_store | store_id | single |
| fact_target | dim_date | month_start → date | single |
| fact_target ↔ fact_sales | (via state + channel) | bridged by a `dim_state_channel` key in Power BI | single |

State on sales = the customer's state (store state for store sales, delivery state for online), so online revenue lands in the right region and can be compared with each state's plan.

## Tables
### dim_date (730 rows)
Calendar with fiscal year (Jul–Jun), quarter and month number. Marked as date table.

| Column | Type | Example |
|---|---|---|
| date | str | 2024-07-01 |
| month_start | str | 2024-07-01 |
| fiscal_year | str | FY25 |
| fiscal_quarter | str | Q1 |
| fiscal_month_no | whole number | 1 |
| month_name | str | Jul |
| is_weekend | whole number | 0 |

### dim_product (60 rows)
60 products in 6 categories with list price and unit cost.

| Column | Type | Example |
|---|---|---|
| product_id | str | P001 |
| product_name | str | Sample Cycle 2P Tent |
| category | str | Tents & Shelter |
| list_price | decimal | 345.95 |
| unit_cost | decimal | 208.47 |

### dim_store (8 rows)
7 stores in 4 states plus the online store.

| Column | Type | Example |
|---|---|---|
| store_id | str | S01 |
| store_name | str | Melbourne CBD |
| store_state | str | VIC |
| channel | str | Store |

### fact_sales (51,401 rows)
One row per order line. Grain: order × product.

| Column | Type | Example |
|---|---|---|
| order_id | str | O100001 |
| order_date | str | 2024-07-01 |
| store_id | str | S01 |
| customer_state | str | VIC |
| product_id | str | P046 |
| qty | whole number | 1 |
| unit_price | decimal | 38.95 |
| discount_pct | decimal | 0.0 |
| net_sales | decimal | 38.95 |
| cogs | decimal | 17.37 |
| returned | whole number | 0 |

### fact_target (96 rows)
Monthly plan. Grain: month × state × channel (FY26 only).

| Column | Type | Example |
|---|---|---|
| month_start | str | 2025-07-01 |
| state | str | NSW |
| channel | str | Online |
| target_net_sales | decimal | 39300.0 |

## Measures
Defined once in `measures.dax` and reproduced in the dashboard script:

```dax
// Sample Outdoor Co. executive dashboard: measure definitions (Power BI / DAX).
// Sample project, placeholder data. The HTML dashboard computes the same measures in the browser.
Net Sales = SUM ( fact_sales[net_sales] )
COGS = SUM ( fact_sales[cogs] )
Gross Profit = [Net Sales] - [COGS]
Gross Margin % = DIVIDE ( [Gross Profit], [Net Sales] )
Orders = DISTINCTCOUNT ( fact_sales[order_id] )
Avg Order Value = DIVIDE ( [Net Sales], [Orders] )
Units Sold = SUM ( fact_sales[qty] )
Returned Sales = CALCULATE ( [Net Sales], fact_sales[returned] = 1 )
Return Rate % = DIVIDE ( [Returned Sales], [Net Sales] )
Target = SUM ( fact_target[target_net_sales] )
Var to Target % = IF ( ISFILTERED ( dim_product[category] ), BLANK (), DIVIDE ( [Net Sales], [Target] ) - 1 )
Net Sales LY = CALCULATE ( [Net Sales], SAMEPERIODLASTYEAR ( dim_date[date] ) )
Net Sales vs LY % = DIVIDE ( [Net Sales], [Net Sales LY] ) - 1
Gross Margin LY % = CALCULATE ( [Gross Margin %], SAMEPERIODLASTYEAR ( dim_date[date] ) )
GM Change pts = ( [Gross Margin %] - [Gross Margin LY %] ) * 100
Online Share % = DIVIDE ( CALCULATE ( [Net Sales], dim_store[channel] = "Online" ), CALCULATE ( [Net Sales], ALL ( dim_store[channel] ) ) )
Product Rank = RANKX ( ALLSELECTED ( dim_product[product_name] ), [Net Sales], , DESC, DENSE )
```

## Rules that matter
- **Orders** is a distinct count, so it is never summed across categories or products. The dashboard keeps a separate order cube for this.
- **Target** exists only at state × channel × month, so variance to target is blank when a category filter is applied, rather than showing a misleading number.
- **Last year** comparisons use the same fiscal months one year earlier; FY25 has no prior year in the data, so LY values show as blank.
- **Gross margin** is on net sales after discounts; returns are reported as a rate, not netted off, matching how the plan was set.

## Refresh
Replace the five CSVs (same columns), then run `python3 build_dashboard.py`. In Power BI, point the queries at the CSV folder and refresh.