>> Have you heard of window functions? I hadn't.
Learn in this episode how powerful window functions can be,
especially when it relates to operational analytics.
This week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today, I'm joined once again by Silvano,
a Program Manager on the Azure SQL Team.
Thanks for joining us again, Silvano.
>> Yeah. Thanks a lot for having me, Anna.
>> You're becoming a regular,
and we're really excited about it.
Today, we're going to be continuing
our discussion on operational analytics,
specifically related to Azure SQL and SQL Server,
and some of the capabilities that you have at your disposal to
make these things go faster and run more efficiently.
Today, I just want to do start by asking you,
Silvano, in this landscape
where we're doing operational analytics,
what are some of the complicated aggregations
or functions or things you might be doing in real-time,
and what are some complexities that might come up?
>> Yeah. We mentioned actually a lot in
these videos about real-time operational analytics,
and I think it's really important to start drilling down into
what kind of analytics we are actually going to run.
I prepared a couple of slides here.
In general, when we are dealing with
this kind of transactional entities,
you can think about inventories,
sales, warehouses, and so on.
You want to typically extract a bunch
of detailed rows about these transactions,
but you need also to aggregate some of these dimensions,
metrics in such that makes sense.
Think about, for example,
I'm running totals across a given inventory item, moving averages.
If we are analyzing sales in real-time,
comparison like a week over week,
month over month, statistical analysis.
We know that there are tools we can think about like
Power BI and DAX language that
have become specific capabilities in this space.
But those are typically intended for, again,
those offline type of
analysis on top of our data warehouse structure.
What we are talking about here as we were saying is, A,
let's do this real-time on top of our transactional data.
A lot of these capabilities are actually already
implemented in SQL Server and Azure
SQL through what is called window functions.
If you want to know everything about
this very powerful capability,
I recommend you this book by my friend Itzik Ben-Gan,
this is T-SQL Window Functions from Microsoft Press,
and it's fantastic to learn all about this particular one.
But let's take a look at the common scenario we are talking about.
So we have a bunch of sales data,
sales transactions, and those transactions are, for example,
showing us for a given stock item in our inventory,
what are the orders that have been
processed and over the last x minutes,
what is the quantity of a single order?
But we also want to know the aggregated total sold.
We know at the end of this time period,
how many items we have been selling about that.
We may want to know,
for example, things like, A,
what is the percentage of
that particular line item on top
of the entire order or for example,
on top of a given level of aggregations.
Let's take a look of
very common scenario where
we see we have transactional data items here.
We are capturing all sales data for
a specific stock item that we have in our warehouse,
and we see that each new order is basically
bringing a quantity of items that we have been selling,
and we want to calculate, for example,
running total of how many of them we've been
selling on top of a given time period, for example.
We want to know on that the same results set by the way,
how much a given row here is impacting on top
of the total sales for that item in percentage.
We can execute these queries.
There are a lot of real world situations where we want
to interrogate our sale system in real-time.
How we have been solving this problem in the past before again,
having tools like window functions,
what we were doing here,
we were typically creating more complex queries.
In this case, I'm using common table expression
here to define my result set,
my initial early days in the result set.
Then what I'm doing here,
I'm creating subqueries where I'm summing up
all the quantities in
the rows that are basically preceding the current one.
What I'm doing here, some on top of
all the line items that are
smaller or equal to the current one in my outer query.
Sorry. This is good,
this is actually working.
The problem is, we are typically increasing
performance the more our dataset is growing.
The more our time interval is growing,
our performance are basically degrading very significantly.
Actually, they are growing exponentially because what
the query plan basically will become is a very complex one
where this kind of recursion that we have is
basically bringing performance to
tens or hundreds of seconds in terms of execution time.
What we can do here instead, with the new school,
with this window functions is,
we can use this new keyword called OVER,
and basically we can define in our result set,
how we want to basically calculate
this particular aggregation function here.
In this case, we want to basically aggregate on top
of our stock item ID as we see ORDER BY,
in this case, OrderID,
and we want this sum operation here to get
executed on all the rows that are preceding the current one.
Rows unbounded preceding means exactly what I'm saying.
This is one way of actually executing,
defining these boundaries for
this particular aggregation function.
The second line here is slightly different.
We are still doing this over, but in this case,
we are just calculating
essentially the weight of the single row on top of
the entire result set for
this particular frame that we created here by filtering our rows.
The result here is,
as you can see, very performant.
Actually, the query that was taking
100 seconds before is actually taking 200 milliseconds.
This is because window functions are working very well in
conjunction with our cluster and column stores,
and they are also capable
of switching to the batch execution mode.
Batch execution mode is,
as we were discussing,
is the ability to basically execute, in this case,
aggregation function on top of multiple rows,
so it's not a row-based operation,
but is basically vectoring across a bunch
of rows this calculation.
>> Let's take a look at some quick demo here.
This is exactly the old-style query
that I was discussing previously.
We are running on top of
the same 500 million table
that I was showing you in previous videos.
We are just looking at
one single stock item here and we are aggregating,
we are calculating those running totals
on a 15 minutes time Windows.
By the way, just remember,
we still have our OLTP workload in the background,
which is inserting thousands of
rows per second in this particular table.
If we run this one,
rows are starting to flow immediately,
but as we can see,
the query is not over yet.
We are still aggregating rows.
We will see that this is probably going
to take again quite some time in order to get executed.
Let's give it a few seconds more.
Or actually, let's skip this.
We will go to this
later on and let's take a look instead how we can solve
the same problem by executing our Window function-based query.
This is exactly what I was showing previously.
Running on top of the same exact dataset, and boom.
We are executing in around 100
milliseconds the same query that here still has to complete.
The reason is exactly what I was mentioning previously.
Fortunately, this subquery-based approach
is basically going to be influenced exponentially by
the amount of data we have in that
results that we calculate while we are
basically executing at linear pace on the Window function side.
You can see the difference is pretty significant.
>> Wow. [inaudible] you definitely have my attention.
I haven't worked with Window functions a lot,
but two things that are very obvious,
sit-stand out here is one,
with Window functions, you have to write way last T-SQL.
It seems like there's a lot of built-in verbiage
that you can use to accomplish with way less code.
Two, of course, it runs so much faster.
Now, when I start to implement these Window functions,
it seems like there's some built-in words
and capabilities that I should be aware of.
Are there main things or main insights on how to set these up?
>> Yeah, absolutely. The two main parts
of this syntax is, first of all,
your preferred aggregation functions that you effectively
have to run on top of these Windows,
where Windows are effectively how you are
slicing and dicing your result set.
This over keyword here is doing
most of the magic and is basically telling us how we
want to operate on top of the result set that we are actually
defining with our very classic predicate here.
First of all, this is giving us
the main Window where we are going to
operate and then the over keyword
here is telling us how we want to slice.
In this case, we want to slice by this stock item ID.
Then we are basically telling with some few keywords,
how we are going to execute
that function that we have seen at the beginning of the line here,
how we are basically going to operate on top
of the remaining rows.
In this case, what we are applying to
all our preceding rows to
the one that we are actually executing in this particular moment.
This is the key of the entire feature here.
In terms of the Window functions available or
typically categorized between ranking functions.
Whenever we want to generate the sequences,
row numbers, for example,
to generate a new sequential number for each row,
rank the data between the different groups,
with gaps or without gaps.
We have standard aggregation functions here,
the sum, average, count and so on,
but also the statistical deviations,
statistical variance and such
plus a bunch of other analytics functions,
I would call it, so the ability, for example,
to go back one row and calculate for example,
the result or the difference between
the current row and the previous row and
a bunch of other very interesting functions
that we can use to provide,
to create powerful analytical queries
on top of our real-time data.
>> Wow, this is really cool, Silvano.
Again, this is something very new to me,
and to realize that we have all this built-in functionality,
it's going to make our lives easier,
not just from writing the T-SQL,
but also getting performance out of our data.
This is pretty cool. I'm really excited about Windows functions.
If you had one piece of advice that
you would share with folks as it gets
started with this and
implementing this or trying to find where this fits,
what piece of advice would you give?
>> Yeah, just go ahead and learn about this type of capabilities.
The more you can push your computation requirements
into the database and closer to where the data resides,
this will give you a lot of efficiency the
way you're implementing your solutions.
You could calculate
all this statistical and analytical functions on your application,
but why do that?
We have a powerful relational system
on the backer that is fully capable of executing
these particular features and
functions and actually do it very
efficiently because this is where the data resides.
I will definitely recommend you to take
a look at the books like the one that I mentioned
previously and learn about
Window functions because this will give you
a boost in terms of performance in your analytical solutions.
>> Awesome. Well, thanks so much for joining us on
this little mini-series about operational analytics.
I learned a lot and I'm sure our viewers have as well.
To our viewers, if you like this video,
please like this video,
subscribe to our channel,
leave us a comment and let us know what you think about
operational analytics with Azure SQL and SQL Server.
We hope to see you next time on Data Exposed.
[MUSIC]