Example: Accessing Metadata and Value Data

Overview

Note: You must be using the V3 Query Engine for this feature to work.

This guide shows you how to access data that is stored in a dimension and fact table in ADX. This is a popular model choice as it allows for flexibility in the metadata (the dimension table) while allowing the value table (fact table) to grow.

Accessing Metadata and Value Data from Within Seeq

Prerequisites

Data in ADX

This guide will be using similar data to that which was used in Example: Accessing Data from an Entity-Timestamp-Value Data Model; however, we will be adding hierarchy information and signal property information to that data as well. Our data will consist of two tables as shown below:

Metadata (Dimension Table)

DimensionTable.csv

Data (Fact Table)

FactTable.csv

In these tables the Dimension Table (Metadata above) describes the data (Fact Table above). There are also two types of values in the Fact Table, string and double (real in ADX). This will require that that we access the Dimension Table during indexing and the Fact Table during data queries. Because of the separate data columns for string and value data, we will be making two separate table definitions.

Creating the ADX connection

Proceed to the Administration page and click on the blue Add Connection button in the upper right.

From here, you will select an appropriate JVM agent from the drop down list, and then select the ADX connector. You can then select the default Template Connection (ADX Connection) to populate the Additional configuration box with a template for ADX-specific configuration.

For a detailed description of the configuration file please see Azure Data Explorer (ADX) | ADX-Additional-Configuration .

For this connection we are going to use the following configuration:

{
    "RootAssetName": "Dimension Model",
    "GenerateTableAsset": false,
    "Tables": [
        {
            "Id": "String Data",
            "Name": "FactTable",
            "NameQuery": null,
            "GroupBy": [
                "siteId",
                "lineId",
                "machineId",
                "tag"
            ],
            "GroupByLimit": 0,
            "DataColumns": [
                "Value_S"
            ],
            "DataColumnQuery": null,
            "TimeColumn": "TS",
            "LastGroupAsSignalName": true,
            "TransformQuery": null,
            "TimeUnit": null,
            "ComputedGroupByResult": null,
            "GroupByRequestProperties": null,
            "DataRequestProperties": null,
            "MetadataQuery": {
                "TableName": "DimensionTable",
                "Filters": "where valuetype == \"string\"",
                "Columns": [
                    "interpolation",
                    "unit",
                    "siteId",
                    "lineId",
                    "machineId",
                    "tag",
                    "tagId"
                ],
                "UniqueKeyColumnName": "tagId"
            },
            "SignalPropertiesMap": {
                "Interpolation Method": "interpolation",
                "Description": "machineId"
            }
        },
        {
            "Id": "Numeric Data",
            "Name": "FactTable",
            "NameQuery": null,
            "GroupBy": [
                "siteId",
                "lineId",
                "machineId",
                "tag"
            ],
            "GroupByLimit": 0,
            "DataColumns": [
                "Value_D"
            ],
            "DataColumnQuery": null,
            "TimeColumn": "TS",
            "LastGroupAsSignalName": true,
            "TransformQuery": null,
            "TimeUnit": null,
            "ComputedGroupByResult": null,
            "GroupByRequestProperties": null,
            "DataRequestProperties": null,
            "MetadataQuery": {
                "TableName": "DimensionTable",
                "Filters": "where valuetype == \"numeric\"",
                "Columns": [
                    "interpolation",
                    "unit",
                    "siteId",
                    "lineId",
                    "machineId",
                    "tag",
                    "tagId"
                ],
                "UniqueKeyColumnName": "tagId"
            },
            "SignalPropertiesMap": {
                "Value Unit Of Measure": "unit",
                "Interpolation Method": "interpolation",
                "Description": "machineId"
            }
        }
    ],
    "ApplicationId": "<example application Id>",
    "AccessKey": "<example access Key>",
    "TenantId": "<example tenant ID>",
    "UserName": null,
    "UserPassword": null,
    "Cluster": "https://<cluster name>.<cluster region>.kusto.windows.net",
    "Database": "Documentation"
}

Make sure to replace the Cluster, ApplicationId, AccessKey, and TenantId with appropriate values. Once this configuration is saved, the connection will automatically index and show something similar to the image below.

MetadataQuery

In this example we are indexing the signals using the information within the Metadata Query specifically in the section called out below:

"MetadataQuery": {
                "TableName": "DimensionTable",
                "Filters": "where valuetype == \"numeric\"",
                "Columns": [
                    "interpolation",
                    "unit",
                    "siteId",
                    "lineId",
                    "machineId",
                    "tag",
                    "tagId"
                ],
                "UniqueKeyColumnName": "tagId"
            },

This subsection of the configuration section is sub-selecting the data based on its value type. Because the Dimension Table contains information about the value type, we are able to create a filter to only return the signals that are numeric. This means that only the properties will be returned for numeric values. This means for all the signals we are indexing, we know which column from the Fact Table to get the value from. This is why the DataColumn is defined as [ "Value_D" ]. We are also specifying all of the columns that we will need from this table. Some of these columns will be GroupBy information required to build the Asset Tree in Seeq, some of these columns will be signal properties, and one of these columns will be the UniqueKeyColumnName.

Unique Key Column Name

The next item is the UniqueKeyColumnName this column is used as the foreign key between the dimension and fact table. This property can only have a single column name, if you have a composite key, you must create a stored function within ADX to project a single column. The value for the UniqueKeyColumnName can also appear in the GroupBy if it is guaranteed to be unique within the Fact and Dimension tables.

Signal Property Map

The signal property map is also used to identify what columns should be used for signal properties. The signal properties subsection is shown below:

"SignalPropertiesMap": {
                "Value Unit Of Measure": "unit",
                "Interpolation Method": "interpolation",
                "Description": "machineId"
            }

This is defining three properties:

• The value unit of measure

• The interpolation method

• The Description

The format is Seeq Property Name : Column Name. The column name MUST be included in the columns property of the MetadataQuery section. This allows the value in the column from ADX to be stored in the property of the Signal in Seeq. These can be dynamic properties as well that act as additional information you would like to include with the signal, such as machine type, sensor type, lineage information, or other tracking information. In the example shown to the right, this is one of the signals that was indexed from the DimensionTable above. It contains information such as the Database Name, Table Name, Value Column, Time Column, Filters used, the unit of measure, and the Interpolation Method. Some of these are automatically created by the connector:

• Data Type - the data type in ADX

• Database - the database in ADX

• Filters - The filter used for the data query

• Table - The table in ADX

• Time Column - The time column used in the ADX table

• Value Column - The value column that the data was pulled from

The others were mapped from the Signal Property map:

• Value unit of measure

• Interpolation method

Asset Tree Creation

The MetadataQuery section will also define the columns required by the group by.

"GroupBy": [
                "siteId",
                "lineId",
                "machineId",
                "tag"
            ],

These columns MUST appear in the columns list from the MetadataQuery. The value for the UniqueKeyColumnName can also appear in the GroupBy if it is guaranteed to be unique within the Fact and Dimension tables.

Even though there are two table definitions, we will index all string and numeric signals to one tree, the will not be separated by table definition.

Multiple options can be selected.