The Challenge of Diverse Manufacturing Data

Manufacturing data rarely arrives in a uniform format. Different factories, lines, or vendors can supply similar metrics using wildly different naming conventions and units. For instance, production quantities might appear as 생산수량, units, or made. Temperature readings could be in Celsius or Fahrenheit, and pressure in kPa or bar. Manually coding specific logic for each incoming data source using conditional statements (e.g., if source == ...) quickly leads to a messy, unmanageable pipeline that becomes increasingly difficult to maintain as new sources are added.

This heterogeneity poses a significant hurdle for generating consistent business intelligence and operational insights. Teams need to compare apples to apples, but they are often presented with a fruit salad of varying types, sizes, and ripenesses. The core business requirement remains the same: to understand overall production efficiency, quality, and operational status across the entire manufacturing landscape, regardless of the source's idiosyncrasies.

Introducing the EAV Mini Slice Approach

To tackle this, a method inspired by the manufacturing-data-platform-mini project employs a small-scale Entity-Attribute-Value (EAV) model. This approach focuses on transforming multiple wide CSV files into a standardized, long-format EAV structure. From there, the data is pivoted and aggregated into a unified 'gold metric mart' suitable for analysis.

The EAV model is particularly well-suited for this problem because it decouples the data's meaning from its physical representation. In an EAV system, data is typically stored in three columns: Entity (the item being described, e.g., a specific machine or production run), Attribute (the characteristic of the entity, e.g., 'production quantity' or 'temperature'), and Value (the measurement of that attribute).

This contrasts with the traditional wide format, where each attribute has its own column. While intuitive for single-source reporting, the wide format breaks down when attributes have different names or units across sources. By converting to EAV, we create a flexible structure where each unique metric (like 'production quantity') becomes a single Attribute, regardless of its original column name in the source CSV.

Diagram showing transformation from wide CSVs to EAV long format

Scenario: Unifying Manufacturing Metrics

Consider a scenario where different plants or vendors supply similar manufacturing metrics via CSV files:

plant_a.csv:
  설비ID, 생산수량, 불량수, 온도C, 압력kPa

plant_b.csv:
  machine_id, output_units, defects, temp_f, pressure_bar

vendor_d.csv:
  unit_name, made, scrap, deg_c, kpa

The business goal is to have a single view of production output, defects, temperature, and pressure across all these sources. Without a standardization layer, a BI tool trying to display 'total production' would need complex, source-specific logic. The EAV approach abstracts this complexity.

The Transformation Process

The process involves several key steps:

  1. Mapping Configuration: For each incoming CSV source, a mapping configuration is defined. This configuration specifies how to translate the source's column names and units into standardized attribute names and base units. For example, 생산수량, output_units, and made all map to a standard attribute like production_quantity. Similarly, temperature readings from 온도C, temp_f, and deg_c would all be converted to a common unit (e.g., Celsius) and mapped to a standard attribute like temperature_celsius.
  2. EAV Conversion: Using the mapping configuration, each row in the wide CSVs is transformed into multiple rows in the EAV format. Each row in the EAV table represents a single metric for a specific entity. For instance, a single row in plant_a.csv containing 설비ID='A1', 생산수량=100, 불량수=5, 온도C=30, 압력kPa=100 might be transformed into four EAV rows:
    • Entity='A1', Attribute='production_quantity', Value='100'
    • Entity='A1', Attribute='defects', Value='5'
    • Entity='A1', Attribute='temperature_celsius', Value='30'
    • Entity='A1', Attribute='pressure_kpa', Value='100'
    This conversion is applied to all incoming CSVs, ensuring that the same metric always has the same attribute name and, if applicable, is in the same base unit.
  3. Data Aggregation and Pivoting: Once all data is in the standardized EAV format, it can be aggregated and pivoted to create the 'gold metric mart'. This mart is essentially a wide table optimized for reporting and analysis. For example, one could pivot the EAV data to create a table with columns like date, plant_id, standard_metric_name, and aggregated_value, allowing for easy calculation of total production, average temperature, or defect rates per plant over time.

Benefits and Considerations

This EAV-centric approach offers several advantages. It provides a robust, scalable way to handle data from diverse sources without cluttering the pipeline code with source-specific conditional logic. The mapping configuration acts as a central, declarative definition of data standards, making it easier to update and manage as new sources appear or requirements change. The resulting gold mart is clean, consistent, and ready for direct consumption by analytics tools, dashboards, and machine learning models.

The primary challenge lies in the initial setup of the mapping configurations. This requires a clear understanding of the business metrics and the ability to define canonical attribute names and units. Furthermore, the EAV format itself can be less intuitive for direct human inspection compared to wide tables, necessitating the creation of the aggregated mart for reporting. The synthetic nature of the data used in the mini-slice example highlights that while the methodology is sound, real-world implementation requires careful attention to data privacy and schema details.

Ultimately, this strategy offers a pragmatic solution for organizations drowning in disparate data formats, enabling them to build a unified, reliable data foundation for critical manufacturing insights.