>> Putting together OT and IT data and making sense of that,
adding some analytics to detect, for example,
anomalies when a customer reports an anomaly on a product,
that's something that could look cumbersome and complicated.
Taylor is here today on
the IoT Show to tell you exactly how you can do,
where I can do all that with Azure Digital Twins,
Azure Data Explorer, and Synapse Analytics.
Don't miss that episode of the IoT Show today.
[MUSIC]
>> Hi, everyone. Thanks for joining the IoT Show.
I'm Olivier your host,
and today we'll talk about data
and more specifically we'll talk about
reconciling data from the OT world into the IT world.
For that, we have Taylor. Taylor, how are you?
>> Good. How are you, Olivier?
>> I'm great, I'm fantastic.
People might not know you, if they have not
seen previous episodes about Azure Digital Twins with you.
We'll definitely invite them to go watch these previous episodes.
But still who is Taylor and what are you doing at Microsoft?
>> Sure. My name is Taylor Anderson and I'm
a Product Manager with the Azure Digital Twins team.
I focus on building integrations between
Azure Digital Twins and other services like Azure Data Explorer.
I also focus on ensuring the needs
of our manufacturing customers are reflected in our roadmap.
>> One of the things that customers
are asking for when it comes to
IoT is to reconcile data from the world of OT,
from the world of operations what we call sometimes IoT actually,
and the world of IT.
The data come from CRM, ERP, and so on.
But can you give us some more clarification around OT and IT,
it's a high level that I provided here,
but can you tell me more about this differentiation here?
>> Absolutely, and I've got a slide actually covering this topic.
When we talk about OT data,
we're really talking about IoT data or
data that's coming from sensors or devices,
for example, on a manufacturing line
or a manufacturing execution system.
When we're talking about IT data,
a lot of times we're talking about data that's coming
from an ERP or CRM system,
something like dynamics or SAP or salesforce.
The challenge is that a lot of times this data
lives in fundamentally different systems
so it's effectively siloed.
A core challenge that we hear from customers is that they need to
reason across both of these data sets to
answer big picture business questions
like how to improve efficiency or reduce defects.
>> Got it. I just understood right now,
the silo pun right here.
Love it, perfect.
Thanks, Taylor. That definitely clarifies it,
different silos and you need to reconcile that.
How do you propose to do that?
>> Well, on this next slide here we actually have a data flow
that shows the solution that we put together here.
We're going to have a demo of this in a minute.
But what we have here is we have on the right-hand side
an Azure Synapse workspace and we can
ingest IT and OT data into
this Synapse workspace where we
can combine it and reason across it.
If we look at these different data flows here we've
got ERP data flowing into
our Synapse workspace and Synapse offers
connections with a variety of popular ERP and CRM packages.
We've got our manufacturing execution system data flowing into
the Synapse workspace and then we also have telemetry
coming from our manufacturing line that's
flowing through Azure Digital Twins where it's
contextualized and then flowing into
Azure Data Explorer using the data history feature.
We're actually historizing all
of the updates to Azure Digital Twins.
We have a complete historical record
for all of the Digital Twins and
then we can combine and
reason across all this data within Synapse.
>> I love it. Let's try and make it a bit more real here.
Tell me about a use case,
I know you prepared a use case that explains all of that,
because that's a lot of bits and pieces put together.
I want to understand that better.
>> Imagine Alice, she's
a Production Manager at Qantos Inc and she gets
a complaint regarding a batch
of widgets that were shipped last week.
She needs to do an investigation to
understand the cause for the quality issue.
To do that, she has a purchase order number.
But to perform the investigation,
she needs to reason across
not only the purchase order information but also
the production information so she can
understand where this order was fulfilled,
where it was manufactured, when was manufactured,
and then even drill down further
to look at the manufacturing line and
the stations on that line
that were responsible for fulfilling that order.
She wants to be able to look at the telemetry emitted by
the very stations down the line
that manufactured the widgets that were sent to the customer.
>> How does that look like from the data perspective?
If I'm a data person,
I want to see diagrams with database and so on. There you go.
>> Here's the overall data model for our example scenario.
We're starting off on the upper left corner
where we have a purchase order ID,
and if we have a purchase order,
then we can then look that purchase order up in
the purchase order table which comes to us
from SAP in this example we're going to see.
Once we look up that purchase order,
we can then look up the plant code.
The plant where that purchase order was fulfilled and then we
can look up that particular production schedule.
Once we have the production schedule for the purchase order,
we can then look up the manufacturing line.
At that point, that leads us
into our Azure Digital Twins block here and we
have the plant code and
the manufacturing line so we can run
a query in Azure Digital Twins.
Then we'll look up the stations on the line that were
responsible for manufacturing the widgets and then, of course,
because we're using the data history feature we actually have
the time series telemetry associated with those stations
during that manufacturing period which then allows us to do things
like time series analytics on
the stations that were responsible
for fulfilling that purchase order.
>> Got it, I see.
Basically getting data from one source,
getting data from another source,
getting data from your operations,
the IoT sensors and devices,
asset tracking, and so on.
Putting all of that into Digital Twins to
make sense of that data together,
and then using ADX to explore
the data and its history in a nutshell.
>> Exactly. I have the example and we can walk through that.
>> Let's walk through the actual demo.
>> Great. I'm going to jump over to my web browser.
Now I'm in Azure Synapse Analytics.
I've created a Synapse workspace
here and I've got a few tabs here.
I've set all these tabs up beforehand,
but the first tab we're going to look at here is a Pipeline.
What this Pipeline does is it takes the table and
copies it into a file, and it does that.
I can do that by defining the source for my Pipeline.
You can see here I've defined
an SAP Table Resource, this is the link service.
I'm going to copy a specific table
from SAP and then I'm only going to
copy the fields I care about and then I also define the same.
In this case here, the sink is going to be a JSON table that lives
within a storage account that's
accessible to my Synapse workspace.
This is the pipeline for moving a table in from my ERP,
in this case, it happens to be SAP.
>> What is the data you're retrieving?
Is it the purchase orders?
>> Yeah, a great question.
The next tab here actually shows what we're copying over.
This is a SQL query that I wrote and
all this query does is now that we've
copied this table into a JSON file,
it copies the contents of that JSON file into
a view within my nerveless SQL pool.
You can see below after it copies it into a view,
it selects a few rows.
We can see here in this table
the first column is actually the purchase order ID,
and then the next column is the plant code and
then some other columns as well in my purchase order table.
>> Now you have data from SAP.
Now you need to have the production order data, right?
>> Exactly. This next script here,
all this does is it reaches out and it copies CSV files which
are the various production order schedules
from all the different factories it can toss away.
In this case here,
we're looking at the production schedule for plant
3,200 and we can go back in time and we can
see the various purchase orders
that were fulfilled by a production order.
We can also see the production line in
the plant code associated with that production order.
>> Okay.
>> Yeah.
>> Cool. Now you have these two tables
and you need to reconcile all that, right?
>> Yeah, right. What we can do is we can
effectively join these two tables and I have
a sample query here and this is for a given purchase order ID,
but all this does is it effectively says, hey,
we've got this purchase order,
then it resolves the plant where it was fulfilled.
Looks up the production schedules
we saw earlier and then provides these results.
We can see for a purchase order three million and forty six was
fulfilled by this production order of
production line 2 in plant 3,200th unit.
>> Cool. Now Alice knows where that order should be fulfilled.
Now she can dig into the data
from the factory floor to know where things are at, right?
>> Exactly. As we saw a little bit earlier,
Alice is modeled for
various production lines in Azure Digital Twins.
Now I'm jumping over to Azure Digital Twins Explorer,
which allows us to visualize the Twin graph.
We can see here plant 3,200,
we have production lines 1,
production line 2, and production line 3.
Now that Alice knows the production line in the plant,
she can effectively run a query to find out which Twins.
>> Are on that line.
So I'm effectively running a query that says, hey,
we're interested in all twins in plane 3200 on line two.
We can see here we've got a robot,
I'm going to organizes these a little more neatly,
a CNC machine that feeds a conveyor belt that feeds a robot.
We now know the stations on
the line that we're responsible for showing this purchase order.
>> Now we need to put all that together and detect
the anomaly based on these historical data,
so how do you do that from here?
>> As we mentioned earlier,
we're using the data history feature,
which means every time we update these twins,
we're going to histories those twin
updates to Azure Data Explorer,
so we have a historical record of everything
these twins did in the past.
I'm going to jump over here and
now I'm looking in the block client for
Azure Data Explorer here and
what I've done is this is the data history table,
I went out and loaded some records earlier,
but we can see that in this data history table,
this is basically time series records for our twins,
we have the timestamp,
we have the twin ID,
we have a model ID and then a key value pair
where the key value pair
simply represents the twin property that changed over time.
In this case here, what we can do is we can say, hey,
in our data history table we know
a time range when the production order
occurred and we'll start off by looking at
the CNC twin on the manufacturing line.
This is one of the three twins we identified online
two and we can just run this query here and
we can see that something strange happened on
the day when that purchase order was fulfilled in Plant 3200.
It looks like we had a spike in
both fluid consumption as well as energy consumption.
>> Then you can determine that there's been some hiccup
on the machinery here that generated the problem
on the product and you're
basically connecting the dots between what the customer sees as,
hey, my product is defect all the way down to exactly when
the problem occurred and where it coming from
and so you can sort it out from there right?
>> Exactly, and what we can do is as we go through this example,
we've had a bunch of queries that represent
each step in that data model we saw earlier.
If Alice has this problem come
up again or someone else wants to investigate it,
what she can do is effectively create a dashboard.
>> Yeah, I was about to ask because it's not your salesperson or
even the operator of the machinery or
the maintenance person who is
going to go through the data like you just did.
So you're basically using
Synapse ADT and ADX to create the queries,
but at the end of the day, what you really want is an output,
which is a dashboard that is consumable by different people.
>> Exactly, so here we can build, for example,
a dropdown menu where we can select
a purchase order number and then run all
of these queries that then from
that purchase order tells us the production date,
when that purchase order was filled,
the production order associated with the purchase order,
the production line, the plant location, the plant code,
the manufacturing stations on that production line,
and then the historical time series data associated with
those stations on that line
when that purchase order was fulfilled.
>> Nice. So someone from
maintenance or from quality was
looking at that, they can now say,
hey, I have three customers that reported the same anomalies,
can I correlate them?
I will go from one to the other and then we'll realize,
hey, the telemetry from that robot arm,
whatever had the same glitch,
so I suspect this is what generated that anomaly here right?
>> Exactly, and so what we're doing effectively is when we take
all these different queries that we've run independently,
we can put them actually in a larger query here and this query,
I know there's a few lines here,
but all this query does,
this query is driving the panel we saw earlier,
all it is is it's KQL queries that invokes
a few SQL queries to
resolve things like the plant location and the line ID,
then it runs an Azure Digital Twins query
to get the stations on the line and then it runs the time series.
>> Everything at once in one place, it's pretty straightforward.
Let's get back to that diagram you
had because I want to sum up what you
did and with which services
before we go on to the the call [inaudible].
>> Absolutely. Yeah, let me pull that up,
just give me a moment.
Yeah, so here's a diagram and as we saw,
we pulled in the purchase order table from
SAP and then we pulled in a production schedule,
which is an Excel file and then
we also pulled in time series data,
and in that final example,
what we did is we actually ran
acoustic query from within
Azure Data Explorer and what that query was
able to do is it was able to reach out to
our sequel pool and run
some queries against the purchase order table,
against the production schedule,
as well as actually run a query against Azure Digital Twin.
So we basically created one large query that's
able to run several sub queries
all from within Azure Data Explorer.
>> Nice, I like that.
You know what, Taylor?
I understand better what Azure Synapse Analytics is for now.
Thank you very much for that.
One last question for you before I let you go.
You demonstrated the dashboard in Data Explorer,
but would that be the things you
recommend our customers to go with or are there other options?
Once you had the queries, I guess you can
build different types of dashboards, right?
>> Yeah, we recommend customers use
whatever BI tool they're comfortable with that they use today.
The great thing about Azure Data Explorer
is that it provides integrations
with a variety of different popular BI tools out there.
It integrates with Power BI,
it integrates [inaudible],
it integrates with Tableau,
so if you decide, hey,
you know we like to build our dashboards
in one of these different products,
you could do that easily and then just use that as
the front end for the query that's
actually running within the Azure Data Explorer.
>> Perfect, Taylor. That's brilliant.
You wrote a blog post on the IoT blog,
I have a link here on my t-shirt,
and so we will like to send people down there.
There is a link we're going to bring
up down here that will send you
to the blog from
Taylor that describes everything you just saw today,
with details, links and pointers,
so you can repro that, reuse this solution.
Taylor, thanks a lot for your time today.
Hope to see you soon on the IoT Show.
>> Thanks for having me on the show.
>> Always, you're more than welcome.
Thanks everyone for tuning in. See you soon. Bye-bye.
>> [MUSIC].