(upbeat music)
- Coming up, another special edition of How We Built It.
I'm joined by Netherlands-based AI and analytics firm Macaw,
who help their customers move off legacy systems
to build intelligent operational processes.
And we're going to look at how they're helping
to modernize the residential
and commercial water supply in Holland
with IoT and proactive analytics using Azure Synapse.
So today I'm joined by data
and analytics architect Henko Kors,
dialing in from Macaw, working at home in Holland.
Welcome to Microsoft Mechanics.
- Hey, nice to be here.
- So thank you so much for joining us today.
And by the way, if you're new to Azure Synapse,
this provides a unified environment
where enterprise data warehousing
and also big data processing
come together into a single service,
removing the traditional constraints
for analyzing data really of all shapes and sizes.
Now Henko, your team at Macaw really specializes
in modernizing data platforms for your customers
and you've got a very specific scenario
that you've been working on
for your evaluation of Azure Synapse,
where you're basically building analytics
on top of pretty closed legacy systems.
Can you tell us more about that?
- Absolutely.
So one of the first considerations
for any data platform monetization effort
is how the proposed solution
can integrate with existing technology.
The first checkbox for us
was the openness of the platform,
so we can make sure that everything works.
- And that's one of the great things about Azure Synapse,
it has a robust hub-and-spoke model
that really allows you to bring in data
from really all sources and use that
against what you already have where that makes sense.
- That's right.
But often, you might be dealing with black-box systems
where you need to focus on getting the data out
so you can build a processes
and analytics platform around it.
So I'll give you a real-world example
of our Synapse evaluation.
In this case, what do you tell the company serving
just under a million households who have no real way
of detecting issues in the water service.
And this is even more important
because they are regulated to measure
the cost of drinking water and the quality of service.
- Okay, so can you tell us where they're coming from,
what are their current systems looking like?
- Yeah, so they have solutions on premises
for scheduling bill payments, financials and ledgers,
and a customer engagement system has logged interactions.
The only way that could keep track
of potential issues to water supply,
was to look at the volume of calls and emails
from customers and the specific complaints.
They were querying the data on Excel sheets
or static reports and detecting broadening issues
after the fact and weren't able to pinpoint
the sort of issues occurring in the water service.
- Right, I think a lot of people have seen this before
where it's kind of an on-prem system, legacy,
it's limited in terms of getting insights
and really optimized for maybe financial reporting
or billing or those types of operations
but not for analytics, right?
- Yeah, so we want to shift them
to an analytics platform that could help them
to more proactively detect service level issues,
so they could keep the quality of service
as high as possible.
And so the first thing we did,
was to introduce new data sources with IoT sensors
that we placed under water transport pipes,
providing drinking water across the area.
They listened for and measured water flow and pressure.
We also placed input and output sensors
on distribution boxes to pinpoint water leakages.
This data was converted to JSON
for stream analytics on the Edge.
And we used IoT Hub to do streaming ingest
of the data into both stream analytics
that can be queried from Power BI,
and routed the data into the data lake storage
for trend reports and to create
training data sets for analytics.
Second, we used the meta-data driven
extraction framework with Azure Synapse pipelines
to get the data out in a standardized, cost-effective way.
We prep and transform the data using
Synapse Spark pools and use Azure Data Lake
as storage layer for the two data lake zones.
One zone for more raw data storage in the bronze layer,
and one for more filtered and prepped data
in the silver layer.
This approach enabled us to merge
the information from the SAP systems
to match locational telemetry from the sensors
with customer and asset management data.
Correlate that with logged customer engagement
and complaints and also leverage
historical data from these systems.
And from here, we're able to directly explore and query
the data interactively using Spark pools
and Synapse SQL Serverless Compute.
And the results are visualized in Power BI
for internal and external customers
and regulators in a governed manner.
- So this sounds like a really modern approach then
to look at basically the water system that you have there.
Can you show us how this would look in action?
- Yeah, so now instead of waiting for
after-the-fact customer reporting for service issues,
we can monitor the pressure and flow in real time.
So here we're zoomed in on four fragile locations
that serve a lot of households in the area.
You can see data streaming in
from IoT sensors representing flow.
Below you can see where the sensors are located on the map.
A healthy reading is around 180 to 200
cubic meters per hour for this pipe type,
but we can see one of the flow meters
is reporting an abnormal amount of flow,
around 80 cubic meters per hour.
This could be a potential risk,
so let me click in to get more details.
Here we can see the daily usage pattern.
You can see that there are peaks in the morning and evening.
As we saw, a previous reading shows 80,
and it should be close, around 200, at this time of day.
And now from here of course,
we can investigate this further.
- So these are really great visualizations
on kind of the reporting side
but because we're the Mechanics,
we want to see what's underneath all of that.
Can you show us what powers all of this?
- Yeah sure, because there's a lot
going behind the scenes here.
Let's get into the Synapse workspace and I'll show you.
You can see in the Orchestration tab
that we're building our dataset
by mapping emerging asset data
from our backend SAP apps for customer
and asset data and telemetry data from the devices.
This is then transformed into a data model
for downstream analytics,
and reporting using a PySpark script, a sparkles,
and we move the data through the lake,
where you can query the data through SQL Serverless
that gives us on-demand compute.
The great thing here is that
we can query CSV files in the in the lake
using T-SQL we all know and love,
like the asset master data.
Power BI just sees this as a SQL table,
but in fact, it's a CSV file on the data link.
- And this is really powerful and significant
because it really opens up your data lake
to legacy apps as well.
- Yeah it does, something we couldn't easily do before.
And we can also apply additional
SQL security capabilities like row-level security,
on top of flat file formats if you want and need.
- Alright, so this is good
but it's still near real time, but still reactive.
Can we flip this and kind of move this
into predictive realm and kind of start
to predict maybe service-level incidents
that might happen in the future?
- Yeah, right.
What I just showed you is still reactive based
on your real time data though.
But we can use the data now to preempt future issues.
So let me show you the model we built
for service degradation over time
that merges historical data with sensor data.
This is our Pyspark notebook.
We are using flow readings
to predict failures within 24 hours.
We're looking over the data from 50 sensors,
asset loss data, and data from known service outages.
So we can start building the model now.
And since we're doing analytics,
there are a lot of components to this.
Remember when a queried the master data earlier,
we are pulling in that data too.
It gives us all the attributes of the pipes,
age and when it was last maintained.
Next, here are the failures of sensors over time.
This is where we are looking for deviations to data
from sensor data in a three-hour period
and a 24-hour period.
So we took all of those different inputs,
all of the sensor readings, outages,
material types, and trended over time against our model.
And we can see that there is a high potential failure
of the water pipe in the area
shown on the map in the next 24 hours.
And really, this was not possible before
with this amount of effort.
- This is really amazing work
and I could also see how this could prevent cases now
where people don't have water,
and you also have the basis for scaling insights
on water consumption and water efficiency.
And it's really great to see how big data
and analytics really impact the services
that we all use every day.
So thanks so much for sharing your Azure Synapse
evaluation with us today Henko.
And if you liked hearing from Henko,
please also check back to the rest of
our How We Built It series at
aka.ms/azureSynapseseries
to learn from other early adopters
also kicking the tires with Azure Synapse.
Thanks again for watching,
we'll see you next time and goodbye for now.
(upbeat music)