Coming up on the show
Azure Analysis Services
a proven analytics engine to connect and analyze disparate data sources
whether you're on premises or in the cloud
without having to manage your own infrastructure
We're going to take a look at how you can gain insights from your data
using analysis services semantic model
the speed of in-memory processing and querying of your enterprise data sets
and migrating your on-prem data models to the cloud
I'm joined today by Josh Caplan from the Azure Analysis Services team
Welcome
Thank you, great to be here
So you recently announced the preview of Azure AS
and really it joins a spectrum of technologies for data management and business insights
but what's your team trying to solve for
So Azure Analysis Services is built on top of analysis services
which is a proven technology
that we shipped along with SQL server for many years now
and what it does is
it lets you take all that data that you have in your in your
databases your
data warehouses
and take all that business knowledge
that business logic you have
and you can combine together into a semantic model
to give access to your business users
to go query the data
not only can they query that data in a semantic model
but they can query it very fast
because you have this very fast in-memory cache
so we've taken that product
and now we've moved it to the cloud as a fully managed platform as a service in Azure
and now it's a lot different these days
because we've got a lot of disparate data that not only lives on premises
whether it's something like SQL or Oracle databases
but we're also getting a lot of data that's going natively to the cloud
with things like IOT devices intelligent devices
so bringing it all together into a single semantic model
at scale with all the speed that you're talking about
is pretty significant
but how does it work
Let me show you
so here we are we're actually in the Power BI desktop and we're connected to a semantic model
and the data for this model actually came from lots of places
it's literally a bunch of cabs
all the cabs in New York City
And those cabs are kind of like IOT devices in and of themselves, right?
yeah they're collecting the data
where they picked up their customers
how long the trips were
what they spent on each trip
And we've kind of taking that data we've aggregated it all together
and we've put into a SQL data warehouse
we built a semantic model on top of that data warehouse
and we've pulled that data into our cache
so you see here is not the data warehouse
but what you see is the semantic model
so everything's in business friendly terms
so if I wanted to do something like
what were the number of trips?
I can easily figure out how to do that query by going to the trips
and I can just see trip count
I can drag that onto our canvas here
and what you saw there's actually just ran a query
against all the data that we have on our trips
That's two billion
yeah so we scan just over two billion rows there
And what was that? A fraction of a second?
yep
so some advantages you have when the data is that quick
is you can ask all kinds of questions of it and get answers
so let's do some more analysis with this data
let's actually see where all the trips happening around
so let's put our location fields on here
with all the pickup locations for every cab ride in New York
and it's New York so there's lots of cabs and lots of rides
and lots of data and you just queried it in seconds
lots of data we just queried
and we just plotted every single point there
let's do a little bit more analysis because right now we have a lot of points
so let's see where are the most popular places to pick up rides
and where are the most profitable if we're a cab driver right?
Right!
so let's look at actually the revenue per trip
like how much money are we making on each trip on average
and let's look at it by different pickup location
pick up zip codes in this case
in New York there are two zip codes that have many more pickups and than others
any idea what those might be
every time I go to New York i'm using cabs at the airport
i'm guessing they were the two main airports
now let's see
we can click on them
we'll actually run another query in here that will filter down the map
just show the data I've been this point here
and you're right that's JFK Airport
right
and we click on the other one that would be LaGuardia Airport
and you can see lots of people picking up rides
and also lots of people waiting on the highway to actually get their ride
I guess it's busy there
right if I'm one of the numerous cab drivers
and want to figure out should I be going to JFK or LaGuardia
how do I do that?
well looking at the the chart here
you can see that you can actually make more money per trip
on average
from JFK than LaGuardia
but let's just let's just see how that holds up
when we ask some more questions about the data
so let's actually look at the average length of those trips
and how many are in those lens
so let's look at the trip count again
and this time we're going to look at it by the trip duration
and we're going to bucket them by that
and we switch this around so everything fits
so now I click on JFK
we're going to run our queries against that model
and you see that most of the trips to JFK are 30 to 45 minutes
pretty long
it is pretty long and we go LaGuardia
it's actually way more the trips are 15 to 30 minutes
much closer to the city right?
yeah definitely
so the amount of money you make per trip is very close
a little bit more JFK
but at the LaGuardia you can actually get more trips in
so I if I had to say
I would say i would go to LaGuardia if i was driver
i could do more trips with about the same amount of revenue
so we'll see if Microsoft Mechanics changes taxi driver behavior watching this
so it's really significant especially the terms of the volume of data is being processed
because we just showed two billion rows of data there
I know a lot of people are probably running SQL Server Analysis Services
how does AAS compared to that?
so this is kind of a typical topology you would see for someone running analysis services
they'd have their data sources
they have SQL server
they have even third-party data sources like Oracle and Teradata
then they pick another server
and they'd run analysis services on it
and they can connect to it to things like Excel and SQL server management studio visual studio
so very similar in Azure Analysis Services
but what we do is you take that analysis services piece
that was running on a server
and now you move that up to Azure it's in our platform as a service
and you can leave your data sources on-premise if you want
you can still connect with same Oracle databases through what we call a gateway
go all the way down to on-prem and connect to those sources
ok or you can move those sources up to the cloud
and put them in things like Azure SQL Database Azure SQL Data Warehouse
now give a full end-to-end data solution in the cloud
right and because its cloud-based
you have to worry about maintaining the physical servers that you would have done on-prem right?
right
so if your data grows
and you don't have to go out and buy a bigger server
you just go a couple clicks and you have more capacity
Has anything changed then when I move from on-prem into Azure Analysis Services
we spend a lot of time to make sure that we had compatibility
the same compatibility that we have on prem in the cloud
So SQL server management studio, SQL server data tools, Excel, Power BI
all those still continue to work in both places
How do I get all of this up and running?
well like anything in Azure or most things in Azure it starts in the Azure portal
I'm going to come in here and I can click new
and under intelligence and analytics
right
you'll find the preview for Azure analysis services
okay
you can click on that
you name your server
these are logical servers
just like other resources in Azure I can go pick my resource group
and i can pick my location
we have lots of locations to choose from
if you have employees all over the world
and you want to get your data kind of co-located with them
you not to go build a data center in their regions
you can Azure, Azure is all over the world
and so is Azure Analysis Services
we're in lots of regions now we're going to continue to add more
and the last thing you have to pick is your pricing tier
the way it's priced is basically
you pick different tiers based on the size of your data
and how many users you think you're going to have concurrently querying it
so that taxi data we showed was pretty big
yeah it was two billion rows and just a single table there
right
more than 2 billion
we're going to pick the biggest one here I'll select that
and we'll go ahead and say create
so that's going to go up and spin up a server AAS to connect to data sources
but we haven't defined all those things yet so
the taxi model that we saw was something that you might have created in SSAS
can I move that model then and bring that up into Azure Analysis Services?
yeah
what you do is
once the service is created, ours is still going
takes about five minutes
you can come in here you can grab
that server name
and then you can go into SQL Server Data Tools
this is where you build your models
so here is that model I showed you earlier in the demo
this is our taxi data
and it's connecting to a SQL Data Warehouse
but you don't you don't see the data warehouse here you see your semantic model
right
this is friendly table names
friendly columns
and you'll see the relationships between the table
this is how I was able to go just click on fields from different tables
and it automatically knew how to join it all together
because that meta data is here in this model
right
in addition to just relationships
calculations are in here
so remember how I just said "give me the trip count"
We can build those calculations in using the same query language you use in analysis services
and also the same query language you use in Power BI
DAX query language
so here you're looking at revenue per mile
which is a pretty simple calculation
it's just showing revenue divided by the trip distance
but you can make these advanced as you want
DAX is a very very powerful language
yeah, one tip if you're new to Power BI
you're going to want to learn DAX querying
How easy is it to target the new AAS environment for your model?
so we copied our server name earlier
all I have to do here is come into the project
go into the properties
and under deployment server
you see the server name?
this is pointing to my desktop before
on-premise
all I have to do is change it to server we just created
I can say ok
finally
come in hit deploy and that's it
it'll go push it right up to the cloud
that's all you're doing is changing the target path
from your on-premise server into the online server effectively
what if I'm a big enterprise
and I've got thousands or hundreds of thousands of data models
maybe not hundreds of thousands
but tens of thousands
how would I do that and automate it
So this is Azure now
and like other azure services
you can automate everything in there
so you can use the Azure ARM APIs
you can use ARM templates
and you can automate creating those thousand servers if you want to
Something you wouldn't easily do on-prem
and then you can also use the API that comes with an Analysis services
to automate the deployment of the models and the management of the models
even the processing
updating that cache keeping it up-to-date
all that can be automated
To sum up what we just saw
AAS is really easy to get started with
you can use your favorite tools to visualize
whether its Power BI, Excel, etc
to analyze that data
where else can people go to learn more and get really deep on this topic?
well you can click the link below to get started
the service is in public preview
so anyone can use it
and please give us feedback
this is what really what we use to drive the product and decide what we're going to do next
it's a really great overview thanks for joining us today Josh
and of course you can keep watching and checking back to microsoft mechanics
for all the latest in tech updates across microsoft
thank you all for watching
and we'll see you next time
Microsoft Mechanics www.Microsoft.com/Mechanics
Microsoft