Finally “True DevOps” became real for a BI Engineer / Analyst with the introduction of Azure Analysis Services. Even though there are lots of “In Cloud Analysis Services” service providers exist since recent past, nothing was able to reach the capabilities provided by the “On-Premise Microsoft Analysis Services” solution. With the introduction of Power BI almost everyone was expecting Microsoft’s Analysis Services solution in cloud and with the recent public preview to Azure Analysis Services, Microsoft really made us happy.
2016 – A Happy Year For Microsoft BI Stack Engineers
Microsoft BI stack is a combination of SS*S tools. They are SSIS (SQL Server Integration Services), SSRS (SQL Server Reporting Services) and SSAS (SQL Server Analysis Services). With SQL Server 2016, Microsoft introduced SSIS with cloud, where engineers can create flows to read from and write to cloud storages and connect to Azure data sources. On the other hand, Power BI was highly motivated and recommended to visualize the data. So when it comes to Cloud-based Microsoft BI stack, the missing part was Analysis Services. Now almost near to the end of 2016, Microsoft announced Azure Analysis Services. So, you don’t need to worry about maintaining an on-premise infrastructure for your MS BI solution, just deploy them in cloud.
Current (preview) offering does support tabular models at 1200 compatibility level, which means it runs on SQL Server 2016 instances only. Thus you can only use SSDT for Visual Studio 2015. Unfortunately, there is no support for Cubes (but I found in some Microsoft documents, saying it’s coming soon).
On-Premises Data Gateway
Similar “On-Premises Data Gateway” that used in Power BI can be used to refresh the cloud data in the model.
- The gateway cloud service analyzes the query and pushes the request to an Azure Service Bus.
- The on-premises gateway polls the Azure Service Bus for pending requests.
- The gateway then gets the query, decrypts the credentials, and connects to the data source for execution.
- The results are then sent from the data source, back to the gateway and then on to the Azure Analysis Services database.
How to Deploy – With On-Premise Data
This is the most common scenario and It’s very easy.
- Loing in Azure Portal and create an Analysis services resource. (When it comes to Roles, Microsoft accounts are not supported. You have to have an account configured with Azure Active Directory).
- Download, install, configure and run On-premises data gateway.
- Open an existing Tabular project.
- Go to Project properties and change “Server” as your Azure Analysis Service server name (which looks like “asazure://[region]..asazure.windows.net/[your instance name])
- Finally, deploy the project – Simple.
How to Deploy – Data Stored in Cloud
This is more futuristic scenario, where your data is stored in any form of cloud storage supported by SQL Server Analysis Services. If already created a tabular project to use these data sources, all you have to do is, just change the deployment server name (as mentioned in step 4 above) and deploy the project. Else, you can simply create a Tabular project to use those data sources and deploy them to the newly created Azure Analysis Service.
Options For Cubes
Unfortunately, Microsoft is really focusing on Tabular Model. Its true that their Cube solution is really matured and yeah, it lacks the very latest cloud possibilites. Microsoft, these days seems putting more priorities and investments towards Tabular Model which supports both MDX and DAX. As I mentioned earlier, I found some document saying Cube is coming soon (if I remember properly I saw a video demo, where this guy demos Cubes).
It really worth waiting but if you can’t keep calm, you can always use one of the SQL Server Business Intelligence in Azure Virtual Machines.