SQL Server Analysis Services (Both Multi-Dimensional and Tabular) only supports Windows Authentication. However, Analysis Services Cubes in a PAAS (Platform as a Service) Service was a long time ask from Azure Customers. Microsoft on the 25th of October 2016, announced the public preview of a fully managed Analysis Services (Tabular) on the Cloud. Microsoft’s self-service BI was already available on the cloud, but a full-scale Enterprise ready OLAP engine wasn’t. Unless you take Azure VM’s (Infrastructure as a Service) into account, this service is totally new. Microsoft is known for deep integration with their existing products and SQL Analysis Services PAAS on Azure Cloud will not be an exception.
SQL Analysis Services PAAS on Azure Cloud
In order to set up Analysis Services, you would need to login to the Azure portal and do the following:
- Click New>Intelligence + Analytics>Analysis Services (Preview)
- Type a globally unique server name and choose a region. (Currently only South Central and Western Europe is only available)
- Choose a pricing Tier and finally click create
Screenshots are as follows:
Once the deployment is complete, you will see the Server name as follows: “asazure://southcentralus.asazure.windows.net/<Instance-Name>”
You can use the SQL Server Management Studio to login to the instance. Ensure that you use the most recent version of the Management Studio. See below:
For now, you will not be able to use the “Import from PowerPivot” option. You would need to use Visual Studio (SSDT) in order to deploy a Data-Model to the SSAS PAAS service on the cloud. In addition to that, you will have to use the complete server name in the Deployment Server Settings. See Below:
If you are using an On-Premises Data source like SQL Server, Oracle, Teradata etc. You will have to install the On-Premises Gateway application from this link. See the installation steps below:
Once the On-Premises Data gateway is connected to your Azure account, you should be able to connect to your on-premises data sources. Do note that the Power BI On-Premises gateway and the Azure Gateway cannot coexist and will not install unless you uninstall the other one.
Connecting to your SQL Analysis Services PAAS on Azure Cloud with Excel 2016
If you want to connect to your Excel 2016 Client, you would have to use the complete Server name. Since the SSAS PAAS Service uses an Azure Active Directory (AAD), you will have to pass the username and password as given below:
That’s it! You should be good to go. If you are facing any issues drop us a comment below in the comment section. Cheers!