Adding Context to IoT Data Just Became Easier

Microsoft

Jun 22, 2021

The growing number of devices connecting to the cloud is producing treasure troves of information. This information can be used to not only understand the state of a device, but the state of an entire environment such as a building, energy grid, or factory. However, this vast amount of data can produce its own challenges. As each device generates a time series stream that is often labeled with an encoded tag, it can be hard to understand how different streams are related, or how an overall environment is behaving.

This is where Azure Digital Twins comes in. Azure Digital Twins enables you to create digital models of assets, places, people, and processes, and then relate these items based on their real-world relationships. Now, you can use the Azure Digital Twins plugin for Azure Data Explorer to combine the digital models of your environment with time series data from your devices. More specifically, you can use the plugin to contextualize disparate time series data in ADX by reasoning across digital twins and their relationships to gain insights into the behavior of your modeled environments over time.

For example, with the new Azure Digital Twins plugin for ADX, you can write a query that...

selects digital twins of interest via the Azure Digital Twins query plugin, then
joins those twins against the respective times series data in ADX, and then
performs advanced time series analytics on those twins.

To get you up and running with the new plugin, we’ve created an example scenario where you will combine data in a sample twin graph in Azure Digital Twins with sample time series data in ADX. More specifically, you’ll use joint Azure Digital Twins/ADX queries to understand the operational behavior of various portions of a power distribution grid.

Create a twin graph of an energy grid using Azure Digital Twins Explorer

To get started, create an Azure Digital Twins instance using the CLI commands below or via the Azure portal. Copy the instance's URL once it is online.

az account set --subscription <your-subscription-ID> 
az dt create -n <instance-name> -g <resource-group>

Note: Ensure the resource group exists.  

Grant yourself the Azure Digital Twins Data Owner role on the instance. It may take up to five minutes for this RBAC change to apply.

az dt role-assignment create -n <instance-name> --assignee "<owneruser@microsoft.com>" --role "Azure Digital Twins Data Owner"

Download the sample models and graph from the energy-grid-example folder to your local computer. You can do this by cloning this repository or downloading it as a ZIP, and then navigating to the energy-grid-example folder on your machine.
Open Azure Digital Twins Explorer. It should prompt you to enter your Azure Digital Twins instance URL. If not, click on the globe icon in the top right corner.
Enter your Azure Digital Twins instance URL (endpoint must start with 'https://').
On the left pane, click on the Upload a Model button (cloud icon with upward arrow).

i. Go to the folder where you downloaded the repository. Navigate into /model/energy-grid-example.

ii. Select all the JSON files (models) and upload them.

In the middle pane, click the Import Graph button (also a cloud icon with upward arrow).

i. Within /model/energy-grid-example, select distributionGrid.xlsx to import it.

You should see a graph preview. Click on the floppy disk icon to finish importing the graph (this creates twins and relationships).
Navigate back to the Twin Graph tab. In the query section, type in "SELECT * FROM DIGITALTWINS" and select Run Query. You should see the graph displayed in the Twin Graph window.
Click on one of the leaf nodes that has this model: dtmi:example:grid:plants:domesticConsumer;1.
Update the PowerRequirement value to 500 and save it (using the disc button circled in red below) to patch the twin.

Run joint Azure Digital Twins/ADX queries to understand the behavior of the energy grid

Connect to the Samples database on the public ADX help cluster. The database features the table SamplePowerRequirementHistorizedData that contains historized property values over a one month period for selected digital twins in the energy grid.
Get the list of example queries in the sample-queries.kql file. Copy and paste each query into ADX. To run a query, click on the query to highlight it and press the Run button. In each query, replace <your_ADT_endpoint> with your instance's endpoint. Your endpoint should be in quotes, start with https://, and be followed by a semicolon. For example:

let ADTendpoint = "https://MyExampleADTinstance.api.wcus.digitaltwins.azure.net";

Run the below query, which will invoke the plugin and return all twins in the energy grid.

//Get all twins in your Azure Digital Twins instance
let ADTendpoint = <your_ADT_endpoint>;
let ADTquery = "SELECT T FROM DIGITALTWINS T";
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)

Now that you’ve used the plugin to run an Azure Digital Twins query from ADX, run the next query to retrieve the IDs of the digital twins that are fed power by the Fall Street Substation. The Azure Digital Twins plugin query selects these twins based on those that have a ‘feeds’ relationship with that substation.

// Get the twin ID of all consumers fed by the Fall Street substation
let ADTendpoint = <your_ADT_endpoint>;
let ADTquery = "SELECT CSMR.$dtId as tid FROM DIGITALTWINS SUB JOIN CSMR RELATED SUB.feeds WHERE SUB.$dtId = 'sub_fall_street' AND IS_PRIMITIVE(CSMR.$dtId)";
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)

Now that you have selected the twins of interest from your twin-graph, you can join the twin IDs against the respective time series in ADX and perform time-series analytics. For example, the below query calculates the average power usage on an hourly basis for all customers fed by the Fall Street substation during week 10.

// Chart the average hourly power usage of consumers fed by the Fall Street substation ('sub_fall_street') during week 10
let ADTendpoint = <your_ADT_endpoint>;
let ADTquery = "SELECT CSMR.$dtId as tid FROM DIGITALTWINS SUB JOIN CSMR RELATED SUB.feeds WHERE SUB.$dtId = 'sub_fall_street' AND IS_PRIMITIVE(CSMR.$dtId)";
let weekNumber = 10;
let startDate = datetime_add('week',weekNumber - 1, make_datetime(2021,1,1));
let endDate = datetime_add('week',1, startDate);
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend twinId = tostring(tid)
| join kind=inner (SamplePowerRequirementHistorizedData) on twinId
| project timestamp, twinId, value
| where timestamp between (startDate .. endDate)
| summarize avg(value) by bin(timestamp,1h),twinId
| render timechart

This last example selects the twins that feed power into the solar grid, identifies missing time series samples, fills in the missing samples with interpolated data points, and then identifies anomalies in the time series for each twin.

// Identify any anomalies in the data
let ADTendpoint = <your_ADT_endpoint>;
let ADTquery = "SELECT PLANT.$dtId as tid FROM DIGITALTWINS PLANT JOIN GRID RELATED PLANT.feeds WHERE GRID.$dtId = 'pl_solar_gen' AND IS_PRIMITIVE(PLANT.$dtId)";
let weekNumber = 10;
let startDate = datetime_add('week',weekNumber - 1, make_datetime(2021,1,1));
let endDate = datetime_add('week',1, startDate);
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend twinId = tostring(tid)
| join kind=inner (SamplePowerRequirementHistorizedData) on twinId
| project timestamp, twinId, value
| where timestamp between (startDate .. endDate)
| make-series AvgPower=avg(value) on timestamp step 10m by twinId
| extend NoGapsTemp=series_fill_linear(AvgPower)
| project timestamp, NoGapsTemp, twinId
| extend anomalies = series_decompose_anomalies(NoGapsTemp,4)
| render anomalychart with(anomalycolumns=anomalies)

Want even more fun? Build an ADX dashboard with charts from your new Azure Digital Twins/ADX queries! ADX also offers integrations with third party visualization packages such as Power BI, Grafana, Tableau and others.