Azure Analysis Services is essentially Analysis Services from SQL Server served as a platform-as-a-service in the Microsoft Azure cloud. The following are the steps I have taken to build, deploy and use Analysis Services for business intelligence. To get a complete overview and see more comprehensive steps, read What is Azure Analysis Services?
In the Azure Portal, create Analysis Service
Notice you can pause to save on costs, backup, set admins and browse models.
Note the Pricing Options
Open Visual Studio with latest SQL Server Data Tools.
Create new Project Analysis Services Tabular Project
Ensure Integrated Workspace is true
You may have to double click on the model.bim to generate the metadata. As a result, the Tabular Model Explorer displays as
Right click on Data Sources > Import From Data Sources > Select Microsoft SQL Azure
I have data prepared in my SQL Azure.
Select Service Account. Click next again.
Select tables to import
As a result of importing tables
After some modelling activities, such as creating relationships, date hierarchy, location hierarchy, show/hide tables or columns to client tools, etc.
To set up for deployment to Azure Analysis Services – right click on the project > Properties
Set the Server to the Analysis Services Server name asazure://canadacentral.asazure.windows.net/rkas
To deploy project and Deploy. You may get prompted for credentials. Ensure this is credentials for an account in your Azure AD directory (not a windows live account like hotmail) and is part of the Analysis Services Admins. Also, ensure you have installed the latest SSDT. Otherwise, you get a vague error message stating ‘An error occurred while connecting to the server.’
To test the deployment, I used SQL Server Management Studio to connect to AS and query the database
This confirms deployment was successful.
Also, using Power BI to connect to AS
Enter server Url to AS
Select the model
Create some charts with some measures, dimensions and hierarchies.
Azure Analysis Services is easy to work and the developer experience is quite the same with SQL Server. The ability to connect to other cloud data sources like SQL Azure and have Power BI service connect to AS provides an entire cloud solution.