Documentation
Data Representation Unification
Unification components

Unification components

Formats standardization

All data coming from different sources is converted into a single format.

For example, data from IoT devices comes in JSON format:

example.json
1
2
3
4
5

{
  "device_id": "sensor-123",
  "temp": 27.3,
  "time":  1739960923
}

Data structure unification

Defining a single structure for all data, regardless of its source.

All data is stored in the channel_data table, which associates it with equipment, sessions, channels and units of measurement:

Field Type Description
id bigint Unique record identifier (primary key).
equipment_id bigint Reference to equipment (equipment.id).
seance_id bigint Reference to session (seances.id).
channel_id bigint Reference to channel (channels.id).
unit_id bigint Reference to unit of measurement (units.id).
archive_type_id integer Archive type reference (referenceparameters.id).
event_time integer The time of the event (eg timestamp).
value character varying(100) The value received from the channel.
created_at timestamp(6) without time zone Time the enrty was created.
updated_at timestamp(6) without time zone Time when the entry was last updated.

Where:

  • equipment_id: Unique equipment identifier.
  • seance_id: Unique session identifier.
  • channel_id: Unique channel identifier.
  • unit_id: Unique identifier of the unit of measurement.

When storing data from different devices in a central storage:

  • data is converted into a single format and structure;
  • stored in a unified data table channel_data.

Semantic unification

All data has the same meaning and interpretation in all systems.

For example:

  • All timestamps are in UTC format. This was implemented at the system design stage.
  • All systems use the same units of measurement (e.g. degrees Celsius). To unify units of measurement, the Units model has been added to the system, which stores the units of measurement used for channel data.
Field Type Description
id bigint Unique record identifier (primary key).
name character varying(30) Name of the unit of measurement.
varname character varying(30) Short variable name for the unit of measurement.
description character varying(100) Description of the unit of measurement.
conversion_factor double precision Conversion factor for a unit of measurement.
rounding smallint Number of decimal places to round to.
synonyms character varying[] Array of synonyms for the unit of measurement.
created_at timestamp(6) without time zone Time the enrty was created.
updated_at timestamp(6) without time zone Time when the entry was last updated.

Data normalization

The system data model is reduced to the (third) normal form of Boyce-Codd in terms of atomic (scalar) values. Some entities of the data model use composite structures to optimize work with rare non-standard attributes.

The data is divided into logical tables (equipment, channels, units, sesances), which eliminates redundancy and duplication.

For example:

  • The channel_data table references the equipment, channels, units and sessions tables via foreign keys.

Using unified data

Unified data can be used for analysis, visualization, machine learning and other tasks.

Data Analysis Using SQL 

SELECT equipment_id, AVG(value) as avg_value
FROM channel_data
WHERE archive_type_id = 4 AND channel_id = 3
GROUP BY equipment_id
ORDER BY equipment_id

Requesting data via REST API

Example request:

GET /api/v1/channel_data?equipment_id=1&archive_type=daily&channel_id=3

Example response:

[
  {
    "id": 123,
    "equipment_id": 1,
    "seance_id": 2,
    "channel_id": 3,
    "archive_type_id": 4,
    "value": 42.5,
    "event_time":  1739950861
  }
]
Zuletzt aktualisiert am
Main goalsAdvantages of unification