Hey friends, you know, the aggregations feature for Power BI
enables interactive analysis over big data in a way that was not possible before and now
you can unlock petabyte scale datasets for clicky-clicky, draggy-droppy,
timey-wimey data analysis which is insane and Christian is here to show us how
to dramatically reduce the cost of unlocking big datasets in Azure, today on Azure Friday.
Hey, folks I'm Scott Hanselman and it is another episode of Azure Friday I'm here with Christian Wade and we're going
to talk about
big data. Absolutely. How big I'm talking massive okay
why massive like even bigger than the younger than I am talking huge.
Bigger than actually even bigger than all three of those combine all three of these combine okay well this is
about a megabyte you told me you're talking about petabyte yes petabytes
hh this is a difficult concept to allow mines to actually fat that we have to kind
of expand our minds we're actually talking about in the petabyte scale is what
we're dealing with here this is absolutely on another planet to anything we've seen before
for interactive analysis wow and
that what I'm having trouble getting my head around it you said we're going to be doing click drag-and-drop
e which doesn't parse with petabyte like I think a big data I figure
they must be a query languages something complicated I have to do and now you are saying I can go and
clan striving draw beauty of it we as a user
you can be a completely non-technical user who don't need to
learn spark sql you don't need to have consultants build reports
for-you you-can just immediately immerse yourself in the data and just click you
drag-and-drop the interactively create reports on-the-fly of a
petabyte scale data sets and guess what it's relatively very cheap compared to anything
but we've had before seriously so there's just no limits I mean the limits of, you know,
on another planet right on a completely different I try to ask the
computer how many megabytes of petabyte was it gave it to me and scientific
notation you'd never non zeroes so blow me away we're all right
okay that's no I'm going to start with this data
sets and this data set is data for a crowd source
to korea service where I smart phone map image the
drivers locations and it generates a ton of data so here we have
the location count measure which as you can see is simply the
count of all the rows in the table that's completely normal so,
I'm going to casually tracked this on to the campus and figure out find out how many rows we
have this thing now just to put this in context fees scope
of first-time I came here about a year-and-a-half of the big deal for males on as of friday and I was
going to show off to billion rows of data right and,
then I came back at school, you know, I'm pushing the boundaries even further I'm up
to ten billion rows of data I had four hundred gigabytes as
your analysis services with a twenty five calls and I was pushing the boundaries of what was
done before who guess how many rows we haven't here let me show you we actually have?
A trillion we've pushed beyond a trillion.
Rows of it as a thousand billion or million,
million exactly I'll.
Take your word for it because it's via it's on a leveled the is beyond anything the we've had
for is actually a quarter of a petabyte this is the order of a petabyte right here
from hd coming through from each
dye box absolutely on another planet we've just blown the laws of physics out of the water
which is partial and I notice either wasn't a pause there was no pose it was instant is this
is instance clarification let me show you how responsive this is?
We have let's go with say distance traveled
and break it out by that and we can just make it nice big we can make it
a bar chart we can break it out by the miles per job and we just get
in allow instance response times of a quarter of a petabytes
of data right it's just click and drag drop it and how is that possible as it preach
you the right indexes I mean back in the day twenty years ago I would have to even
do a table scan I have to think about indexes and clustered indexes on customers that
were preventive exact is how we've also at the dimensionality of the data how
is this physically possible so let's address that so the way that we achieve this blazing
false performance in our by is that we cache the data into memory
right now for these large-scale data sets you need a lot of that memories hence
agonized services has these four hundred gigabytes of skews so, what
we're actually doing here is we're still caching the data into memory but we're doing at
the aggregated level which uses
a tiny fraction of the memory requirements and unlocks these massive
data sets in a way that was physically impossible before and,
the resource usage is tiny and one locking these massive that's the thing about
is the business intelligence queries probably ninety five percent or above are
at the aggregated level so you're going to get very high
cash hits rights and, if the user happens stroll down to the
detail level it's okay just that it just submits a direct query to the schools which
I'll demonstrating just so, yeah, that was my question yes what percentage of these am I going to be, you know,
cash hit yes and what's one of them is going to me I have to go to lunch
yes come back so let me show you so let's say we want
to filter this thing by december twenty third
and I'm going to break count the location count and?
I'll make it a table finish still talking about twenty one million records at this point
yes cells on the exact to a single
dye in a single and this is only the jobs for the.
What over fifty miles for drivers who subsequently left the company
online because this app emits the location, you know,
little every few seconds so it just generates attire data and, now I'm going to break out the drive in
nine right so I've interactively of a quarter of a petabytes of
data I have generated a reports of drivers who left the company who worked on
december twenty third and perform jobs of fifty miles?
Just instance snout and all this time we've been hidden in memory cache because this some
level of aggregation right I'm now going to drill down.
And, I'm going to maybe look abigail johnson so I'm going to
go through to this movements reports of guys
can actually plots the individual locations for abigail
johnson on december twenty third right when reason that there's such a huge amount of data here is
we're talking about latitude and longitude over time over time so
it's city click stream data equivalent of driving around and moving around exactly,
exactly so when now kind at the detail level where we will have points.
For each row in the trillion row tables of it's not
hidden aggregation hence it's a little bit slower so it's digging into the join of,
you know, you find a single user out of a trillion and now you're bringing together for a
single day all of her movements exactly imagine the query is that right and it took at ten
second there is already done and if I show you here this is a twenty three node actually
I spot cluster okay so this just completed there's the filter for
abigail johnson and now we can see the abigail johnson had to drive all the way across
memphis this was the day just before the holidays looking for spend time of
the family she got frustrated she left the company and maybe she started her own counsels and company and
she's doing very well now the point b we've unlocked a quarter of a petabyte data and,
why that was physically impossible before using a tiny fraction of the resources
and if the user drove down to the detail level where there is no cash that's okay it just submits
a direct query to the source the source can be in this case
hci spark can be data breaks it can be agile sequel data warehouse and these
a so systems they tend to handle
these non aggregated filtered queries very well by the target us
exactly that targeted and the aggregated queries is really what our beyond does
really well so it gives you this balanced architecture where you can spread the load
of your queries across the architecture and really get efficient usage, yeah,
it's interesting cause you're seeing a separation of concerns about each am each
back-end data store doing what it does best yet but the front-end is presenting it in such a way that it's
seamless in the business users not thinking about picking a back-end absolutely so they can just have
this single reusable semantic model and enterprise organization if I happen to
go down to the detail level where there is not cash that's
okay it still works and unlocks these massive data sets and why those physically impossible before and
another point is that you don't actually need a trillion rose to use this right you can
just be in the range that you would have needed an address analysis services, you know,
one hundred gigabytes of four hundred gigabytes of and you had to do a do a lot of data
duplication and management overhead to refresh these caches whereas now you can refresh them very
quickly very easily make the data available for the users much quicker and much more
cheaply so some of the people who are watching might be thinking I don't have a petabyte data I only have a couple hundred million
rows, yeah, they're welcome as well saturday are absolutely welcome and they will
benefits laws absolutely which is hard to set up did you have to do anything
to this morning about aggregates is actually
funnily enough probably the simplest scaleability we feature we've
delivered in microsoft by I mean this is absolutely very simple so
in fact I'm going to explain it conceptually using these slides and,
I'll just move this okay, so?
Traditionally you would have to choose whether
the probably I'm model was import or direct query and it was a binary
decisions so the whole model all of the tables had to be important and you get that blazing
fast performance right we've had this really big data set it could be expensive
and time-consuming to generate that imports crashed in
memory data set so you could set up for direct query where
our by will dynamically submit queries to the source
on-the-fly like we saw against right I spoke but then all of the tables had to be
direct query which meant that for a big moderate tended to be slower right so what we're doing
now is we are bringing together the best of both worlds.
So, the in a single model we can at the table
level choose whether it's direct query our input which gives much more flexibility so,
basically what traditionally you'd have to pick the whole models are query import you can
now pick and choose which individual tables to crash where
really counts how would I know that until I've done some of these queries
is requires some planning and some forethought of yet the shape of the data and how
my business chooses will look at a potentially a little bit
we are introducing some capabilities to automatically suggest
the tables which get query most of the logs but,
business intelligence models would generally have a good fail you can say well these tables
of for my executive dashboard and they need to be super
falls from so, in this particular case let's say the cells type has ten billion
rows and doesn't habitual it could be ten billion, for example,
and, it could be quite expensive and time-consuming to cash so what we can do is we can
create an aggregated table that is going to be the sum of cells group by
tied the geography id and if you think about this is going to go from ten
billion to potentially thousands of rows is just going to be the cross join updates id and
geography id which is going to be tiny you've been refresh into memory very quickly using
a tiny fraction of the memory and then you set up the mappings so
that queries that a submitted to as the uses interact with the visuals
in paradise submits queries to the data set and as queries are submitted you'll see
that this query is just asking for some of cells amount it doesn't know anything
about the aggregation table the uses the consumers they know-nothing about this
and even the queries that need to know anything about the aggregation tablets completely
abstracted from the consumer but then the query comes in and it just says I want some
of cells and entirely the engine will re-direct that query to
the aggregation table knowing the this query that the granularity that can hit the
And, how fresher these things if I'm thinking about the amount of chewing and work that has to be done to
cash this is happening on a schedule I have to
give a freshness indicator yes so you can schedule it and it will
be much fresher than trying to cash the whole table because the whole time
would take a lot longer to cash so you could potentially refresh this, you know,
in seconds potentially depend because executives always say that they want
real-time data we all realize that real-time might beat every fifteen twenty minutes it doesn't necessarily mean exactly
so that's another feature is the unlocks that
data freshness and that near real-time capability being able to pick and choose which
time was to cash and not to cash opens up all of those scenarios as well
wildly yet so then a query comes in the again just toss the sum of cells
amount it doesn't know anything about the cells aggregation table but in this case it's grouping by
customer name custom attributes cannot hit the aggregation table because
for particular geography they'll be multiple customize it wouldn't know what which one to aggregate too so
it now on-the-fly internally reverse the direct
query and then submits the query to the source like we saw in the demo that is basically
hit is so simple so the powerful why did we do this I know?
We did we did some other versions of this before but nothing worked
as simply and as beautifully is this okay so when is available to do I have this now
to have to sign up for something all you have to do is download the september
powered by desktop release and it is there it's available it is
released for the masses is ready to use fantastic all right we are learning
all about petabytes of data and how to unlock that our here I measure
friday moi?