PlantOps BI

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

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

Row counts, column counts and file sizes are read from the files when the site is built. "Key unique" checks that the grain holds: no two rows share the key.

TableGrainRowsColumnsKeyKey uniqueCSV
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

13 files, 9.2 MB in all.

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].

Factdateplantlineproductcustomerreason
fact_production_hourly Joins dateThrough lineJoins lineJoins productNoJoins reason
fact_weekly_schedule Joins dateThrough lineJoins lineNoNoNo
fact_orders Joins dateJoins plantNoJoins productJoins customerNo
fact_inventory_snapshot Joins dateJoins plantNoJoins productNoNo
fact_labor_monthly Joins dateJoins plantNoNoNoNo
fact_plant_pnl_monthly Joins dateJoins plantNoNoNoNo
fact_safety_incidents Joins dateJoins plantNoNoNoNo

● joins directly. ○ reaches it through dim_line.

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)RowsBlank keyNo 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

Blank downtime_reason_id is by design: an hour with no downtime has no reason. Keep it blank rather than inventing a "None" reason, or a count of reasons will include hours that had no stop.

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.

TableRuleRowsFailingIf 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.

14 rules, 822,013 row checks. The site's tests fail if any rule fails, so the demo data cannot drift into breaking the analyses built on it.

Column Profiles

Types are inferred from every value in the column. Min and max are shown for numbers and dates.

dim_date 974 rows
ColumnTypeDistinctBlankMinMax
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 3 rows
ColumnTypeDistinctBlankMinMax
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 12 rows
ColumnTypeDistinctBlankMinMax
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 200 rows
ColumnTypeDistinctBlankMinMax
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 80 rows
ColumnTypeDistinctBlankMinMax
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 10 rows
ColumnTypeDistinctBlankMinMax
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 155,340 rows
ColumnTypeDistinctBlankMinMax
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 1,680 rows
ColumnTypeDistinctBlankMinMax
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 44,848 rows
ColumnTypeDistinctBlankMinMax
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 9,575 rows
ColumnTypeDistinctBlankMinMax
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 96 rows
ColumnTypeDistinctBlankMinMax
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 96 rows
ColumnTypeDistinctBlankMinMax
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 239 rows
ColumnTypeDistinctBlankMinMax
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