(upbeat sounds)
- Coming up, we're joined by John Macintyre,
Engineering Lead for Azure Synapse,
Microsoft's limitless analytics platform,
for a tour of the latest updates now generally available
and a first look at additional capabilities coming soon.
So John, welcome to the show.
- Thanks Jeremy it's great to be back.
- So we've been following the momentum of Azure Synapse
here in Microsoft Mechanics closely over the past year.
In fact,
we recently chronicled several early adopter customers
and if you're new to Azure Synapse,
it's Microsoft limitless analytics platform
that really brings together enterprise data warehousing,
also big data processing into a single managed environment
with no systems integration required.
So, John, I know you and the team have been hard at work,
but what's new in the service?
- So as you know Jeremy,
Azure Synapse Analytics has been available for customers
for the past year.
And we've had some really great preview capabilities
in the Synapse workspace
that are now ready for production workloads.
Like Azure Synapse Link,
which is the first cloud-native HTAP solution.
And that enables continuous analytics
over operational data in Cosmos DB.
That's done without interfering with your operational
or application workloads.
Next SQL Serverless is also now generally available.
And that gives you the horsepower you need
at the exact moment you run a query.
It runs completely serverless
so you only pay for each query and the data you process.
And beyond that, for analytics with Spark,
we've also built performance optimizations
for our implementation of Apache Spark,
including enhanced shuffle,
which aligns data to improve query performance.
We've also implemented dynamic partition pruning
to eliminate that unnecessary data during job execution.
All of these things are working together
to really speed up performance.
And that Spark environment that Synapse offers
is fully managed.
So when a job comes in,
the service will provision resources, scale resources,
and manage those resources as you need them.
- Right, and this is really great news I think
for Synapse users
and really highly anticipated capabilities.
But you've also added a host of new capabilities
that were recently in preview
that are now also available and fully supported as of today.
- We have, and you know, Jeremy,
these have been focused in a number of areas.
So first, to help you really easily get started:
the new knowledge center gives you Pipeline templates
to bring data in, sample scripts for analytics, automation,
and Notebooks to start to analyze your data
as well as access to data within the Azure open datasets.
Second, we're making it even easier
to bring data into Synapse for advanced analytics.
And to enrich that data in code-free ways
and apply your Azure Machine Learning models.
- Right, and those capabilities will make data analysts
and also data scientists really happy.
But, what are some of the things that we've added
for our data admins?
- One of the biggest things we've done
is to make it easier for you to connect to your data
and storage securely through managed private endpoints.
As you provision your Azure Synapse workspace,
you can simply enable the manage virtual network option.
And with that,
we automatically handle all that configuration
of virtual network and private endpoints
so that you can immediately start running SQL scripts
or use Notebooks to analyze your data.
You can also enable the exfiltration protection
for your workspace.
And what this does is it ensures
that all that outbound traffic
goes through private endpoints
and only to selected resources that are approved
in your Azure AD tenants.
- All right, and this is nice
because you no longer have to manage subnets,
worry about IP ranges, configure private endpoints
like you said.
You don't need deep networking knowledge or,
you know, knowledge about data movement or orchestration.
Also the performance and resiliency is managed then
by Microsoft.
- That's right.
We're removing that complexity for you.
And also as part of our comprehensive approach
to data protection, we've added new role types to Synapse
for role-based access controls.
They really give you more granular control
over both your resources and your data.
- Right, and this is a lot of popular updates
I think a lot of people have been waiting for.
But this is Mechanics,
so why don't we make this real for everybody?
- Yeah, you know, this is what I've been waiting for.
Demos are my favorite part of coming on Mechanics.
So I'll start here in my Azure Synapse workspace.
And I want to walk you through
how a grocery retailer might use new capabilities
in Synapse to plan their inventory levels.
As you know, beyond just monitoring operational data
and sales data,
we need to take into account external factors
that may impact sales and inventory.
As we've seen in 2020,
it's really the COVID-19 pandemic
that is changing buying behavior.
So for the most accurate forecast,
we need to work with our real-time operational sales data,
but at the same time,
correlate that with public COVID-19 data.
So let's start in data.
This provides a great view
of all of your connected data sources.
You can see it's easy to keep my data unified
and centralized, including data managed in the workspace
and data linked from sources that sit outside the workspace.
And from home, under useful links,
I can get to our knowledge center.
And this is so I can explore data sets
that are available to me.
In my case, I'll add the Bing COVID-19 dataset,
which provide daily confirmed cases
as well as related data worldwide.
All I need to do is click add data set,
and you'll see this shows up in my Linked data tab.
It's integrated into my Synapse workspace automatically.
And without worrying about schema details
or the format of the data,
I can start to explore the COVID-19 dataset.
If I click on actions and select a new SQL script,
select top 100 rows, Synapse will generate T-SQL commands
to analyze the data.
And I can start to explore that data using Serverless SQL.
And using the same process, if I create a new Notebook
to process and visualize the data with Python,
Synapse gives me a head start
with pre-populated PySpark code, all ready to execute.
And you can just attach that notebook
to a serverless Spark pool, run the Notebook
and start analyzing that data.
And this experience is available for data at any scale,
whether it's just a few thousand rows of data
or millions of rows of data, like we just demonstrated.
- Now what's great is
now you don't need to figure out how to connect to the data
and you can just start your analysis right away.
- That's right.
We're removing that step for you to make things easier.
So in my case,
the retail store data is managed and Cosmos DB.
And I want to see the impact of the COVID-19 cases
related to my operational retail sales data.
We can easily bring in new Cosmos DB data and to do that,
when I created the Cosmos DB container,
I selected the analytical store option.
And I can do this without worrying
about how when I enable this,
it's going to impact the performance
of my operational data workload.
And all that data is there in the Synapse workspace
in near real time.
Now I've done this in advance
and already have a Cosmos DB container
with Synapse Link enabled.
And as you can see here in my Linked data with Synapse,
that Cosmos DB is available to me.
And now we can easily query that data
between our sales system
as well as the COVID-19 data that we've pulled in.
And in my case, I want to see the correlation
between COVID case counts and the sales data.
To do that, I've added COVID data
and filtered by Texas and California,
where many of our stores are located
and where we know case counts are high.
And in this case, we're specifically looking
at sales of household paper products and cleaning supplies.
I've run a Serverless SQL query
and I'll display the COVID case count data.
And you will see that in March,
the sales and demand spiked
before case counts started to accelerate.
But if you look at week 30 and beyond,
the COVID case count is a good predictor of sales and demand
for these products.
When case counts go up,
we can see higher demand quickly follows.
To put this into further context,
let's compare this to our historical 2019 sales data
that resides in my Azure Data Lake.
I'll use the same parameters
and we can see that our run rate for these items
is a lot lower.
And it isn't really even in the ballpark
of the actual demand that we're seeing.
So this isn't really going to help us much
with future predictions and forecasts.
- Right, and what we just saw was how simple and fast it was
to bring in the public data.
and also the operational data that you had in Cosmos DB
and analyze it at scale
against your historical data
that you brought in because you're querying was serverless,
also there wasn't a setup or servers to manage
or any configuration required.
But how were you able to bring in that historical data
that we saw in the last step?
- Yeah, I'm glad you asked Jeremy.
We're making things a lot easier,
not just for the administrators,
but also for the data engineers.
That historical data actually came
from a legacy on-premises data warehouse.
But let me show you how easy it is
to bring in data like that with Synapse.
I can either use a code-free pipeline,
or I can simply load the data into a SQL pool.
To make data loading easier,
we've added a new experience for bulk loading.
First you select the folder, you right-click,
then new SQL script, then bulk load.
From there, you can select a storage account,
which I'll do, and I'll click continue.
I'll keep the auto selected properties, hit continue again.
Then I'll pick a dedicated SQL pool
where I want to load the data.
I can create a new target table or use an existing one.
In my case, I'll use one I created just for the show
called MechLoad.
The column mappings look good.
Then I'll open the script,
and beyond just a simple one-time import,
what's really cool is that right here
I can operationalize my data pipeline.
Here I have a basic store procedure.
You can see it from the section that is commented out.
So I'll uncomment that.
When I do that and run it,
you'll see the bulk load procedure is added
to my store procedure folder.
I'll add this to a new pipeline and that's it.
It's operationalized.
- Okay, so you've automated the pipeline
to bring in the data that you need,
but how do you take the next step then
to perform predictive analytics for sales forecast?
- So now that I have the data flowing in,
from here I can go on to predict purchasing
and stay ahead of demand.
And I can use that COVID-19 case data
for my predictive analysis.
And we just built our pipeline to ingest the data
into a dedicated SQL pool
where I can actually run all of my predictions.
We now have native integration with Azure Machine Learning.
And to save time,
I've already linked to my Synapse workspace
with my Azure Machine Learning service.
If I jump back to data, the action for machine learning
will appear against all my SQL tables.
I just need to click into a table
and select Machine Learning and enrich with existing model.
I'll see the list of all my models from the ML registry
that I have linked with Azure Machine Learning.
And this is the model registry
that my data science team is using
to develop their predictive models.
And I can just choose one of these
that corresponds to the selected table.
Using this model, I can enrich the table.
When I click continue, it's going to analyze the table
and the model,
and it will automatically map source column names
with the model inputs
to make sure everything works correctly.
This next step will create a store procedure for me
so that I can continue running this model
with my latest data.
I just need to give it a name.
I'll load this model into an existing table.
Now I'll deploy it and that'll take just a second.
And from here, we can execute our store procedure
to enrich the data from our table
and we'll use our new predict function
to predict our inventory forecast.
I'll run it.
And note, these ML predictions are being calculated
in the engine,
which means all my queries are still really fast.
The ML engine is scaling with my cluster
and there's no additional cost for making API calls
from outside my data warehouse environment
to some separate scoring service.
In just a few seconds, it's analyzed three million records
and we can see the predicted quantities we need
for inventory categories all without moving the data.
- And now it's also part of a stored procedure
so it's operationalized
and it's going to stay up to date then with its prediction.
So all of what we just shown though,
is probably part of a pre-production or a test environment,
so how do we push something like this then into production?
- So we built CICB options into Azure Synapse
under managed and sourced control.
This means that your resource definitions,
link services, connection strings, pipelines,
and code artifacts can all be version controlled using Git.
And you can deploy Synpase artifacts
through your dev ops release pipeline,
making it easier for you to maintain your development
and production workspaces.
- Right, and as we've shown many times,
it's really easy to serve up that data to business users.
For example, using Power BI directly from Synapse.
Now, everything you've shown is out of preview today
and can be used right now for production workloads.
- That's right.
And the great thing about continuous innovation in the cloud
is that we've also just released more capabilities
in preview.
We're making it easier for you to transform your data
at scale, code-free, with power query built directly
into the Azure Synapse Studio experience.
You can also build Machine Learning models, code-free,
with Auto ML, without ever leaving the Synapse environment.
And we're really excited about the native integration
with Azure Purview,
our new service for discovering and mapping data
across your complete data estate.
With this integration,
all that data is available for analytics
within Azure Synapse.
- So really a ton of progress in the last couple of months.
Thanks John for joining us today,
but for people that want to get started
and kick the tires of this, what do you recommend people do?
- If you're already using Azure Synapse Analytics
for data warehousing,
you can attach a Synapse workspace to it today
to discover all this new functionality.
If not, sign up for a trial
or create your first Synapse workspace at aka.ms/GetSynapse.
- Amazing stuff.
And now it's generally available
for all your production workloads with even more to try
that's in preview.
Of course, we're going to continue to track this
on Microsoft Mechanics so be sure to keep checking back,
subscribe to our channel if you haven't already
and thanks for watching.
(upbeat music)