>> Hey friends. As you move your data from
where it's created to where it's analyzed,
you usually need to transform it for easier analysis.
Lakshmi Murthy is here to show me
how database templates for data lakes in Azure Synapse
can help with the transformation of data
into insights today on Azure Friday.
[MUSIC]
>> Hey friends, I'm Scott Hanselman and it's Azure Friday.
Here with Lakshmi Murthy and she's going to
talk to me about Azure Synapse. How are you?
>> Doing good. Thank you.
>> We're talking about big data.
If it's a data lake, what does that mean?
It's just a lot of data.
It's not just a regular database,
it's a lake of data.
>> Yes. But no structure you just have data sitting in the lake.
Think of it as a big lake of water.
>> We're trying to get meaning out of that
and that's where Synapse comes in and brings
everyone together and then we can start
really getting insight and analysis into that.
>> Absolutely, through our database
templates that we're going to talk today.
>> I understand that you have a slide.
Let's see your slides and you can give me some context.
>> Sure. Typically,
we see a lot of customers having the same problem.
You have your data in application silos and you're trying to bring
that data into the lake and be able to
do meaningful analytics to make key business decisions.
But unfortunately that requires
a huge standardization effort
and management of data is getting very expensive these days.
With Azure Synapse Analytics database templates,
we hope to solve that by bringing industry templates which
have pre-defined schema to help you model
your data and derive meaningful insights pretty quickly.
>> When you say industry standards, which industries?
It just depends, all kinds of
industries or some specific industries?
>> Yes. We have a total of
18 different industry templates and we'll take a look at that.
We have a total of 18 different industries,
whether it's retail, oil and gas,
manufacturing, banking, what have you.
Any industry out there,
we have curated customized templates.
It's a pretty comprehensive and describe all of
the different business functions
that are critical to that industry.
We have a total of over 5,000 entities with
30,000 attributes spanning all of those industries.
>> Well, back in the day like 20,
30 years ago when we were doing SQL server,
we had to sit around and we go like,
we're making a library.
What does a book have?
What's a book? It's a title and we would just make stuff up.
You're saying that a lot of industries,
a lot of entities have been decided and that
we don't have to think about that anymore?
>> Yes. These act as a blueprint.
Exactly that, you can start with that.
Within Synapse which is
our analytics platform for big data analytics,
we bring together the features of data warehousing,
data integration and the big data analytics
through our Spark and serverless engines that are in-built.
We've integrated these templates which serve as
blueprints for you to go and just get started.
You could just model your data,
look at anything whether it's customer or product,
build up your table.
If you have your data in the lake,
you can just point your data to that and
there you go and off you go to query
that data and start
adding a lot of business context to your data in the Lake.
>> That gives me contexts from the slide,
so I understand the problem now.
Where am I going to go?
I'm going to go into the Azure portal
or where am I going to do this work?
>> Let me actually just walk you through
a quick user journey of what we're going to see in the portal.
Let me just flip over to that.
First, we're going to look into
Azure Synapse studio where in the gallery,
we have all of the templates available.
Then we're going to pick a few tables
that are of interest to us and create
a database visually without using any code.
That's the other thing that I want to talk about today
which is our no code database designer.
Once you have that data,
you can either ingest data using Data Integration,
which is the Synapse pipelines and data flows.
Or if you have data already sitting in your data lake,
then you could just point it to that.
Then the last thing that we'll see
is how you can quickly query this data inside a Synapse.
With that, I think we
can switch over to actually look at a real demo.
>> Let's do it.
>> I'm over now in my Azure portal and I'm
now inside my Synapse Analytics workspace.
In here, I'm going to click into
the Knowledge Center and I'm going to go into the Browse Gallery.
In the gallery as you can see,
there's all of these templates.
As I said, we have total of
18 different industries from agriculture,
automotive, banking, fund management, freight and logistics.
Whatever industry, there are
several different industries we're also looking to add
more as we get more customer feedback
on the types of industries that are interested in.
For this demo, I would like to just pick Retail
and walk you through what we just saw in
the user journey. I'll hit Continue.
Now, we land in what we call as the exploration experience.
This is where from
the comprehensive models that I just talked about,
you will see that all of the data
is organized by business areas. We'll just give it a minute.
>> It's loading a huge amount of data.
>> Yes. Here we are.
I'm going to clear my canvas and just walk you through.
Within the Retail model,
we expect that these are
different business areas or
business functions that customers be interested in,
Channel, Customer, Customer Account.
You can browse through all of these different business areas,
see all of the entities that make up that business area,
and then actually just pick anything that's of
interest to you to take a deeper look.
In this view, you can
actually look at this particular, I've selected Customer.
You can see what are all of
the different attributes that make up Customer.
Now, let me look for a couple more.
I can look for transaction.
As you can see, transaction is related to customer.
You can model that as saying,
I have a transaction and I have
a customer ID, let's try transaction.
Then once you have all of the tables that's of interest to you,
you can click Create Database.
As you saw, you didn't write
any line of any code to actually create this database.
Now, you come into what we call as the customization view.
In here, you can now give this database a name,
so let's call this Azure Friday database.
Then also some of the properties that you said is if you
have data in your existing data in your data lake,
you can set your data lake location in here,
a linked service with points to the data lake and
the actual input folder and the data formats.
Today, we actually support as delimited text and parquet.
Once you have that, you're ready to publish your database.
It's still not saved.
But a couple of things that I wanted to actually show you
was along with creating the database,
you can come and edit any of
this information that was already predefined from the template.
You can actually, this is an open canvas,
you can come in here,
you can modify all of your tables, add new columns,
and even create new relationships depending on
your particular business or
organization how you want to model that data.
Along with that, you can also either add custom table.
You could just build your own table,
you don't have to use something from the template.
Also, the other thing that you could do
is if you just have data sitting in your lake,
you could just go into your data lake,
give it a name, pick the location or the file folder path.
This will just scan the folder path and
specific file name that you pick and propose a schema for you.
I hit Continue.
>> It's looking at that CSV file
and it's going to propose what it thinks you
should do based on what it thinks
is going on in the headers of the CSV?
>> Yes. I know already that the CSV doesn't have a header row.
But if you did, you can always save for column names.
But in this case, I'm just going to say Create.
What it did is because it didn't have column names,
it said this file actually had three columns and if I go in here,
I can rename these.
This is string, float.
This is the different things within the data.
A lot of our customers have SEP data that's already in the lake,
so what they do is they just go in here,
do a scan of the lake,
bring in that data,
have all of this EPI data and then create
their own structure here at relationships to existing tables, etc.
and then they're ready to go.
This is how I will edit the table.
Once I'm done, I can hit Publish.
Once the publishing completes,
we now have a lake database that's created.
>> Once you have data created in here like I just showed,
either if you have data in the lake,
you can just point your data to this database.
The cool thing about this is also that if you have,
let's say you have 10 tables and you have
three different lake locations from where you want to pull
data so you don't really need to duplicate data,
you can actually come in here and
uncheck the inherit from the default settings from the database.
You can actually specify different data lakes,
Azure Data Lake locations in your different folder paths to
hydrate these particular tables
within the database so you have a lot of flexibility there.
Then the other thing that I just want to show you is also,
once you have these created,
you can use the data integration tools
that are in-built in site Synapse to
create a data flow so you can create a brand new dataflow,
you can ingest data into these lake databases if you
want to do some transformation and
ingest it from an external source.
Then once you have data there,
you can query that data.
I'm going to switch to
a existing adventure Works table that I've already created.
As you know, this is that fictional retail store that we
use for a lot of our demos here at Microsoft so this,
as you can see here,
I have all of
these different entities that are
tables that are part of this database,
which is modeling that retail store Adventure Works.
I can actually just query
this data like I've already ingested data
into this building my pipelines so I'll just show you,
for example, a data flow that I've already created.
I have my source data in here that I have a source dataset.
You can configure your source data where your
data's coming from and you can configure
your data sync inside
of your data flow like this is your final sink.
My sync is the lake database that I've just created.
Once you run your pipeline and your data is in your lake database,
what you can then do is quickly get back
into your table and you can query this data.
For example, I can just look up the top 100 rows,
and again, I'm not writing any code.
I am just quickly doing a quick query here,
just going to hide that and as you can see,
I'm seeing the results.
This is our inbuilt server-less engine,
and you can also use
a Spark engine to just to see the same results.
>> You can use the serverless SQL,
you can use a Spark within Azure Synapse,
you probably use notebooks to explore the data if you want.
>> Yes. Right now I'm just attach this to a pool,
a Spark pool and this was just going to run running.
You can either use the serverless or
the Spark pool to actually query this data.
That's what I wanted to demo.
>> Then you can go and build,
once you've got this data,
you can build Power BI reports
on the data and the metadata if you wanted.
>> Yes, absolutely.
Actually, why don't I just show you that very
quickly so if I have Power BI desktop open.
The other thing that I want to call out here is
we have a new data connector within Power BI,
which is available for Synapse
and that actually brings in all of that metadata that we saw,
which is available in the templates and it also
brings you-all of those relationships so you can actually
model that inside of Power BI.
If I say synapse and this is our new connectors called
Synapse Analytics beta and if I say Connect,
it will show me all of the different workspaces and I can look for
my workspace and when I open up my workspace, I will see.
I had just created that
Azure Friday database so I can see this in here.
If I open this up and if I hover over these,
as you can see here,
hopefully, this is still visible,
but you can see all of
that rich metadata that came in from the templates.
It talks about what a customer is,
what is the transaction defined as,
and you can select these and add them to your Power BI reports.
I actually have a prebuilt report
for my adventure works that I can just show you.
I have channeled transaction,
transaction line item that I've brought in,
and let me just give this a minute to load.
You can bring that in and then I can even look
at the Power Query Editor here to
look at all of their relationships and all of
the data columns and attributes,
etc so it's really simple experience.
You can start with the templates and you can model your data,
you can build your database,
bring your data in just through existing data,
just pointing it to that,
or ingesting that data and you can just quickly run
use your in-built engines,
compute engines to query that data.
Lastly, you can also import this into
Power BI to build reports and dashboards.
>> Very cool. Azure Synapse Analytics it lets me go and design
those Lake databases you've got when you said 18
detailed models for lots of different industries,
it's GA now so people can use all this stuff per day.
>> Yes.
>> Then folks can take a look and learn more I understand at
Azure docs specifically Synapse Analytics,
looking up database templates under the database designer,
>> AKA db tablets.
>> AKA.MS/ Db templates.
Okay, cool. Good deal.
Thank you so much Lakshmi Murthy for hanging out with me today.
>> Thanks, Scott. Have a Good Friday.
>> I've been learning all about the great analytics and database
templates that I can do with Azure Synapse today on Azure Friday.
>> Hey, thanks for watching this episode of Azure Friday.
Now I need you to like it, Comment on it,
tell your friends retweet it,
watch more Azure Friday.
[MUSIC]