# Power BI retail modelling lab

Original synthetic fixture for model and DAX lessons. No real customers, course
prices or business results. The data is related to the spreadsheet teaching
scenario but is a separate fixture: do not assume its grand totals are identical.

Rebuild and verify with `python 11-Blog-Programme/labs/bi-retail/build_and_verify.py`.
Python standard library only. CSVs, SQLite schema and reference-check results are
included. DAX and Power BI application behaviour require Desktop/service review;
no tested PBIX or service deployment is claimed.

## Import and model contract

Import all four CSVs. Set CustomerID to Text before automatic numeric conversion;
keys and amounts to Whole Number; OrderDate, DeliveryDate and DimDate[Date] to Date.
Blank DeliveryDate on the pending line is intentional. Each monetary value is
integer paise. DiscountPaise and CostPaise are line totals, not per-unit values.

- FactSales: eight invoice/order lines, unique LineID. OrderID can repeat.
  Net value = Quantity * UnitPricePaise - DiscountPaise. Paid is the baseline
  measure population; this is invoice value, not an audited revenue-recognition rule.
- DimCustomer: one unique CustomerKey per row, including key 0 Unknown. Customer 5
  has no transactions. Unknown is an explicit unresolved identity bucket.
- DimProduct: one unique ProductKey per row, three categories.
- DimDate: unique, complete, nonblank daily dates covering full years 2025–2026.
  No 2025 transactions are provided; date coverage does not imply transaction coverage.

Create active, single-direction one-to-many relationships from DimCustomer and
DimProduct to their corresponding FactSales keys, and DimDate[Date] to OrderDate.
Use an inactive DimDate-to-DeliveryDate relationship only for lessons that explicitly
activate delivery-date semantics. Mark the date table as appropriate for the
time-intelligence approach used; check current Power BI guidance.

## Reference controls

All eight lines: 77,500 paise. Paid: seven lines, six orders, 69,500 net paise,
37,000 cost paise, 32,500 margin paise. Three known paid customers; including the
Unknown bucket produces four distinct customer keys. January Paid: 47,500;
February Paid: 22,000. Pending January: 8,000.

Paid category totals: Software 39,000, Training 23,500, Support 7,000.
Paid regions: North 44,500, West 20,000, Unknown 5,000; South has no paid activity.
Do not silently remove Unknown when reconciling the complete paid population.

Acceptance checks: reproduce these totals in the actual model; test slicer subsets,
inactive date selection, unknown keys and a duplicate dimension mutation. Record
Power BI version, relationship settings and filter selections with results.
Original fixture/code may be used, modified and redistributed for learning with
attribution to NeuraPath Academy.

## Separate time-series exercises

Run `python 11-Blog-Programme/labs/bi-retail/build_time_examples.py` for two separate
facts used only by the time lessons. They do not change FactSales or its controls.

- FactDailyValue: complete synthetic daily values from 2025-01-01 through 2026-02-28;
  1,000 paise every 2025 day and 2,000 every supplied 2026 day. Relate DimDate[Date]
  one-to-many, single-direction to its Date column. As of February 28, YTD is
  118,000 and trailing March 2025–February 2026 is 424,000. No later source coverage.
- FactTradingCoverage: six hypothetical trading dates with a unique sequential
  TradingIndex. February 4 is incomplete with unknown value; February 3 is complete
  with confirmed zero. Import as a separate exercise table, preserving its numeric
  completeness flag and missing value. Do not infer a real exchange/holiday calendar.

The time lessons identify which table they use. These are independent scenarios,
not additional sales lines to append to the retail fact. Expected results are in
time-example-results.json; no DAX execution is claimed.

## Independent inventory, currency and cohort exercises

Run `python 11-Blog-Programme/labs/bi-retail/build_extension_examples.py`.
These files are separate scenarios, not rows to append to FactSales.

- FactInventory: one signed quantity movement, including an opening entry, for SKU1.
  Relate DimDate[Date] to Date for date-window exercises. Closing quantities are
  100, 150, 120, 125, 45 and 40 across January 1–6, 2026.
- FactFXSales and DimFX: one sale and one rate per date/currency respectively.
  RateKey uniquely identifies a rate; relate DimFX one-to-many to FactFXSales.
  AmountLocal is in major local-currency units. INRPerLocalUnit is invented INR per
  one local major unit, not a market quote. Expected INR amounts: 8,350 / 16,800 /
  4,500, total 29,650. Preserve decimals; do not interpret these as paise.
- DimCohortCustomer and FactCustomerMonth: one customer with first observed month,
  and one customer/month with activity. Reproduces the advanced-SQL cohort example.
  Relate customer ID one-to-many, single-direction from dimension to fact. Use the
  dimension's FirstObservedMonth for cohort rows. Relate the separate DimActivityMonth
  one-to-many to FactCustomerMonth[MonthStart], and use its MonthStart for activity
  columns so empty activity cells retain a defined period. Observation ends April 2026.
  First observed month is not verified lifetime acquisition.

Expected reference results are in extension-example-results.json. The scripts do
not evaluate DAX or build a tested Power BI model.