[MUSIC]
Hi, I'm Josh Caplan, Senior Program Manager for Azure Analysis Services.
Today, we're gonna talk about Enterprise Data Warehousing in
the Cloud, and how you achieve this with Azure SQL Data Warehouse and
Azure Analysis Services.
Before we get into the details,
let's take a look at this in action with a quick demo.
So what you see here is the Power BI desktop
connected to an Azure analysis services model.
The data in that model shows data from trips from New York City
taxi cabs.
Now we have information on every trip.
When a customer was picked up, how long the trip was,
where they were dropped off at.
So if I wanna know something simple,
just how many trips were there in this time period?
All you have to do is go to the trips table and
find the trip count measure.
And I can just drag that onto here.
And what this is gonna do is actually run a query and count
all the rows that are in our trips table, inside of our data warehouse.
Now, what it did here is it scanned that data and you'll see it returned
just over two billion rows, and it did that in a fraction of a second.
The way it was able to do that was by,
it actually scanned the data in memory, in our cache,
rather than going all the way back to the data warehouse.
Now, when the data is this fast,
you can start to ask more questions of it.
So let's look at actually where all these trips are occurring.
We're gonna add our pick-up latitude and our pick-up longitude.
And we'll look at this by revenue.
And you can see there are lots of trips to New York,
as to be expected.
And we've just plotted them on a map.
Every time I clicked there it actually went and
ran queries back against Analysis Services, and returned the results.
And, you see, it's a lot of results.
So let's actually do analysis.
If I was a cap driver, where would be the best place for
me to go pick up my customers?
So let's look at the revenue per trip,
how much money I can make per trip,
and we'll look at it by the top ten pickup locations by zip code.
Again, this just ran additional queries out to analysis services,
and returned the results back.
And we can see two big locations stand out in New York.
If I want to know what they are, I can click the first one, and
actually filter down the other two visuals on the report.
You'll see the first zip code is actually JFK Airport.
To be expected.
You need a lot of taxis from the airport.
And, a second one is LaGuardia Airport,
as there's two airports in New York.
Gets so popular, you have to line up on the highway to get your rides.
So, looking at this, I can tell that I will get more
revenue per trip if I go to JFK, rather than going to LaGuardia.
However, where can I get more trips?
Where, if I'm a cab driver,
should I go to make the most money I can in a day?
So let's look at the actual number per trips.
Put our trip count back on here.
And let's slice this by the average duration of each trip.
I'll make some room here.
We can fit it on our screen.
I wanna use a little trick here in Power BI that le's me change how
the visuals interact with each other.
It's gonna make it so when I click on something here,
it actually filters this visual down, rather than slicing it.
So now I click on JFK, I only see trips for JFK.
And you can see, most of the trips from JFK are 30 to 45 minutes.
JFK's kinda far from downtown, so that's kinda understandable.
If we look at LaGuardia, however,
you can see the vast majority of the trips are just 15, 30 minutes.
Now, the revenue per trip is still pretty similar.
I think they have some flat rates in New York.
So, while you can make more money per trip with JFK,
you actually do way more trips if you pick up customers at LaGuardia.
So, this is just a quick example of how we can do query on top of large
amounts of data, from data that was collected in SQL Data Warehouse, and
then processed into Azure Analysis Services.
And we can do this very, very quickly Now you've see the demo,
let's look at how we built it.
We collected data from thousands of taxi cabs in New York City, and
we've loaded it to an Azure SQL data warehouse.
Now, that data warehouse is optimized for
loading the data and transforming it.
In order to give our business users access to that data,
we then built a semantic model on top of it,
using Azure analysis services, and processed that data in memory.
We use Azure functions to coordinate the processing
of the new data as it arrives.
Then we can connect to with Power BI or other tools.
Now, why do we built the semantic model, when instead of just
letting the users connect directly to the data resources?
We can just as easily connect Power BI or Excel or other tools to
Azure SQL Data Warehouse, and you just query from there.
However, Data Warehouse is not usually optimized for
business users to be connecting directly to it.
It's optimized for data load and data storage, and it doesn't usually
handle lots and lots of different queries being thrown at it.
So to make it easier for business users to accrue that data,
without having to know which tables to join on, and
which filters to apply, we built the semantic model layer with our
analysis services in the middle, which contains all this information.
In addition to that, we also cache the data in memory,
in our in-memory columnstore engine, so that when queries come in,
they're actually intercepted by analysis services and answered out
of memory, rather than having be sent back to the data warehouse.
This means that your data warehouse does not have to be scaled up to
handle every single query that comes in.
After it's been transformed and loaded into analysis services,
you can actually pause that data warehouse or
scale it back down to save money.
To learn more about Azure Analysis Services or SQL Data Warehouse,
please check out the following links on Azure.com.
Thank you for watching this video.
[MUSIC]