[MUSIC]
Hi, I'm Josh Caplan, Senior Program Manager for Azure Analysis Services.
In this session, we're going to talk about how you can model complex data
into actual insight with Azure Analysis Services.
Azure Analysis Services is built on top of proven technology of
SQL Server Analysis Services, which has shipped with the SQL product for
many years.
It allows you to take all that data that you have hidden away in data
warehouses, or data marts, and model that into a semantic model which
encapsulates all your business logic.
And makes it much friendlier for
your business users to come in and query.
Now that you have all your business users coming in to create your data,
you can also use Analysis Services to cache that data.
So it's very quick for them to access it and
it reduces the load on your underlying data marks.
We've taken SQL Server Analysis Services and we've moved
it to the cloud as a fully managed platform as a service within Azure.
You can keep your on-premise data sources by connecting to them
through a gateway or you can move to more modern data sources like
Azure SQL Data Warehouse or Azure SQL Database.
Then you can connect it up to Power BI or
other third party client tools.
Let's take a look at how this works.
What you see here is an Azure SQL Data Warehouse, and what we've done
is we've collected data from thousands of different taxi cabs in
New York City and we've pulled that data into this data warehouse.
Now the data warehouse you see is optimized for loading that data,
transforming that data, and not necessarily for querying.
If I wanted to open this up for business users to come in and
do their own analysis here, first of all, they'd have to know SQL.
But they'd also have to know the scheme of the database, how to join
the different tables together, how to perform certain calculations,
it's not necessarily the most friendly.
So with Azure Analysis Services we can build a semantic
model over this data.
And this is just one example and same data set.
What we did is we pulled some of those tables and
we've transformed them.
We've put friendly business names on there, we put friendly column names,
we put descriptions on there.
And we've put calculations in here like miles per trip, or
number of trips.
And we've also put the relationships between the tables.
All this is metadata that the user would normally have to know if they
were querying against the database directly.
But in the semantic model it's all stored for them.
So you can come in to tools like Power BI or Excel or other tools,
and they can just simply drag and drop and query the data.
So if I wanna know something as simple as what
are the total number of trips in New York City from cabs, I can go and
just take trip count and pull that onto the field.
And you can see, when I pulled that on there, we actually went and
ran a query against Azure Analysis Services and
we scanned all the roads in the trip table and returned the results.
We scanned over two billion rows to get that number.
That's New York City, so there's always a lot of trips.
But let's look at where in New York those trips are occurring.
We'll take our latitude and longitude and
add those to the report, and I'll throw revenue on here.
So what we did is we just ran a query to figure out where all
the trips in New York City are occurring,
where we're picking up customers.
Each one of those dots represents a trip where a customer was picked up.
And the size of the dot represents the revenue that was made for
that trip or the fare for that trip.
And it's New York, so again, there's lots of trips.
But if I'm a cab driver, where is the best place for
me to go pick up customers?
Where do I get the most customers and
where will I make the most money per trip?
So let's look at the most popular pick up locations in New York.
I'm gonna throw on here the revenue per trip, and
we're going to look at the zip codes,
and we're gonna order this by the number of trips per zip code.
Again we just ran another query against Azure Analysis Services
scanning those two billion rows and returning our results very quickly.
It's very quick, again,
cuz we're using that cache, the data is loaded into that memory cache and
it's not going back to the actual SQL Data Warehouse.
So you can see two zip codes stand out
much bigger than others in New York.
And you can guess what they are, but
you don't have to guess, you can click them.
And when I click on one of this,
it actually go run additional queries here and here to filter down
these other two visuals to just show the data for this bar right here.
So you can see the first bar, the first zip code,
is JFK Airport, which makes sense.
And the second one is gonna be LaGuardia Airport,
there's two airports in New York.
And you see people, lots of trips happening there, and
people lining up on the highways to get their cab.
So looking at this, if this was the only data I have, I could see,
okay I could make more money per trip by picking up people at
JFK Airport rather than LaGuardia.
But since we have a lot of data in here and the queries run very fast,
you can ask lots more questions.
Let's actually look and see where I can get the most trips from,
not just the most revenue.
So I'm gonna throw our trip count back on here, and
I'm gonna slice this time by the average trip duration.
I'm gonna make a little bit of room here.
And there's a neat trick in here in Power BI where you can actually
change how the visuals interact.
So I'm gonna do is I'm gonna change how this visual
interacts with this one.
Instead of slicing it's actually gonna go ahead and filter it.
So now I click on JFK, the bar for JFK,
you only see results down here for JFK.
Now you can see the vast majority of the trips for
JFK are between 30 and 45 minutes.
There are some there between 15 and 30 minutes.
Whereas LaGuardia, way more trips are in that 15 to 30 minute range.
Now you make the same revenue per trip almost between the two,
I think they have some flat rates there.
But you can get a lot more trips from LaGuardia
than you can from JFK, just because they're more centrally located and
they're usually shorter trips as the data is showing us.
This is one way that you could explore large amounts
of data using Azure Analysis Services.
As you've just seen, Azure Analysis Services has all
the same benefits as SQL Server Analysis Services,
except it's in the cloud as a fully managed platform as a service.
Try it out yourself by visiting the following links to learn more.
Thank you for watching this session.
[MUSIC]