Wednesday, September 28, 2022
HomeIoTCreate insights by contextualizing industrial tools information utilizing AWS IoT SiteWise (Half...

Create insights by contextualizing industrial tools information utilizing AWS IoT SiteWise (Half 2)

Within the first a part of this weblog sequence Create insights by contextualizing industrial tools information utilizing AWS IoT SiteWise (Half 1) we targeted on asset modelling and real-time analytics in AWS IoT SiteWise. We created a dashboard in AWS IoT SiteWise Monitor to get a real-time overview of our furnace heating cycles. However we concluded {that a} extra in-depth evaluation was wanted to search out the foundation reason behind the irregular heating cycle of Furnace1. On this second a part of the weblog, we are going to present how clients can use the AWS IoT SiteWise chilly tier storage function to export the uncooked, aggregated and meta-data to AWS IoT Analytics for additional evaluation.

Allow AWS IoT SiteWise chilly tier storage and AWS IoT Analytics export

AWS IoT SiteWise chilly tier storage function makes it simple to eat historic information in downstream AWS analytic companies. Moreover, it’s going to additionally decrease your storage value on AWS IoT SiteWise by exporting historic information to Amazon S3. Buyer can freely outline how lengthy the information might be stored within the time-series optimized AWS IoT SiteWise information earlier than being exported into S3 by setting a knowledge retention threshold.

Enabling AWS IoT SiteWise chilly tier storage

To allow AWS IoT SiteWise S3 export, open the AWS IoT SiteWise console, select Settings, Storage, Edit within the navigation pane and test Allow Chilly tier storage,

SiteWise Storage Tiers

Enter an present S3 bucket location in the identical AWS area,

SiteWise Storage Tiers S3

Verify Allow AWS IoT Analytics information retailer, kind iotsitewise because the Information retailer identify and select Save

SiteWise Storage Tiers IoT Analytics

For this stroll by way of we are going to use AWS IoT Analytics to question the information and visualize it in Amazon QuickSight.
The AWS IoT SiteWise S3 export function exports data on asset properties from the asset mannequin into the asset-metadata S3 prefix when the mannequin change. As soon as the standing of the S3 export is enabled, it is best to see a line delimited JSON file per asset in your S3 bucket. The uncooked information is exported each 6 hours and might be positioned into the uncooked S3 prefix. For extra particulars on the export format and placement, see File paths and schemas of information saved within the chilly tier.

Create an AWS IoT Analytics dataset to investigate the uncooked information

We will now begin to analyze the AWS IoT SiteWise exported information with AWS IoT Analytics. Open the AWS IoT Analytics console, select Datasets within the navigation menu and select Create dataset, Create SQL. This may open a wizard that can information you thru the Dataset creation. On the primary display identify your information set and select the iotsitewise information retailer that was created by the AWS IoT SiteWise chilly tier export wizard.

IoT Analytics Dataset Name

Select Subsequent to open the Writer SQL question dialog, copy and previous the pattern question under.

The question used some superior Athena SQL options and in addition demonstrated how AWS IoT Analytics Dataset queries can be utilized to affix information from totally different AWS IoT Analytics information shops. For this evaluation, we need to question the metric Final Holding Cycle Time for all our belongings within the present month. To perform this, the question begins from the uncooked information retailer filtered by a particular month. It joins the asset_metadata information retailer to retrieve the property metadata just like the asset identify. Lastly, it joins the asset_metadata once more, however this time grouped by asset ID. This final JOIN assertion retrieves all static attribute of the corresponding AWS IoT SiteWise asset and provides it to the outcome row. This information is essential for our evaluation, as a result of we are going to use it in our final step as dimensional information.

    from_unixtime(information.timeinseconds + (information.offsetinnanos / 1000000000)) ts, 
    metadata.assetname,  metadata.assetpropertyname, metadata.assetpropertydatatype, 
    latesValue['Location'] as Location , latesValue['Manufacturer'] as Producer, 
    latesValue['YearOfConstruction'] as YearOfConstruction, latesValue['Setpoint'] as Setpoint
FROM iotsitewise.uncooked  as information
-- Be part of the meta information desk 
INNER JOIN iotsitewise.asset_metadata as metadata
ON information.seriesid = metadata.timeseriesid
-- Be part of sub question that retrieves all asset attributes and newest values    
  SELECT assetid, map_agg(assetpropertyname, latestvalue) latesValue from (
    SELECT assetid,  assetpropertyid, assetpropertyname,
            coalesce (
            max_by(information.stringvalue, information.timeinseconds),
            max_by(solid(information.integervalue as VARCHAR), information.timeinseconds),
            max_by(solid(information.doublevalue as  VARCHAR), information.timeinseconds)
            ) latestvalue
    FROM iotsitewise.uncooked information  
    INNER JOIN iotsitewise.asset_metadata metadata
    ON information.seriesid = metadata.timeseriesid
    GROUP BY assetid, assetpropertyid, assetpropertyname) 
  GROUP BY assetid) as dim
ON metadata.assetid = dim.assetid
WHERE information.startyear = yr(current_date)
AND information.startmonth = month(current_date)
AND metadata.assetpropertyname="Final Holding Cycle Time"

To check the question, select Check question. If the question accommodates no syntax errors, it is best to see a preview of the information within the End result preview part.

IoT Analytics Test Query

Depart the remainder of the steps 3-6 with the default worth by selecting Subsequent and select Create dataset on the final evaluate step 7.

To validate if every thing is accurately setup, navigate to your newly created dataset

IoT Analytics Dataset Test

Select Run now and wait till the outcome content material seems on the Content material tab with Succeeded. Once you select the End result hyperlink, the console reveals you a preview of the question outcome:

IoT Analytics Dataset Result

The outcome reveals the Final Holding Cycle Time metric by time. The question additionally added the AWS IoT SiteWise mannequin data, just like the asset identify and mannequin identify, and the asset attribute values to every row. Such flattened information rows make it simpler to investigate the information in BI instruments. Within the subsequent step we are going to use Amazon QuickSight to investigate the IoT Analytics dataset.

Analyze the lead to Amazon QuickSight

As a closing step, we are going to analyze the information in Amazon QuickSight. Amazon QuickSight comes with a built-in connector for AWS IoT Analytics, so it’s simple to visualise the information.
Open the Amazon QuickSight console and selected New Evaluation, when prompted for a knowledge sources, create a brand new one by selecting New Dataset. Select AWS IoT Analytics and choose the SiteWise AWS IoT Analytics dataset holdingcycletimereport we created within the earlier step. To create the information supply select Create Information Supply.

QuickSight Create Analysis

Select Visualize to begin utilizing Amazon QuickSight Visible Sorts to show the information set.

QuickSight Visualize

On this particular use case, we wish perceive how the Manufacture and the Building 12 months influences the HOLDING cycle length, the Amazon QuickSight warmth map is an effective selection to visualise this information.

QuickSigth Heat Map

And from this view, we will clearly establish that the furnaces manufactured by Furnace Corp in 1999 have the longest cycle time (>=88) and should be prioritized for substitute.


This concludes the 2 half weblog sequence on use AWS IoT SiteWise and AWS IoT Analytics to contextualize your industrial tools information. We began by ingesting uncooked time sequence information into AWS IoT SiteWise. Subsequent, we used the AWS IoT SiteWise asset mannequin so as to add context in regards to the industrial tools that produced the time sequence information. Lastly, we demonstrated use dataset queries in AWS IoT Analytics to mix the time sequence information factors and context information right into a flattened format that’s simple to eat in BI instruments like Amazon QuickSights.

In regards to the writer

Jan Borch is a Principal Specialist Resolution Architect for IoT at Amazon Internet Providers (AWS) and spent the final 10 years serving to clients design and construct best-in-class cloud options on AWS. The final 5 years, he targeted on the intersection of Cloud and IoT, main the AWS IoT Prototyping Crew to co-develop modern related IoT options with AWS clients in Europe, Center East and Africa and not too long ago his focus shifted to clients with strategic IoT workloads on AWS.



Please enter your comment!
Please enter your name here

Most Popular