This table holds all assets.
An asset for us is the unique combination of enterprise, site, area, line, workcell & origin_id.
All keys except for id and enterprise are optional.
In our example we have just started our CNC cutter, so it’s unique asset will get inserted into the database.
It already contains some data we inserted before so the new asset will be inserted at id: 8
id
enterprise
site
area
line
workcell
origin_id
1
acme-corporation
2
acme-corporation
new-york
3
acme-corporation
london
north
assembly
4
stark-industries
berlin
south
fabrication
cell-a1
3002
5
stark-industries
tokyo
east
testing
cell-b3
3005
6
stark-industries
paris
west
packaging
cell-c2
3009
7
umh
cologne
office
dev
server1
sensor0
8
cuttingincoperated
cologne
cnc-cutter
work_order
This table holds all work orders.
A work order is a unique combination of external_work_order_id and asset_id.
work_order_id
external_work_order_id
asset_id
product_type_id
quantity
status
start_time
end_time
1
#2475
8
1
100
0
2022-01-01T08:00:00Z
2022-01-01T18:00:00Z
product_type
This table holds all product types.
A product type is a unique combination of external_product_type_id and asset_id.
product_type_id
external_product_type_id
cycleTime
asset_id
1
desk-leg-0112
10.0
8
product
This table holds all products.
product_type_id
product_batch_id
asset_id
start_time
end_time
quantity
bad_quantity
1
batch-n113
8
2022-01-01T08:00:00Z
2022-01-01T08:10:00Z
100
7
shift
This table holds all shifts.
A shift is a unique combination of asset_id and start_time.
shiftId
asset_id
start_time
end_time
1
8
2022-01-01T08:00:00Z
2022-01-01T19:00:00Z
state
This table holds all states.
A state is a unique combination of asset_id and start_time.
stateId
asset_id
start_time
state
1
8
2022-01-01T08:00:00Z
20000
2
8
2022-01-01T08:10:00Z
10000
2 - Historian
How _historian data is stored and can be queried
Our database for the umh.v1 _historian datamodel currently consists of three tables.
These are used for the _historian schema.
We choose this layout to enable easy lookups based on the asset features, while maintaining separation between data and names.
The split into tag & tag_string prevents accidental lookups of the wrong datatype, which might break queries such as aggregations, averages, …
asset
This table holds all assets.
An asset for us is the unique combination of enterprise, site, area, line, workcell & origin_id.
All keys except for id and enterprise are optional.
In our example we have just started our CNC cutter, so it’s unique asset will get inserted into the database.
It already contains some data we inserted before so the new asset will be inserted at id: 8
id
enterprise
site
area
line
workcell
origin_id
1
acme-corporation
2
acme-corporation
new-york
3
acme-corporation
london
north
assembly
4
stark-industries
berlin
south
fabrication
cell-a1
3002
5
stark-industries
tokyo
east
testing
cell-b3
3005
6
stark-industries
paris
west
packaging
cell-c2
3009
7
umh
cologne
office
dev
server1
sensor0
8
cuttingincoperated
cologne
cnc-cutter
tag
This table is a timescale hypertable.
These tables are optimized to contain a large amount of data which is roughly sorted by time.
In our example we send data to umh/v1/cuttingincorperated/cologne/cnc-cutter/_historian/head using the following JSON:
The origin is a placeholder for a later feature, and currently defaults to unknown.
tag_string
This table is the same as tag, but for string data.
Our CNC cutter also emits the G-Code currently processed.
umh/v1/cuttingincorperated/cologne/cnc-cutter/_historian