Database Tables

Database Tables

The Azure Data Explorer contains at least one database per environment. Each database contains a predefined schema of Tables.

The Tables of the database are crucial for exploring and analyzing your data. This section provides an overview of the most commonly used Tables to help you get started.

You can view the database structure by expanding the nodes in the Database Explorer. Among the available tables, the most relevant ones include:

• AssetHierarchy: This Table contains information from Asset Hierarchies, that has been deployed to a Device. The Table details the organizational structure of deployed assets, featuring the latest version of each node— whether it is an Area or an Asset within any Asset Hierarchy.

• AssetHierarchyMetadata: This Table contains metadata information from Asset Hierarchies, that has been deployed to a device. The Table provides metadata for all Area/Assets and Tags that has been configured for Asset hierarchies.

• Measurements: Stores time-series measurements and events associated with your assets. The Table contains the actual metrics that has been collected from your devices.

AssetHierarchy Table

This Table contains information from Asset Hierarchies, that has been deployed to a Device. The Table details the organizational structure of deployed assets, featuring the latest version of each node— whether it is an Area or an Asset within any Asset Hierarchy.

Running this query, lets you get a list of latest available AssetHierarchies in the Database.

AssetHierarchy
| distinct HierarchyName, HierarchyId, HierarchyVersion
| order by HierarchyName asc

Beneath is an example result of running the query

Now that we can see all available Asset Hierarchies in the Table, we can now refine our search, to only show all Nodes from a specific Asset Hierarchy. Running the following query, will show the Asset Hierarchy nodes for the Asset Hierarchy called "Odense Factory".

The Query only includes some of the available columns using the project operator.

AssetHierarchy
| where HierarchyName == "Odense Factory"
| project HierarchicalName, Type, Description, IsDeployed, DeploymentTimestamp, DeviceId
| order by HierarchicalName asc

Here is an example result of running the query

Notice that by ordering the query result by HierarchicalName displays the result exactly as it was Modelled in Tricloud Nexus in that version. Also notice that you can see whether a Node has been deployed, when it was deployed and the device that was targeted for the deployment.

AssetHierarchyMetadata Table

This Table contains metadata information from Asset Hierarchies, that has been deployed to a device. The Table provides metadata for all Area/Assets and Tags that has been configured for Asset hierarchies.

All metadata for an Area/Asset or Tag that has been deployed to a device, can found by running the query beneath. The query displays all metadata Key/Value objects for all available Asset Hierarchies. It removes some less important columns from the result (Id, IngestionTime, DataType). It then orders the result by the Type of metadata.

AssetHierarchyMetadata
| project-away Id, IngestionTime, DataType
| order by Type asc

Here is an example result of running the query

Notice that the first 2 rows are metadata about an Area that seemingly sets a GPS coordinate for the Area. The rest of the rows are metadata about a Tag such as uom (Unit Of Measure), description, ranges etc.

You can combine an entire AssetHierarchy with the available metadata for all Area/Assets or Tags into a single query by joining the AssetHierarchy and AssetHierarchyMetadata Tables. The query combines all metadata available for a specific node into a json document (Key/Value) and stores this in the Metadata column.

AssetHierarchy
| join kind=leftouter AssetHierarchyMetadata on Id
| where HierarchyName == "Dallas Factory"
| project Id, Name, HierarchicalName, MetadataKey = Key, MetadataValue =  Value, DeploymentTimestamp
| summarize Metadata = make_bag(pack(MetadataKey, MetadataValue)) by HierarchicalName
| order by HierarchicalName asc

Result of running Query

Measurement Table

The Measurement Table stores time-series measurements and events associated with your assets. The Table contains the actual metrics that has been collected from the devices.

The following Query, will find all available metrics/measurements that has a StartTimestamp between 17. the dec to 20. the dec. (UTC) for the Tag with the HierarchicalName OD.Printing.Line.Temperature, then order the result by StartTimestamp descending:

Measurements
| where StartTimestamp between (datetime('2024-12-17T00:00:00') .. datetime('2024-12-20T00:00:00'))
| where HierarchicalName contains "OD.Printing.Line.Temperature"
| order by StartTimestamp desc

Result of running the Query

The result shows the structure of the Measurement Table, the Table has the following Columns:

• Id - The Id of the Tag that governs measurements

• HierarcicalName - ISA95 name for the Tag that governs the measurements

• TagName - Shorthanded name of the Measurements

• TimeGenerated - The UTC time the Measurement was generated at the Data Collector

• StartTimestamp - The UTC start time of the Measurement

• EndTimestamp - The UTC end time of the Measurement. Many measurements does not have an endtime specifed, denoting that the measurement does not have timespan.

• Type - Measurement type. Can be either Analog, Digital or String. The Type depends on Tag type that was configured in the Asset Hierarchy.

• Value - The value of the measurement as a real data type. This will likely only be set for measurements of type Analog or Digital.

• ValueDigital - The value of the measurement as a boolean data type. This will likely only be set for measurements of type Digital and Analog. The database will automatically try and convert the value of the measurement to a boolean, by converting 1 to true and 0 to false.

• ValueString - The value of the measurement as a string data type. This will likely only be set for measurements of type String. This is a very flexible data type and can be used for metrics that carries a complex data type such as Json.

• Quality - The quality of the measurement. The quality of each measurement is set by the data collector on the device, that is used to collect the measurement from the destination data source. If a given measurement has a quality other than the value good, it is not recommended to use the measurements for training an AI model.