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

Within the first a part of this weblog collection Create insights by contextualizing industrial tools knowledge utilizing AWS IoT SiteWise (Half 1) we centered 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 seek out the foundation reason for 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 characteristic 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 characteristic makes it simple to eat historic knowledge in downstream AWS analytic companies. Moreover, it should additionally decrease your storage value on AWS IoT SiteWise by exporting historic knowledge to Amazon S3. Buyer can freely outline how lengthy the information can be stored within the time-series optimized AWS IoT SiteWise knowledge 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 examine Allow Chilly tier storage,

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

Examine Allow AWS IoT Analytics knowledge retailer, sort iotsitewise because the Information retailer title and select Save

For this stroll by we are going to use AWS IoT Analytics to question the information and visualize it in Amazon QuickSight.
The AWS IoT SiteWise S3 export characteristic exports info 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’s best to see a line delimited JSON file per asset in your S3 bucket. The uncooked knowledge is exported each 6 hours and can 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 knowledge

We will now begin to analyze the AWS IoT SiteWise exported knowledge 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 title your knowledge set and select the iotsitewise knowledge retailer that was created by the AWS IoT SiteWise chilly tier export wizard.

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 knowledge from totally different AWS IoT Analytics knowledge shops. For this evaluation, we need to question the metric Final Holding Cycle Time for all our property within the present month. To perform this, the question begins from the uncooked knowledge retailer filtered by a selected month. It joins the asset_metadata knowledge retailer to retrieve the property metadata just like the asset title. 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 end result row. This knowledge is essential for our evaluation, as a result of we are going to use it in our final step as dimensional knowledge.

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

To check the question, select Take a look at question. If the question comprises no syntax errors, it’s best to see a preview of the information within the End result preview part.

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

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

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

The end result exhibits the Final Holding Cycle Time metric by time. The question additionally added the AWS IoT SiteWise mannequin info, just like the asset title and mannequin title, and the asset attribute values to every row. Such flattened knowledge 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.

Select Visualize to start out utilizing Amazon QuickSight Visible Varieties to show the information set.

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

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

Conclusion

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

Concerning the writer

Jan Borch is a Principal Specialist Resolution Architect for IoT at Amazon Internet Companies (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 centered on the intersection of Cloud and IoT, main the AWS IoT Prototyping Workforce to co-develop modern related IoT options with AWS clients in Europe, Center East and Africa and just lately his point of interest shifted to clients with strategic IoT workloads on AWS.