A SQL Server schema for downtime and OEE data that Tableau can report on

“Our Tableau production reports don’t match what happened on the floor.” Before you rebuild the dashboard, look underneath it. The usual suspects are durations typed in at the end of a shift, a reason code that quietly changed meaning halfway through the year, a night shift split across two calendar days, and percentages averaged across shifts of different lengths. A schema designed for reporting from the first day closes off each of those.

What follows is the general downtime and OEE data model we recommend for SQL Server 2016 or later. It isn’t any client’s production schema. It works whether operators enter stops on a tablet or another system sends them; what to record on the floor is covered in how to track production downtime.

What your production reporting needs to answer

Start from the questions, not the tables. Each question forces a design decision, and a decision skipped now comes back later as a Tableau calculation nobody can explain.

The core tables: runs, downtime events, lines and shifts

Use two schemas. Tables live in prod, where the entry app reads and writes. Reporting views live in rpt, the only place Tableau looks. Every stop, run and shift timestamp is datetime2(0) in UTC: seconds are precise enough for stops, and UTC keeps the arithmetic simple.

Two small reference tables come first. prod.Line holds LineId, LineCode, LineName, IsActive and PlantTimeZone, a Windows time-zone name such as Eastern Standard Time. prod.Product holds ProductId, Sku and ProductName. Ideal cycle times can live in a standards table or another system; each run copies the value it used.

prod.DowntimeEvent: one row per stop

Good downtime table design puts three rules in the database, not just the app:

• End after start: CHECK (EndUtc IS NULL OR EndUtc > StartUtc).
• One open stop per line: a unique filtered index on LineId WHERE EndUtc IS NULL AND IsVoided = 0. SQL Server allows filters on indexes with the UNIQUE property, though not on unique constraints (Microsoft documentation).
• A void needs a reason: CHECK (IsVoided = 0 OR VoidReason IS NOT NULL).

Add an index on (LineId, StartUtc) that includes EndUtc, ReasonCodeId and IsVoided, because the reporting views filter by line and time.

prod.ProductionRun: what ran, how fast it should run, how many were good

The shift-boundary rule simplifies everything downstream. When a shift ends mid-run, the entry screen closes the run and opens a new one, so every count belongs to exactly one shift.

If counts or schedules already live in another system, load them into these tables through that system’s API instead of retyping them. That’s the kind of job our API integration services cover.

prod.ShiftInstance: the shift calendar

One row per line per scheduled shift: LineId, ProductionDate, ShiftName, StartUtc and EndUtc, unique on (LineId, StartUtc). The calendar is what turns stop minutes into availability, so it gets its own section below.

Reason codes with planned/unplanned flags and OEE factors

prod.ReasonCode holds Code, ReasonName, Category and IsActive, plus two flags that do different jobs:

• IsPlanned answers “was this stop on the schedule?” It drives the planned vs unplanned split.
• OeeLoss answers “where does this time land in OEE?” Allowed values are Availability, Performance, Quality and Excluded, meaning removed from planned production time.

They’re separate because your rules may not line up. A scheduled changeover is planned, yet some plants count it against availability so changeover time stays visible in OEE, while others exclude it. Store both flags and that choice lives in data an admin controls, not in a Tableau formula.

Keep the list short enough that operators pick the right code in a few seconds. Our downtime reason codes guide has a starter list and a category structure. If you also record rejects by reason, keep those codes in the same table with OeeLoss set to Quality.

Shifts, calendars and time zones

Store UTC and display local time. UTC doesn’t skip or repeat an hour, so durations and overlaps are plain subtraction. Convert to plant time only in the reporting views, with AT TIME ZONE, which is available from SQL Server 2016; the installed time-zone names are listed in sys.time_zone_info (Microsoft documentation).

The shift calendar is a table, not a formula. Generate prod.ShiftInstance from your shift patterns a few months ahead: for each line, date and shift, take the local start and the local end, convert each one to UTC separately, and store both. Don’t add eight hours to the UTC start. On daylight saving nights, that gives the wrong answer.

Example (hypothetical): a plant on Eastern time runs nights from 10 p.m. to 6 a.m.

Treat those two nights as 480 minutes and the report disagrees with the floor by a full hour of planned production time on each of them.

Store ProductionDate on each shift row too. A night shift that starts Saturday at 10 p.m. belongs to one production day, whichever rule your plant uses, and working it out from timestamps inside Tableau invites arguments. Finally, have the server stamp EnteredUtc with SYSUTCDATETIME() rather than trusting the clock on a tablet.

Reporting views for Tableau: availability, Pareto and planned vs unplanned

Point Tableau at views in the rpt schema, through a read-only login whose role has SELECT on rpt and nothing else. Views keep the business logic in one place that’s versioned with the database, and you can change a table without breaking every workbook. Tableau’s own help adds a performance reason to avoid pasting queries into workbooks: “Using custom SQL can affect performance of a workbook” (Tableau Help).

Expose minutes and counts, not percentages

rpt.vShiftOee returns additive columns: ShiftMin, ExcludedMin, PlannedProductionMin, AvailabilityLossMin, RunMin, IdealMin, TotalCount and GoodCount, plus PlannedStopMin, UnplannedStopMin and UncodedStopMin. Tableau sums them first and divides last:

• Availability = SUM([RunMin]) / SUM([PlannedProductionMin])
• Performance = SUM([IdealMin]) / SUM([RunMin])
• Quality = SUM([GoodCount]) / SUM([TotalCount])
• OEE = Availability × Performance × Quality

Example (hypothetical): one shift had 400 planned production minutes and ran 360, which is 90% availability. A short Saturday shift had 40 and ran 20, which is 50%. Averaging the two percentages says 70%. Summing the minutes says 380 of 440, about 86%. Only the second matches the floor.

Short stops nobody logs aren’t lost, either. Run time still includes them, so they show up as lower performance.

Splitting stops at shift boundaries

A stop from 1:50 to 2:20 p.m., across a 2 p.m. shift change, belongs to two shifts. vShiftOee joins each stop to every shift it overlaps and counts only the overlapping minutes: from the later of the two starts to the earlier of the two ends. CASE expressions handle that on any supported version; GREATEST and LEAST read more neatly but need SQL Server 2022 or later (Microsoft documentation). vDowntimeEvents keeps each stop whole and assigns it to the shift it started in, which is what a Pareto chart needs.

The Pareto and planned vs unplanned sheets

For the Pareto, filter PlannedStatus to Unplanned, put Reason on columns sorted by SUM([DurationMin]) descending, and add a dual-axis line with a running total of the same measure, shown as percent of total. For the split, put PlannedStatus on color and leave “Not yet coded” visible as its own color. Filtering it out makes the data look cleaner than it is. To sanity-check the ranking first, paste an export into the downtime Pareto analyzer.

Data-quality rules that keep the dashboards honest

1. Never delete a stop. Void it with a reason. The views filter voided rows out, and the app’s database role gets SELECT, INSERT and UPDATE on prod, with DELETE explicitly denied.
2. Keep every edit. Make DowntimeEvent, ProductionRun and ReasonCode system-versioned temporal tables. SQL Server then copies the previous version of a row into a history table each time it’s updated or deleted (Microsoft documentation). The app writes ChangedBy on every update, so the history shows who as well as when.
3. Retire reason codes; never repurpose them. Fixing a typo in a name is fine. Changing what a code means rewrites every past stop that used it. Set IsActive to 0 and add a new code instead.
4. Calculate durations; never type them. The views compute minutes from StartUtc and EndUtc, so correcting one end time corrects every report.
5. Freeze the standard each record used. Copying IdealCycleSeconds onto each run means a new standard next quarter doesn’t quietly rewrite last quarter’s performance.
6. Show what’s missing. Uncoded stops appear as “Not yet coded” and count as availability loss until someone codes them, so gaps can’t make a line look better.
7. Clear the fix-it list daily. rpt.vDataQuality lists stops open longer than 12 hours, closed stops with no reason, overlapping stops on one line, and records outside any scheduled shift.

Putting this downtime tracking database schema to work

Everything in this guide can be written as one T-SQL script: the prod and rpt schemas, the six tables with their constraints, indexes and temporal history, the three reporting views, a shift-calendar generator, two database roles and a few rows of hypothetical sample data. It’s a general recommended pattern, not any client’s production schema. Once it’s written:

1. Run it in a new, empty test database on SQL Server 2016 or later, or Azure SQL Database.
2. Set PlantTimeZone on each line, then generate the shift calendar from your own shift patterns and check the two daylight saving nights.
3. Replace the sample reason codes with your list. Operations, not the developer, decides IsPlanned and OeeLoss for each code.
4. Add Tableau’s login to the tableau_reader role and connect to the rpt views only.
5. Build three sheets: availability by line and shift, an unplanned-downtime Pareto, and the planned vs unplanned split. Compare one week against what supervisors saw on the floor before anyone relies on it.

More practical guides on downtime, OEE and production data are in our guides library.