Analysis Services in Azure, when and why…

By | November 7, 2016

As you probably have seen is that Analysis Services is now available in Azure and you can read all about in this great blog post by Bret. I have spend a few days at the SQLClinic at SQLPass talking to many of you, one of the topics that often came up is on when it would make sense to use Azure Analysis Services instead of running SSAS on your own hardware or even on VM’s in Azure. In this post I want to look at when and why you might want to look closely at Analysis Service in Azure.

Ok so you are a SSAS developer or admin and have heard about SSAS in Azure, why would it be interesting for you or your company to look at Azure:

  1. If your data is already in Azure it makes sense to also have your analytics in Azure. By the way the data doesn’t have to be in Azure for AS in Azure to work, there is a gateway available as well: )
  2. You get HA build in. For those of you who have set up HA with SSAS before recognize that this is huge, if you do it yourself you have to get 2 servers with SSAS on it and probably even a NLB.
  3. There is a pause button. Ok lets say you have a downtime of 4 hours a day where everyone will be asleep and no processing is running. Great you just pause the machine and pay NOTHING at that time.. you could even automate the process as SSAS is an Azure service the pause functionality is build into the ARM api’s so you could build it into the nightly update process
  4. You get to use the latest version of SSAS all the time, don’t worry about patching or maintenance
  5. You can use Azure AD, this could be great if you want to allow connection of SSAS to external users using Azure B2B  (Notice: just heard back from someone who tried, it doesn’t work. The team will look into this.)
  6. No worries about compatibility, you can use everything you used with SQL 2016 from scripting to functionality

And this are just the things you get in the current preview. If you look at the Azure SSAS uservoice you will see that the team is working or planning several features that will really blow your mind away:

  1. Scale up and down. Now imagine you have a cube for your fiscal users and the fiscal year is getting close. The finance department goes into overdrive and goes from 20 concurrent users to 200. No problem, just scale your SSAS server up to get more CPU power and when the fiscal year comes to a close you just scale it down again.
  2. Scale out.  Now this gets even better, many of the AS enterprise customers today already set up a farm of SSAS servers, commonly with multiple query nodes and a offline processing node. Imagine that AS in Azure can do this for you with a push of the button .
  3. Geo replication Imagine you have users worldwide and you want to make sure the latency is as small as possible. As a remote worker in Europe with most of my collateral in Redmond I know all too well how important this is Smile.

And this is just the beginning!

Now a few points that you might to consider when thinking about growing up your Power BI desktop file to a real server. According to the uservoice this is also planned (of course it is !)

  1. You get dedicated capacity for your models, Power BI is not dedicated
  2. You can create partitions to manage loading of the data at your frequency, want to refresh parts of your model every 5 minutes… now you can
  3. The size limit is much larger (depends on the capacity model you chose)
  4. You can use ALM and check in your model into source control and use your traditional deployment methods to get better control of your models lifecycle (wouldn’t it be cool if we had deployment slots? GO VOTE and request Smile)
  5. You have full access to the API’s SSAS has to offer, so much flexibility!


Just to mention support for Multi dimensional models, this is under review by the product group as you can see here, go vote if you want Multi dimensional models. The more noise you make the sooner it happens Smile. Tabular was easier for us to bring to the cloud seen the years of experience we have on hosting SSAS in a cloud like fashion either as PowerPivot for SharePoint and as part of Power BI. Also I would think the grow up story from Power BI desktop files to a Tabular server on Azure is more likely to get more customers initially. A lift and shift from your corporate on prem MOLAP model will be a longer project, now you can start having this discussion and be ready for when it actually happens :).