(machine gears clicking)
- Coming up on this special edition of How We Built It,
we meet a Aggreko, a global leader
in the supply of temporary power generation
and temperature control systems for major events
like the Super Bowl here in the U.S.,
providing backup energy
for the Fukushima nuclear power station in Japan,
or energy supply during times of crises.
Now, we're gonna look at how they're evaluating
Azure Synapse to increase their operational efficiency
with just-in-time supply of their specialist equipment.
So I'm joined today by Elizabeth Hollinger,
Director of data insights at Aggreko,
joining us today from Scotland.
Welcome to Microsoft Mechanics.
- Thanks, it's great to be on the show.
- And thanks so much for joining us today.
And by the way, if you're new to Azure Synapse,
this is Microsoft's limitless analytics platform
that really brings together enterprise data warehousing
and also big data processing
into a single service environment.
So Aggreko really serves this critical foundational role
in terms of providing organizations and even countries,
the power and energy solutions that they need.
Elizabeth,
what are you guys doing to help in these cases?
- So it all comes down to how we use our data
to drive value
and one of the areas we focus on
is operational efficiency.
As you pointed out,
our global business requires a highly responsive
supply and demand infrastructure.
We manufacture our specialist equipment ourselves
and we need to keep a well-stocked inventory of parts
so we can meet both temporary and long-term demand.
We create bespoke applications for every customer
based on their needs
and their specific operating conditions.
So for example, we might be providing power
and cooling solutions
and high ambient temperature in Australia,
or we might be providing power
to drill for oil in the North Sea.
So there's a lot of complexity
in how we build that assets
for very specific needs.
- Okay, so can you tell us your systems to date
and how you've been tackling this before Azure Synapse?
- Yeah, sure.
So we started down the path of modernizing
our data state a few years ago
by introducing a data lake.
Historically, we used to upload our data
from disparate systems to our on-prem data warehouse.
We'd run this batch process
three times a day starting every eight hours,
and it would take around four hours for the job to complete.
Every day we'd delete
and rebuild our data warehouse
having to overwrite data
to avoid exceeding our storage limits.
So to solve for this,
we re-platformed our on-prem data warehouse
and our reports to the data lake in Azure.
This created for us that coveted
single source of truth,
the central data hub
for our applications and our customers
to access the data they need.
Now all of the data from our source systems,
as well as any external data we use
is ingested, stored, and transformed
directly in the data lake.
So we no longer need this long schedule batch process,
As now every time details changed in the system,
it passes automatically to the data lake,
and we no longer have those storage limitations.
The data is more accessible.
And we also have high frequency time series data,
including the most up-to-date data for manufacturing.
- Great. So can you tell us how
you're experimenting now with Azure Synapse?
- Yeah, sure.
So one of the things we wanted to do
was to explore insights
without moving the data outside of that central data lake.
Synapse gives us a single environment
to explore and query the data without moving it.
So at a spectrum of the volume of data,
we can achieve exponentially faster insights
by querying directly over the lake
before outputting insight to Power BI.
And it also gives our data scientists and analysts
one cohesive workspace,
which is brilliant for collaboration.
- So it's really great to see
that Azure Synapse now is part
of your data modernization strategy.
But how do you then
or how does it then help with the approach
that you're taking for things like,
just-in-time logistics,
and also the supply chain?
- Sure, so let me give you an example
of one of our recent projects
that we've now transformed using Azure Synapse.
We have a parts inventory report
that pulls a selection of data from our ERP system,
things like manufacturing orders,
purchase orders,
delivery dates, etc.
And in the past to check that out inventory levels,
we relied on polling the data every 8 hours,
using that information to build the report in Excel
that could take up to a day to produce.
So there was always this lag in information
that the team had access to.
This report allows our manufacturing planners and buyers
to check if they have the complete bill of materials
in order to build an asset.
And that includes things like pipes,
cables, containers, wires, etc.
- Right, So then in this case,
your views and everything are kind of
delayed all the time, material shortages.
Everything's got this lag kind of built into it.
So it gets better now, right?
- Yeah, so we were always operating a little bit behind,
and we could end up spending loads of time understanding
how the numbers were derived,
which took away from the time
we could spend building the assets.
So with Synapse,
we can query directly over the lake
and output to Power BI to produce a report now in minutes.
And that data can now refresh every 5 minutes,
which is near real time for us.
- This sounds really great.
But I'd love to see it in action. Can we take a look?
- Sure.
So here's our material status report and Power BI.
And we've created this with Synapse.
The dashboard page provides a summary
of the status of the parts.
The users of this report
include the buyers and our manufacturing teams.
So we're interested in who the buyer is,
and where there's a shortage of any particular part.
This middle table shows detail on where we have shortages,
and for what inventory.
Where it's red, there are shortages,
and we can see by how many units.
And orange means there is stock
to meet that week's production demand.
When I click into the detailed view,
you can see we have a power app integrated within the report
with comments included from the buyers.
So I can see there are zero of this part in stock,
but 10 on order.
We need more than that.
So we can create another request for purchase
with a due date of the 28th.
So I'll submit comment to send the request to order now.
And now I can see a view of all activities across suppliers
from all the parts requested,
and where the inventory is located
and its status.
And I can also see activities by supplier.
- So that's current and just-in-time view of your inventory
and all the materials that you have,
but this wasn't possible before.
So what did they do then in the back end,
using Azure Synapse to get all of this enabled and running?
- So once we've ingested any of our data sources,
we typically spend around 30-40% of the time
exploring, transforming that data
making it ready to create insight from.
So during that time, we discover work and reshape data
and reshape that data model.
So here you can see our notebook
in Synapse running PySpark.
It's attached to the data from our inventory system,
which contains info from a number of sources
like those manufacturing orders
and purchase orders I mentioned.
So this is a query
to preview this file as a table,
even though it's a flat JSON file.
So let's run this.
And you'll see it returns a table
and it's sorted by timestamp.
You can see the most recent timestamps.
But pay attention to this top stamp
of the last record at 1315.
Before this data would have updated every eight hours.
So I'm going to rerun the same query
on our live data.
We sped up this demo recording for the interest of time.
But you'll see when it completes,
that the results are five minutes newer,
as indicated by our top row here
at 1320.
And we can see seven more orders have come through.
- And this is great because all of your queries now
are querying over the most up-to-date operational data.
But what are you doing then to explore that data?
- So yeah, here in our second notebook,
we're using a Spark job to manipulate the data
to highlight expected shortages
that will cause delay in the manufacturing.
So first of all,
I will make sure we can read the column names
for subsequent queries.
Next, I'll output everything in CSV format,
into our data lake folder.
From here, I can then read the CSV
with all of our warehouses.
And I can also query it using SQL.
For example, if I want to filter
to a specific warehouse location.
So let's run all of these in order.
First, I'll run the CSV right.
Next, I'll run the route.
And lastly, I'll run the filter
and update my data.
And you'll see it's found all the data
from all the different warehouses.
But let me show the filtered data
from this specific warehouse HM0.
And if I scroll to the right,
you can see here the inventory status,
which is how we deride the order status parts
that we saw in Power BI.
- So we've done all the exploration and analysis,
but what else do we need to do to publish all of
that back into Power BI?
- Yeah.
So our last step then is just to be able to publish
and visualize this information
to make it easy to understand in Power BI.
So here's the output CSV
we saw in our data explanation.
Even though it's a flat file,
I can preview the data as a table in the data lake.
And if I right click in it
then create a new notebook,
I can run T-SQL or Python against the date lake
and even build my first sale
calling the CSV file.
So I click on Properties
and give it a name; say explore buyers.
And to get to these notebooks later,
you'll find them in the develop area
of our Synapse workspace.
So I'll close this one
and jump over to a notebook I created earlier
called explore manufacturing.
And I'll run that.
And that will take a moment.
But we will see the raw output below.
Of course, using Power BI,
I can make this easier to analyze.
I'll go now to our Power BI,
into manufacturing under material status report.
And this is the Power BI report that I showed earlier on.
The great thing about this is
that our developers can dynamically test out
the end user experience
and make sure everything's working correctly.
Even the power app.
And we can do that all within Synapse.
This is a big deal for us,
because it just makes our developers lives easier.
- So pretty much you got all of the analytics foundation
for managing your inventory,
and your logistics really,
as needs start to dynamically change.
But what's next for you and Aggreko?
- So next for us
is that we want to explore how we can take this further
and build in longer looking lead times
for our manufacturing team to plan.
- So thanks, Elizabeth.
Thanks so much for sharing with us
your evaluation of Azure Synapse.
And if you liked hearing from Aggreko,
please also check out the rest of our series
for How We Built It
at aka.ms/AzureSynapseSeries
to learn from other early adopters
trying out Azure Synapse.
That's all the time we have for today's show.
Thanks for watching.
We'll see you next time.
(upbeat music)