Manufacturing OEE Analytics data model
Kimball warehouse for manufacturing OEE: a production-run fact carrying the three OEE components, a downtime-event fact tied to six-big-losses reason codes, and a quality-inspection fact over conformed machine/product/work-order/shift/operator dimensions.
Manufacturing analytics centered on Overall Equipment Effectiveness (availability x performance x quality) covering production runs, downtime events, and quality inspections — a Kimball star schema with conformed machine (SCD2), product, work-order, shift, operator, and downtime-reason dimensions for plant-floor loss and yield reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 125
- columns
- 21
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Databricks). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Production run as the OEE transaction grain
Availability, performance, and quality are computed per machine, work order, and shift, so the production run is the natural grain that lets OEE roll up cleanly by line, plant, and time.
Separate downtime-event fact tied to reason codes
Loss analysis requires every stoppage at its own grain mapped to the six big losses; a dedicated downtime fact avoids diluting the production-run grain and supports Pareto root-cause reporting.
Machine modeled as SCD2
Machine attributes (rated speed, line assignment, asset class) change after retooling or relocation and must reflect the version in effect for each run to keep historical OEE accurate.
Quality inspection separate from production run
Inspections occur at a different cadence and sampling grain than runs; keeping them apart preserves additivity of defect counts while still feeding the OEE quality rate.
Tables in the Manufacturing OEE Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_production_run
Grain: one row per machine per work order per shift
Transaction-grain production run fact carrying OEE components.
| Column | Type | Keys |
|---|---|---|
| production_run_key | bigint | PK |
| date_key | bigint | |
| machine_key | bigint | |
| product_key | bigint | |
| work_order_key | bigint | |
| shift_key | bigint | |
| operator_key | bigint | |
| run_id | varchar | |
| planned_run_minutes | decimal | |
| actual_run_minutes | decimal | |
| downtime_minutes | decimal | |
| units_produced | integer | |
| good_units | integer | |
| reject_units | integer | |
| rework_units | integer | |
| ideal_cycle_time_sec | decimal | |
| availability_pct | decimal | |
| performance_pct | decimal | |
| quality_pct | decimal | |
| oee_pct | decimal |
fct_downtime_event
Grain: one row per downtime event
Transaction-grain downtime event fact for loss analysis.
| Column | Type | Keys |
|---|---|---|
| downtime_event_key | bigint | PK |
| date_key | bigint | |
| machine_key | bigint | |
| shift_key | bigint | |
| downtime_reason_key | bigint | |
| operator_key | bigint | |
| event_id | varchar | |
| duration_minutes | decimal | |
| is_unplanned | boolean | |
| requires_maintenance | boolean | |
| estimated_loss_cost | decimal |
fct_quality_inspection
Grain: one row per quality inspection sample
Transaction-grain quality inspection fact feeding the OEE quality rate.
| Column | Type | Keys |
|---|---|---|
| quality_inspection_key | bigint | PK |
| date_key | bigint | |
| machine_key | bigint | |
| product_key | bigint | |
| work_order_key | bigint | |
| operator_key | bigint | |
| defect_code_key | bigint | |
| inspection_id | varchar | |
| sample_size | integer | |
| passed_units | integer | |
| failed_units | integer | |
| defect_count | integer | |
| measured_value | decimal | |
| spec_lower_limit | decimal | |
| spec_upper_limit | decimal | |
| is_within_spec | boolean | |
| disposition | varchar |
fct_maintenance
Grain: one row per maintenance work event
Transaction-grain maintenance fact for reliability (MTBF/MTTR).
| Column | Type | Keys |
|---|---|---|
| maintenance_key | bigint | PK |
| date_key | bigint | |
| machine_key | bigint | |
| operator_key | bigint | |
| maintenance_type_key | bigint | |
| maintenance_id | varchar | |
| labor_hours | decimal | |
| machine_downtime_minutes | decimal | |
| labor_cost | decimal | |
| parts_cost | decimal | |
| mtbf_hours | decimal | |
| mttr_hours | decimal | |
| is_unplanned | boolean |
Dimension tables (5)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_working_day | boolean | |
| is_planned_maintenance_day | boolean |
dim_machine
Grain: one row per machine per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| machine_key | bigint | PK |
| machine_id | varchar | BK |
| machine_name | varchar | |
| machine_type | varchar | |
| manufacturer | varchar | |
| model_number | varchar | |
| production_line | varchar | |
| plant_code | varchar | |
| rated_speed_uph | decimal | |
| install_date | date | |
| asset_class | varchar | |
| is_active | boolean |
dim_product
Grain: one row per manufactured product
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| part_number | varchar | |
| product_name | varchar | |
| product_family | varchar | |
| unit_of_measure | varchar | |
| standard_cycle_time_sec | decimal | |
| target_yield_pct | decimal |
dim_work_order
Grain: one row per production work order
| Column | Type | Keys |
|---|---|---|
| work_order_key | bigint | PK |
| work_order_id | varchar | BK |
| work_order_type | varchar | |
| priority | varchar | |
| planned_quantity | integer | |
| bom_revision | varchar | |
| routing_code | varchar |
dim_operator
Grain: one row per operator per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| operator_key | bigint | PK |
| operator_id | varchar | BK |
| operator_name | varchar | PII |
| badge_number | varchar | PII |
| crew | varchar | |
| skill_level | varchar | |
| certification | varchar | |
| is_active | boolean |
Lookup tables (4)
dim_shift
Grain: one row per shift definition
| Column | Type | Keys |
|---|---|---|
| shift_key | bigint | PK |
| shift_code | varchar | BK |
| shift_name | varchar | |
| start_time | varchar | |
| end_time | varchar | |
| planned_minutes | integer |
dim_downtime_reason
Grain: one row per downtime reason code
| Column | Type | Keys |
|---|---|---|
| downtime_reason_key | bigint | PK |
| reason_code | varchar | BK |
| reason_name | varchar | |
| loss_category | varchar | |
| is_planned | boolean | |
| six_big_losses | varchar |
dim_defect_code
Grain: one row per quality defect code
| Column | Type | Keys |
|---|---|---|
| defect_code_key | bigint | PK |
| defect_code | varchar | BK |
| defect_name | varchar | |
| defect_category | varchar | |
| severity | varchar |
dim_maintenance_type
Grain: one row per maintenance type
| Column | Type | Keys |
|---|---|---|
| maintenance_type_key | bigint | PK |
| maintenance_type_code | varchar | BK |
| maintenance_type_name | varchar | |
| is_planned | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_production_run.date_key → dim_date (many-to-one)
- fct_production_run.machine_key → dim_machine (many-to-one)
- fct_production_run.product_key → dim_product (many-to-one)
- fct_production_run.work_order_key → dim_work_order (many-to-one)
- fct_production_run.shift_key → dim_shift (many-to-one)
- fct_production_run.operator_key → dim_operator (many-to-one)
- fct_downtime_event.date_key → dim_date (many-to-one)
- fct_downtime_event.machine_key → dim_machine (many-to-one)
- fct_downtime_event.shift_key → dim_shift (many-to-one)
- fct_downtime_event.downtime_reason_key → dim_downtime_reason (many-to-one)
- fct_downtime_event.operator_key → dim_operator (many-to-one)
- fct_quality_inspection.date_key → dim_date (many-to-one)
- fct_quality_inspection.machine_key → dim_machine (many-to-one)
- fct_quality_inspection.product_key → dim_product (many-to-one)
- fct_quality_inspection.work_order_key → dim_work_order (many-to-one)
- fct_quality_inspection.operator_key → dim_operator (many-to-one)
- fct_quality_inspection.defect_code_key → dim_defect_code (many-to-one)
- fct_maintenance.date_key → dim_date (many-to-one)
- fct_maintenance.machine_key → dim_machine (many-to-one)
- fct_maintenance.operator_key → dim_operator (many-to-one)
- fct_maintenance.maintenance_type_key → dim_maintenance_type (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.