(whirring)
- Coming up on another special edition of How We Built It,
I'm joined by American gas and electric
utility firm Southern Company.
We're going to see how they're evaluating Azure Synapse
to make it easier to share anonymized data
with their analytics partners,
and also strengthen their capabilities
to do analytics in-house.
So I'm here today with Enterprise Architect Greg Floyd
from Southern Company in Atlanta.
Welcome to Microsoft Mechanics.
- Thanks for inviting me on your show.
- Thanks so much for joining us today.
And by the way, if you're new to Azure Synapse,
it really provides a unified environment
where enterprise data warehousing and big data processing
come together into a single service,
really removing the traditional constraints
for analyzing data of all shapes and sizes.
Now Greg, your evaluation of Azure Synapse
from Southern Company is interesting
because you actually started off
doing something very exploratory.
And now it's actually opening the door
for you and your team at Southern Company
to build more analytics
on top of the different models that you have
and do more in house.
Can you tell us more?
- Right, yes, our Synapse evaluation was initially scoped
to prove out how we might optimize our data transformation
and sharing process
in our Energy Information Exchange project.
This is where we share anonymized data
of initially 1.2 main customers
with partners who have developed machine learning models
and analytic services.
They provide us back energy insights
so we can offer products and services
personalized for our customers,
like energy efficiency programs, community seller programs
and rate recommendations.
- Alright, so your traditional model
has been really to bring in partners
to help with the analytics
and kind of make these different recommendations
for different customer campaigns, right?
- That's right, while we do our own analytics,
we also use trusted partners to assist us,
but the data we shared does not contain
any private customer information.
We obfuscate and anonymize all of that.
- So, what were things like then before Azure Synapse?
- Well, the current solution uses Azure Data Lake analytics
with SQL Server, C# and U-SQL.
We do daily batch processes to export data
out of our customer information system,
storing our customer billing and rates data,
and Hadoop, which has our energy data.
Both are on premises.
This data is encrypted and uploaded to staging
in a private storage account.
Then the Azure Data Factory pipeline is triggered
via an API that moves data from staging to raw storage,
and runs an ETL process to first anonymize the data
and create goods for the account, meter and premise keys,
and store that linked data into a database.
Then it moves private data like customer name, address,
or GPS coordinates to Cosmos DB.
And it saves anonymized data as compressed JSON
to give us a common schema and stored
in our anonymized storage account.
And finally, it triggers a notification
that data is available for the approved datasets.
Partners can then access the anonymized data
via a secured API.
- Alright, so it sounds like you're jumping
kind of in and out of various services
both in Azure and also through SQL databases.
So how did things change then with Azure Synapse?
- Right, the process works well today.
But notice analytics is not part of the end-to-end solution.
And since we're expanding the scope to include data
from 4.2 million customers,
we want to prove out a better
and simpler approach using Spark on a number of levels.
With Synapse, rather than trying
to bring together different services,
it gives us a single environment
incorporating the data lake,
the data orchestration engine and Spark.
The main thing we were looking for was the ability
to better distribute large datasets
across an Azure Data Lake without imposing strict schemas.
With Spark, the job could run in full batch mode.
And we could use the parquet files
with more flexible schemas,
instead of using SQL database tables.
Proving this out in Azure Synapse
would also strengthen our internal analytics
by giving us a foundation to do more data analytics
against raw data files in the data lake
using SQL or Python notebooks.
- This all sounds very powerful.
Can we take a look?
- Absolutely, let me walk you through our evaluation.
We begin with our orchestration pipeline.
Here we import data from delimited text in staging,
to parquet and raw storage,
and then transform the data into anonymized information
by calling Spark notebooks.
We first import account information
and generate linkage keys with GUIDs,
storing this in private storage.
Then we transform each dataset.
So here's a sample notebook written in Scala,
where we transform raw customer account information
into anonymize JSON in five steps.
First, we load customer account data
into a data frame from raw storage.
We join linkage keys into the data frames
that we generated earlier.
Then we extract the private data with the linkage keys
and store it in private storage.
Notice the account key in private data in the output.
Finally, we create an anonymized data frame
and write it to compress JSON in anonymize storage.
And these are the storage folders
representing different stages of the data transformation.
In production, each container will be assigned
to an account accessible only by the API for query,
or the Data Factory for transformation,
or Spark for analysis.
This follows CQRS best practices.
And here's the output JSON file showing energy readings
from an anonymized meter.
Our partners can run their models against this data
and provide back a list of anonymize keys
as potential candidates for a specific program.
Using the linkage keys,
we can map back to customer accounts.
- And this looks really great.
So how does this change things for you now?
- So this was a small proof of concept,
but now it simplifies the solution
by reducing the number of overlapping technologies.
It also provides a single pane of glass
for data engineers and analysts.
And the private Parquet files can be used
for in-house exploration and analysis.
This is something new for us.
- Alright, so can you give us a look
at what you're doing now?
- Sure, let's get back to our Synapse workspace.
In this notebook, I'm using PI and Spark in SQL
to calculate energy usage from readings
that were stored in a private folder.
First, I read the energy data and using windowing functions,
I flatten the current and previous meter reading.
Then I calculate the difference
and store the energy usage in a Spark table.
From there, using SQL Server list,
I can query the usage for a particular account.
Our internal folks could do something similar
to suggest a time of use rate.
- Alright, so this is awesome stuff.
I love the progress that you've made.
So what's next on the agenda then
for Southern Company with Synapse?
- So today, this is preview technology.
Next we'll be testing out VNet service endpoints
and the ability to secure slices of data in the dataset.
And from there We'll look to run the whole process
from Synapse in the future.
We also see potential for Synapse
in other projects using data warehousing.
And we're considering moving a portion
of our Hadoop system to Synapse to focus more
on big data analytics and less on infrastructure.
- Great stuff, Greg.
Thanks again for joining us today
and really sharing your evaluation of Azure Synapse.
By the way, if you liked what you saw with Southern Company,
please check back to the rest
of our How We Built It series at aka.ms/AzureSynapseSeries
to learn from other early adopters
kicking the tires with Azure Synapse.
That's all the time we have for this show.
Thanks so much for watching, and goodbye for now.
(upbeat music)