Demo Dataset
The Spot the Problem charts and the table names in the DAX examples come from one synthetic dataset: a fictional manufacturer with three plants, twelve lines and 32 months of history. It is a star schema of 13 CSV files, 213,153 rows in all.
Analysis on This Data
How unit-pooled OEE drifts once lines with different ideal rates roll up, and DAX in ideal-rate minutes that adds up.
Downtime ParetoUnplanned minutes by reason per plant, with cumulative-share DAX that survives slicers and ties.
XmR ChartProcess behavior limits from a fixed baseline, read honestly, with the DAX to draw them.
Inventory SnapshotsClosing balance and average stock from month-end snapshots, and why LASTNONBLANK by part ships wrong numbers.
On-Time ShippingWhy the newest month flatters, how to count overdue open lines, and what changes between order month and ship month.
TRIR with Small NumbersExact Poisson ranges for a rate built on a handful of incidents, a test for whether a drop is real, and CHISQ.INV in DAX.
ISO WeeksWhy calendar year plus ISO week corrupts every week-1 total, an ISO year column, and two honest ways to compare with last year.
Price-Volume-MixA revenue bridge from the demo orders, and why measuring at the wrong grain turns customer mix into a pricing win.
ABC ClassificationParts ranked by usage value, the stock and days of supply each class holds, and dynamic ABC segmentation in DAX that handles ties.
Top N and OthersTop customers plus one Others bar that adds up, handles ties and respects slicers, and the HHI as one number for concentration.
Fictional data. Cascadia Industrial Products is a made-up company. The files are generated by a seeded script; nothing comes from a real plant.
Tables
| Table | Grain | Rows | Columns | Key | Key unique | CSV |
|---|---|---|---|---|---|---|
dim_date | One row per calendar day | 974 | 9 | date | Yes | 48 KB |
dim_plant | One row per plant | 3 | 7 | plant_id | Yes | 1 KB |
dim_line | One row per production line | 12 | 7 | line_id | Yes | 1 KB |
dim_product | One row per part | 200 | 8 | part_id | Yes | 15 KB |
dim_customer | One row per customer | 80 | 5 | customer_id | Yes | 3 KB |
dim_downtime_reason | One row per downtime reason | 10 | 4 | reason_id | Yes | 1 KB |
fact_production_hourly | Line by operating hour | 155,340 | 10 | date + hour + line_id | Yes | 5.4 MB |
fact_weekly_schedule | Line by week | 1,680 | 4 | week_start + line_id | Yes | 42 KB |
fact_orders | Sales order line | 44,848 | 12 | order_no + line_no | Yes | 3.2 MB |
fact_inventory_snapshot | Part by plant by month end (semi-additive) | 9,575 | 7 | snapshot_date + plant_id + part_id | Yes | 439 KB |
fact_labor_monthly | Plant by month | 96 | 6 | month_start + plant_id | Yes | 3 KB |
fact_plant_pnl_monthly | Plant by month | 96 | 11 | month_start + plant_id | Yes | 9 KB |
fact_safety_incidents | One row per incident | 239 | 6 | incident_id | Yes | 10 KB |
Bus Matrix
Rows are fact tables, columns are dimensions, and a mark means the fact joins to that dimension. A dimension
shared by several facts is conformed: one slicer on it filters all of them. Plant reaches the production
facts through Line, so a plant slicer filters hourly output by way of dim_line[plant_id].
| Fact | date | plant | line | product | customer | reason |
|---|---|---|---|---|---|---|
fact_production_hourly | Joins date | Through line | Joins line | Joins product | No | Joins reason |
fact_weekly_schedule | Joins date | Through line | Joins line | No | No | No |
fact_orders | Joins date | Joins plant | No | Joins product | Joins customer | No |
fact_inventory_snapshot | Joins date | Joins plant | No | Joins product | No | No |
fact_labor_monthly | Joins date | Joins plant | No | No | No | No |
fact_plant_pnl_monthly | Joins date | Joins plant | No | No | No | No |
fact_safety_incidents | Joins date | Joins plant | No | No | No | No |
Relationships and Key Checks
Each fact table joins to its dimensions many-to-one. The check below looks up every fact key in the dimension it points at. Every key finds its match, so no row lands in the blank member of a slicer.
| From (many) | To (one) | Rows | Blank key | No match |
|---|---|---|---|---|
fact_production_hourly[date] | dim_date[date] | 155,340 | 0 | 0 |
fact_production_hourly[line_id] | dim_line[line_id] | 155,340 | 0 | 0 |
fact_production_hourly[part_id] | dim_product[part_id] | 155,340 | 0 | 0 |
fact_production_hourly[downtime_reason_id] | dim_downtime_reason[reason_id] | 155,340 | 107,729 | 0 |
fact_weekly_schedule[week_start] | dim_date[date] | 1,680 | 0 | 0 |
fact_weekly_schedule[line_id] | dim_line[line_id] | 1,680 | 0 | 0 |
fact_orders[order_date] | dim_date[date] | 44,848 | 0 | 0 |
fact_orders[customer_id] | dim_customer[customer_id] | 44,848 | 0 | 0 |
fact_orders[part_id] | dim_product[part_id] | 44,848 | 0 | 0 |
fact_orders[plant_id] | dim_plant[plant_id] | 44,848 | 0 | 0 |
fact_inventory_snapshot[snapshot_date] | dim_date[date] | 9,575 | 0 | 0 |
fact_inventory_snapshot[plant_id] | dim_plant[plant_id] | 9,575 | 0 | 0 |
fact_inventory_snapshot[part_id] | dim_product[part_id] | 9,575 | 0 | 0 |
fact_labor_monthly[month_start] | dim_date[date] | 96 | 0 | 0 |
fact_labor_monthly[plant_id] | dim_plant[plant_id] | 96 | 0 | 0 |
fact_plant_pnl_monthly[month_start] | dim_date[date] | 96 | 0 | 0 |
fact_plant_pnl_monthly[plant_id] | dim_plant[plant_id] | 96 | 0 | 0 |
fact_safety_incidents[date] | dim_date[date] | 239 | 0 | 0 |
fact_safety_incidents[plant_id] | dim_plant[plant_id] | 239 | 0 | 0 |
dim_line[plant_id] | dim_plant[plant_id] | 12 | 0 | 0 |
Row Rules
Keys can match and a column can have the right type while the values are still impossible. These rules are what the KPI measures quietly assume. Each one runs over every row when the site is built; every row passes.
| Table | Rule | Rows | Failing | If it failed |
|---|---|---|---|---|
fact_production_hourly | Scrap is at most units produced | 155,340 | 0 | Quality % and FPY would go negative. |
fact_production_hourly | Downtime is 0 to 60 minutes per hour | 155,340 | 0 | Run time would be negative or above planned time, breaking Availability. |
fact_production_hourly | Downtime has a reason exactly when it is above zero | 155,340 | 0 | A Pareto by reason would miss minutes, or count reasons for hours with no stop. |
fact_production_hourly | Hour is 0 to 23 | 155,340 | 0 | Hourly grain and shift logic depend on it. |
fact_orders | Promise date is on or after order date | 44,848 | 0 | Lead-time and on-time measures assume it. |
fact_orders | Ship date, when present, is on or after order date | 44,848 | 0 | A line cannot ship before it was ordered. |
fact_orders | on_time matches ship and promise dates | 44,848 | 0 | The stored flag must agree with the dates it summarises. |
fact_orders | Quantity and prices are positive | 44,848 | 0 | Revenue and margin measures assume it. |
fact_inventory_snapshot | Extended cost equals quantity times unit cost | 9,575 | 0 | Inventory value would not tie to quantity on hand. |
fact_inventory_snapshot | Last movement is on or before the snapshot | 9,575 | 0 | Days-since-movement and slow-mover logic would go negative. |
fact_labor_monthly | Overtime hours are within hours worked | 96 | 0 | Overtime % would exceed 100%. |
fact_labor_monthly | Absence rate is between 0 and 1 | 96 | 0 | It is stored as a fraction, not a percent. |
fact_weekly_schedule | Produced units are within scheduled units | 1,680 | 0 | The demo data never overproduces. Real plants can, and then attainment should cap each line-week at 100% so a surplus does not hide a miss. |
fact_safety_incidents | Days away is zero unless the incident is recordable | 239 | 0 | Lost-time cases are recordable by definition. |
Column Profiles
dim_date
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
date | Date | 974 | 0 | 2024-01-01 | 2026-08-31 |
year | Whole number | 3 | 0 | 2024 | 2026 |
month_num | Whole number | 12 | 0 | 1 | 12 |
month_name | Text | 12 | 0 | ||
quarter | Whole number | 4 | 0 | 1 | 4 |
iso_week | Whole number | 52 | 0 | 1 | 52 |
week_start | Date | 140 | 0 | 2024-01-01 | 2026-08-31 |
day_name | Text | 7 | 0 | ||
is_weekday | Whole number (0/1) | 2 | 0 | 0 | 1 |
dim_plant
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
plant_id | Whole number | 3 | 0 | 1 | 3 |
plant_code | Text | 3 | 0 | ||
plant_name | Text | 3 | 0 | ||
city | Text | 3 | 0 | ||
state | Text | 3 | 0 | ||
square_feet | Whole number | 3 | 0 | 98000 | 185000 |
base_headcount | Whole number | 3 | 0 | 105 | 210 |
dim_line
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
line_id | Whole number | 12 | 0 | 1 | 12 |
plant_id | Whole number | 3 | 0 | 1 | 3 |
line_code | Text | 12 | 0 | ||
line_name | Text | 5 | 0 | ||
product_family | Text | 5 | 0 | ||
shift_count | Whole number | 2 | 0 | 2 | 3 |
ideal_rate_uph | Whole number | 12 | 0 | 22 | 693 |
dim_product
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
part_id | Whole number | 200 | 0 | 1 | 200 |
part_number | Text | 200 | 0 | ||
part_name | Text | 200 | 0 | ||
family | Text | 5 | 0 | ||
std_cost | Decimal number | 194 | 0 | 0.37 | 335.47 |
list_price | Decimal number | 194 | 0 | 0.63 | 444.66 |
uom | Text | 1 | 0 | ||
intro_date | Date | 190 | 0 | 2017-06-13 | 2023-09-21 |
dim_customer
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
customer_id | Whole number | 80 | 0 | 1 | 80 |
customer_code | Text | 80 | 0 | ||
customer_name | Text | 80 | 0 | ||
segment | Text | 4 | 0 | ||
state | Text | 15 | 0 |
dim_downtime_reason
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
reason_id | Whole number | 10 | 0 | 1 | 10 |
reason_code | Text | 10 | 0 | ||
reason_description | Text | 10 | 0 | ||
category | Text | 2 | 0 |
fact_production_hourly
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
date | Date | 835 | 0 | 2024-01-01 | 2026-08-31 |
hour | Whole number | 24 | 0 | 0 | 23 |
shift_no | Whole number | 3 | 0 | 1 | 3 |
line_id | Whole number | 12 | 0 | 1 | 12 |
part_id | Whole number | 200 | 0 | 1 | 200 |
units_produced | Whole number | 672 | 0 | 0 | 691 |
units_target | Whole number | 12 | 0 | 22 | 693 |
scrap_units | Whole number | 19 | 0 | 0 | 18 |
downtime_minutes | Whole number | 55 | 0 | 0 | 58 |
downtime_reason_id | Whole number | 10 | 107,729 | 1 | 10 |
fact_weekly_schedule
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
week_start | Date | 140 | 0 | 2024-01-01 | 2026-08-31 |
line_id | Whole number | 12 | 0 | 1 | 12 |
scheduled_units | Whole number | 1,534 | 0 | 268 | 86495 |
produced_units | Whole number | 1,507 | 0 | 204 | 68923 |
fact_orders
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
order_no | Text | 18,309 | 0 | ||
line_no | Whole number | 4 | 0 | 1 | 4 |
order_date | Date | 696 | 0 | 2024-01-01 | 2026-08-31 |
customer_id | Whole number | 80 | 0 | 1 | 80 |
part_id | Whole number | 200 | 0 | 1 | 200 |
plant_id | Whole number | 3 | 0 | 1 | 3 |
qty | Whole number | 635 | 0 | 1 | 2731 |
unit_price | Decimal number | 16,206 | 0 | 0.55 | 444.65 |
unit_cost | Decimal number | 194 | 0 | 0.37 | 335.47 |
promise_date | Date | 990 | 0 | 2024-01-06 | 2026-09-23 |
ship_date | Date | 970 | 605 | 2024-01-05 | 2026-08-31 |
on_time | Whole number (0/1) | 2 | 605 | 0 | 1 |
fact_inventory_snapshot
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
snapshot_date | Date | 32 | 0 | 2024-01-31 | 2026-08-31 |
plant_id | Whole number | 3 | 0 | 1 | 3 |
part_id | Whole number | 200 | 0 | 1 | 200 |
qty_on_hand | Whole number | 1,571 | 0 | 1 | 28310 |
unit_cost | Decimal number | 194 | 0 | 0.37 | 335.47 |
ext_cost | Decimal number | 8,952 | 0 | 2.59 | 3566443.38 |
last_movement_date | Date | 1,296 | 0 | 2022-06-20 | 2026-08-31 |
fact_labor_monthly
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
month_start | Date | 32 | 0 | 2024-01-01 | 2026-08-01 |
plant_id | Whole number | 3 | 0 | 1 | 3 |
headcount | Whole number | 38 | 0 | 102 | 224 |
hours_worked | Whole number | 96 | 0 | 15664 | 36191 |
overtime_hours | Whole number | 93 | 0 | 456 | 3976 |
absence_rate | Decimal number | 82 | 0 | 0.0138 | 0.0557 |
fact_plant_pnl_monthly
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
month_start | Date | 32 | 0 | 2024-01-01 | 2026-08-01 |
plant_id | Whole number | 3 | 0 | 1 | 3 |
revenue | Decimal number | 96 | 0 | 151240.23 | 4892112.81 |
material_cost | Decimal number | 96 | 0 | 59088.75 | 2187307.24 |
direct_labor | Decimal number | 96 | 0 | 21263.69 | 856919.41 |
overhead | Decimal number | 96 | 0 | 18527.54 | 646431.77 |
ppv | Decimal number | 96 | 0 | -31795.77 | 55775.97 |
scrap_cost | Decimal number | 96 | 0 | 1587.04 | 68254.81 |
freight | Decimal number | 96 | 0 | 4795.48 | 170851.9 |
maintenance | Decimal number | 96 | 0 | 2784.06 | 104024.8 |
utilities | Decimal number | 96 | 0 | 2415.6 | 61587.45 |
fact_safety_incidents
| Column | Type | Distinct | Blank | Min | Max |
|---|---|---|---|---|---|
incident_id | Whole number | 239 | 0 | 1 | 239 |
date | Date | 213 | 0 | 2024-01-02 | 2026-08-27 |
plant_id | Whole number | 3 | 0 | 1 | 3 |
incident_type | Text | 3 | 0 | ||
category | Text | 6 | 0 | ||
days_away | Whole number | 4 | 0 | 0 | 12 |