>> You've been hearing a lot about Microsoft Fabric,
but how can it work with SQL Server today?
Learn all about it this week on Data Exposed.
[MUSIC].
>> Hi. I'm Anna Hoffman and welcome to
this episode of Data Exposed.
Today I'm joined by the famous Bob Ward.
Bob, thanks so much for coming on Data Exposed again.
>> Out of our famous or maybe infamous Anna.
But it's always great to be on your show.
Great to be back here talking SQL again.
>> Yeah. It's great to have you.
We got a really exciting topic today
because not only are we talking about Microsoft Fabric,
we're talking about how SQL works with Microsoft Fabric,
which I think is probably a question a lot
of people have been having.
But before we get into it,
I got to ask you what
is this Microsoft Fabric we've been hearing about.
>> At Microsoft Fabric,
huge exciting announcement from
Microsoft and Microsoft Bill event,
is a complete new data platform
unifying so many different technologies.
Power BI, Synapse,
data engineering, and data science,
all sorts of different things,
and more to come and SQL Server being a data platform itself,
I thought it might be interesting to figure out how to SQL
integrate with this because there's lot of
cycler runs the entire Fabric concept.
>> Yeah, definitely. Know makes a ton of sense.
We've talked about data virtualization capabilities
before how does it differ when it comes
to Microsoft Fabric or what do you think?
>> Well, we didn't talk about in one of
our Data Exposed episodes on
SQL Server 2022 about data virtualization.
By the way, anything I'm talking about today also applies to
Azure SQL Managed Instance because it has
that same data virtualization capability.
Here's what happened. I knew we had this capability.
I knew the amounts with the factor was coming.
I thought to myself, how do we tie these worlds together?
What I thought I'd show you is an example
of how SQL can be used to perhaps,
maybe archive data using data virtualization.
Then maybe taking that archived data
and then integrating to the Fabric.
>> Sounds pretty good to me.
>> Well, so let me start by showing you this.
In SQL Server, we have a concept called partitions.
Also in SQL 2022,
we've decided to call CETAS,
which is basically creating this external table to
export data. Imagine this scenario.
You have SQL partition,
a table's partitioned,
and whatever your partition comes older or cold data.
SQL is always at this capability to
do what's called switch a partition
like move the partitioned out of the act of
table and put it somewhere else.
In the case of SQL Server,
we could call it an archive table,
just a standard SQL Server table.
Because we have the CETAS concept though, consider this.
We could though create
an external table based on a select of the archive table,
and export parquet data to Azure Storage.
Now if you want to go query your colder data,
you would just want a select statement against
this external table and we'd be
accessing parquet instead of data inside SQL server.
I found a way to archive my cold data into less expensive storage.
Let me show you how this works.
In SQL Server, if you use partitions before,
you have something called partition
function and that's a way to actually
segment off your data into various types of ranges.
If you can see this particular example here,
this is sales data I'm going to use here.
I'm going to use partition functions to segment off
the data into months or maybe ranges of months.
Here's my partition function you see here on my T-SQL script.
By the way, I've got a pointer to into
this to all the demo materials for your audience.
Once I've got those partition,
I want to create this external table concept,
so I need an external data source.
You've seen me show us before
where the data sources pointing to Azure storage.
But in this case, I'm going to create a file format for
something called parquet, which SQL recognizes.
Let me switch the partition.
I'm going to take that colder partition and
switch it to the archive table, and then here's the beauty.
Here's the CETAS concept.
I'll take the external table, which is a SQL table,
and export it as
parquet as a select statement out of the archive table.
Then now that data lives in Azure.
If I want to go just query this thing,
I'm really querying parquet.
By the way, since this parquet which like a column store index,
we can access, we do column elimination,
and row group elimination.
Or it could even union the cold archive data with live data.
Now here's an Azure storage.
This is where it actually looks like.
This parquet file here represents
my cold data now living in this inexpensive storage concept.
Using data virtualization, using CETAS,
but putting data in a place that's less
expensive but still accessing what I needed.
>> That seems really cool to me, Bob.
Also just in general,
a good way to save costs,
move data to parquet.
I didn't even know you could query using
T-SQL on Parquet, so that's pretty cool.
However, how does this relate to Microsoft Fabric?
>> It makes sense for you to ask that question.
If you can see in this slide here,
I'm showing them what I showed before.
Let's say I've gone to this CETAS concept.
When I was looking at the Microsoft Fabric,
I noticed this concept called one link and the idea of a shortcut.
Inside the Fabric,
I can use the one light you create a shortcut to
my Azure storage and maybe
even integrate it with data like an Excel.
Let me show how that works.
Here are in the Fabric,
I'm in the Fabric interface.
I've created this idea of a shortcut to Azure storage,
the one I actually created with my CETAS with my parquet files.
I'm telling the Fabric this is the location of
my Azure storage and some credentials.
Now I'll go in and give it a shortcut name
because I want to be able to view
this data and museum manipulated inside the Fabric.
I'll give it a name and I'll even put in here
like the name of my container in Azure storage or my parquet file.
Let me go ahead and give it a name.
When I hit "Create" here,
the Fabric will create this nice little shortcuts
which now inside this lake house explore,
I can go view my data.
I go and look at my container folder
here and if I drill into this,
I now have parquet data,
which is actually pretty cool.
But you're asking yourself well,
what do you do with this parquet data?
Well, inside the Fabric,
it naturally supports notebooks on your big notebook fan,
so I can do like a PySpark notebook
to do an easy visualization of data,
or even do things like machine learning what I want to do.
I'm inside the Fabric interface building this PySpark notebook,
and I want to be able to visualize
the data that sits in Azure Storage,
but as a shortcut through the Fabric.
Now I need a path for this.
I love this in the Fabric right here.
I can just go pick on the parquet file and it tells me, hey,
here's the relative path that you need to access this data.
>> Nice.
>> Yeah, I'll just paste that in there.
>> Run this code and I'm doing like a DataFrame.
You have to keep seeing this before in Python and Spark.
Now I just have an easy visualization of our data.
Mind you that data doesn't sit inside the fabric.
We're doing a shortcut pointing to archive data already have.
>> Nice, okay, this is pretty cool.
But I'm thinking there's more.
>> There's more. Here's what I'd like to do.
I'd like to be able to visualize this data like with Power BI.
What I'm going to do first is I'm going to load
the parquet file into something called the delta table.
Now, in the Microsoft fabric,
tables are in this concept called delta table,
which maybe your databricks users are familiar with.
I'll click in here and say load this
as a delta table and I'll give it a name.
Once I give this a name,
we just put it in here, something meaningful to me.
It creates this GUI based on my parquet file name.
I'll say confirm, it's pretty quick process now.
Now in the Lake House Explorer,
I can go look at this table just like a SQL table perhaps.
I see the columns, the types,
give me click on it and I can visualize the data.
Now at this point I can query
this delta table just like I could do a SQL table.
That's part of the whole idea inside the fabric.
>> Wow, that's so cool.
>> I know. I mean, look, I've already done
some pretty easy integration by just doing a few clicks.
Banana, I want to do something pretty cool.
I was thinking, talking to my colleagues
about this at Microsoft and our team.
I'm like, how do I create a scenario that's really rich?
What if we could actually
integrate the fabric data that I have built
already with my archive data with something
like sentiment surveys in Excel.
If I've got an analyst that has
built survey data based on sales data,
I want to compare these
together and they've got Excel, you're going to love this.
I can take this Excel file and on my laptop,
I can actually integrate this with
One Lake through something called a One Lake OneDrive concept.
This is literally a folder on my laptop yeah,
so I just did a drag and drop.
I just took the Excel file,
copied it on my laptop into this One Lake concept,
and now if you go back to the fabric and refresh, check this out.
I'll refresh this here.
Now inside. Now there's the file.
Why don't you even load this now is a Delta table.
Click load delta table here.
It's got the name already built-in, and now I'm done.
I had now have both of these files inside the fabric. Pretty cool.
Wow.
This is the promise of the fabric though,
integrating all your data in a one lake.
Now, I want to do some visualization.
In my mind, look I know Patrick Leblanc or Adam Saxton.
I want to create a Power BI report
based misstated to trend analysis.
The first thing I need to do is create
a relationship between these two tables.
I love this in the fabric and
the upper right-hand corner here you'll see,
let me go scroll over here for a second,
I can get what's called a SQL endpoint to my Lakehouse.
Let me click here, and when I do that,
I can use the model function
Power BI users will be familiar with this.
Now I can do a drag and drop to say, hey,
the customer sentiment data in this table is
really related to the customers and the sales data.
Now that I've created this and just confirm
the Power BI model knows this relationship exists.
What I want to do with this, I want to go
create a dataset and a report.
On back here in the lake house.
Now I've got my relationship.
I'll say new Power BI dataset.
Let me scroll over here and I want Power BI
to automatically create the port for me.
I'll be brought with screen here that'll say,
hey, would you like to visualize your data?
I'm like, yeah, I would but I know guy in a cube, so just do it.
Yeah, I know. It built this report and with a couple of clicks,
I got what I want it.
This is really powerful. Now what I could do,
just like you see in Power BI,
I can say, okay, let me go click on
a customer and its sales data over here,
and let me see if I can look at
the corresponding chart to look at the trend, for example,
or the higher sales from
customers have a certain pattern for sentiment
from products or salesperson or maybe even the middle tier sales,
or especially the lower sales,
like what really drives lower sales, and you can see in here,
there's actually salespersons sentiment in our case that
drove the lower sales data and I can
even drill in which salesperson was involved.
An analyst that's not
a SQL expert can easily use the fabric based on
existing archived data that I already built through
SQL Server to easily do trend analysis on the fabric.
That's crazy. You put all that together, isn't it?
>> Yeah. I mean, it's crazy. You put it all together.
I'm also super excited to go check
out the resources so I can try it myself.
I did have a quick question for you.
We had the parquet file,
we had the CSV file,
we turn it into
a Delta table coming from maybe a newbie or a SQL side of
you like are we duplicating the data there are
we creating a view on top of it, what's happening?
>> Yeah. In this particular case,
we're just getting started this journey
loading into Delta table is
a copy of my data based on my archive of Parquet.
But imagine a world where we've tightly
integrate this all together so you only not to do that.
This is just the start of the journey,
but yeah, those are copies of the data.
But it was so easy to do.
Here's the thing I can do with all different types of data.
Delta being this natural table format
for the fabric and for Power BI datasets,
it was easier to do it that way,
but men were just starting the journey here.
I expect a really
seamlessly integrate this together in the future,
even easier and quicker.
>> I feel like there's some
subliminal messaging you're giving the viewers,
are we going to do more with fabric and SQL Bob?
>> Absolutely. I think we think fabric is a data platform for a
lot of users to be able to
easily access data on all different types.
Why would Microsoft SQL or
flagship database product via bailout journey?
Yeah, I think you can look for more from our team,
the future on this integration.
>> Awesome, great, and then from your perspective,
folks who are just getting started with this wants some tips and
tricks and advice. Where should I go?
>> Well, you know me, I always provide more resources.
I got a bunch listed on here, and excuse me,
starting from the left-hand side and
the second one notice here getting started with the fabric.
The Fabric team has already built
a complete Microsoft learning path Anna to
get started with the fabric itself, already available.
Go out there and just today for
free trial and go you start with the fabric,
and you can even start it for like a trial version of the fabric,
which is free to get started with the service itself,
and on the top right-hand corner
you can see the demo I just gave you,
which is a slide deck with everything built into it.
Somebody could just go download this and see
the demo again and walk through it themselves.
>> Awesome, cool. Well Bob always happy
to have you on the show. I learned a lot.
I think our viewers learned a lot.
If you're a viewer, check the links in the description.
We'll put all of those links for you to go
learn more if you like this episode,
go ahead and give it a like,
leaves us comment and let us know what you think of
the scenario and what you'd like to
see from SQL and Microsoft Fabric,
and we hope to see you next time on Data Exposed.
[MUSIC]