How to design a solid structure for machine data?

Every tool covered elsewhere on this blog — Power BI, Grafana, a data lake, an ETL pipeline — performs better with data that's been deliberately structured for analysis, rather than dumped in whatever shape it happened to arrive. This article covers how to design that structure: the star schema pattern most analytics tools are built around, how to choose the right grain for machine data specifically, and the naming consistency that makes a schema usable across a whole fleet rather than just one machine.

Contents:

1. Why schema design matters for analytics
2. Star schema basics: facts and dimensions
3. Designing the fact table for machine data
4. Designing dimension tables
5. Choosing the right grain
6. Naming consistency across machines
7. Common pitfalls
8. Frequently asked questions
9. Conclusion

Why schema design matters for analytics

Analytics and BI tools generally perform best against data that's been structured with their query patterns in mind, rather than raw operational data as it happens to be stored. A well-designed schema makes reports faster to build, easier to maintain, and less likely to produce inconsistent numbers across different reports built by different people. A poorly designed one — inconsistent naming, unclear granularity, missing relationships — tends to produce exactly the kind of quiet, hard-to-diagnose errors covered in our piece on shop floor data quality, just one layer further downstream.

Star schema basics: facts and dimensions

The star schema is the standard pattern for analytics-oriented data modeling, built from two types of tables:

• Fact tables hold the measurable events or observations — a machine reading, a downtime event, a completed cycle — along with numeric values and foreign keys pointing to related dimensions.
• Dimension tables hold descriptive context — machine details, time/calendar attributes, shift information, product or job data — that fact table rows reference rather than repeat.

This structure is called a "star" because a central fact table connects outward to multiple dimension tables, each providing a different lens for slicing the same underlying facts — by machine, by shift, by product, by time period — without duplicating that descriptive data in every single fact row.

Designing the fact table for machine data

Machine data often needs more than one fact table, since different kinds of facts have genuinely different natural grains:

• A telemetry fact table, holding high-frequency readings (spindle load, feed rate) at a fine time grain, often the largest table by row count.
• An event fact table, holding discrete occurrences (alarms, state changes, job completions) with a start and end time rather than a single instantaneous reading.
• A summary fact table, holding pre-aggregated values (hourly or per-shift Availability, Performance, Quality) built from the more granular tables, used specifically to keep dashboard queries fast without recalculating from raw data every time.

Trying to force all of these into a single fact table generally produces a structure that's awkward for at least one of the use cases it's meant to serve.

Designing dimension tables

• Machine dimension. Machine ID, name, controller brand/model, location, and any grouping attributes (line, cell, department) used for filtering and rollups.
• Calendar/time dimension. Date, shift, week, month, and fiscal period attributes, which make time-based filtering and comparison in reporting tools far easier than deriving them from raw timestamps every query.
• Shift dimension. Shift name, start/end times, and the operators or crew associated with it, useful for shift-based performance comparisons.
• Product/job dimension. Part number, job or work order ID, and related attributes, connecting machine activity back to what was actually being produced.
• Downtime reason dimension. A standardized list of downtime categories, referenced by the event fact table, which is what makes Pareto-style downtime reporting possible in the first place.

Choosing the right grain

"Grain" refers to what a single row in a fact table actually represents — one reading per second, one event per state change, one summary per shift. Choosing the grain too fine for a given fact table's purpose creates unnecessary volume and slower queries; choosing it too coarse loses detail that later analysis might need. A practical approach is matching grain to how the data will actually be aggregated in the transformation logic that turns signals into metrics: keep raw or near-raw grain in a telemetry table for troubleshooting and detailed analysis, and build coarser summary tables specifically for the dashboards that don't need that level of detail, rather than forcing every consumer to query the finest grain available.

Naming consistency across machines

A schema is only as useful as its consistency across the fleet it covers. If one machine's data uses "spindle_load" and another uses "SpindleLoadPct" for the conceptually same measurement, every downstream report has to account for that inconsistency individually, which multiplies effort and risk of error across every consumer of the schema. This connects directly to the definition-consistency issue covered in our piece on shop floor data quality: schema naming and signal interpretation need to be standardized together, ideally as part of the same governance process, not treated as separate concerns handled by whoever happened to configure each connection.

Common pitfalls

• One giant denormalized table. Combining everything into a single wide table without proper dimensions is simpler to build initially but becomes harder to maintain and query efficiently as the schema grows.
• Inconsistent naming across machines or sources. As covered above, this multiplies effort for every report and analysis built on top of the schema.
• No documentation of what each field actually means. A schema without a data dictionary forces every new analyst to rediscover meaning through trial and error, or worse, through incorrect assumptions.
• Designing for today's reports only. A schema built to answer only the current set of questions can require significant rework the first time a genuinely new analysis need arises; some deliberate flexibility (like keeping raw-grain data alongside summaries) helps absorb that without a full redesign.

Frequently asked questions

Do I need a formal star schema for a small number of machines?

Not necessarily at very small scale, but the discipline pays off quickly as machine count and report complexity grow. Starting with reasonable structure early is generally easier than retrofitting it onto an established, informally structured dataset later.

Should raw telemetry and summary metrics live in the same fact table?

Generally not — they have different grains and different query patterns. Separate fact tables (or at least clearly separated layers) for raw telemetry and pre-aggregated summaries tend to serve both use cases better than forcing them together.

How do I handle a machine that gets renamed or reconfigured over time?

This is a "slowly changing dimension" problem in data modeling terms — the machine dimension needs a strategy for either tracking history (preserving old attribute values alongside new ones) or overwriting (losing historical accuracy but simplifying the model), chosen deliberately based on whether historical reporting needs to reflect the machine's state at the time versus its current state.

Who should own the schema design for machine data analytics?

It varies by organization, but it benefits from combined input: someone with data modeling expertise for the technical structure, and someone with operational knowledge of the shop floor to ensure the schema actually reflects meaningful business concepts rather than just technical convenience.

Conclusion

A well-designed schema is what makes machine data genuinely usable for analytics, rather than just technically present in a database somewhere. Separating facts by grain, building out proper dimension tables, and enforcing naming consistency across machines takes more upfront effort than dumping raw data as-is, but it's what makes every downstream report, dashboard, and ad hoc analysis faster to build and more trustworthy once it exists.

Related articles:

• ETL Pipelines for Machine Data
• How to Push Shop Floor Data into a Data Lake
• Integrating Machine Data with Power BI
• Data Quality on the Shop Floor: Common Pitfalls
• MDCplus Machine Connectivity & Integrations

About MDCplus

Our key features are real-time machine monitoring for swift issue resolution, power consumption tracking to promote sustainability, computerized maintenance management to reduce downtime, and vibration diagnostics for predictive maintenance. MDCplus's solutions are tailored for diverse industries, including aerospace, automotive, precision machining, and heavy industry. By delivering actionable insights and fostering seamless integration, we empower manufacturers to boost Overall Equipment Effectiveness (OEE), reduce operational costs, and achieve sustainable growth along with future planning.