Sign in Subscribe

6 Steps to Microsoft Azure Data Analytics

Dennis Faucher

• 5 min read

I noticed the other day in the Azure blog that the Analysis Service was just released.  I was interested in trying this out for a few reasons:

  1. Data isn’t much good without information
  2. This would be a good way to spend some more time with Azure, Visual Studio, SQL Server Data Tools and Power BI
According to Microsoft: “Built on the proven analytical engine in Microsoft SQL Server Analysis Services, Azure Analysis Services delivers enterprise-grade BI semantic modeling capabilities with the scale, flexibility and management benefits of the cloud. Azure Analysis Services helps you transform complex data into actionable insights. And by leveraging the skills, tools and data your team has today, you can get more from the investments you’ve already made.”


Here is where Azure Analysis Service fits in with other Microsoft products both on-premise and in the cloud.


Step 1: Gather All the Parts

You will need Azure, Visual Studio, SQL Server Data Tools and Power BI.  Install all components.


Step 2: Create an Analysis Service in Azure

From the Azure Portal, click + New, Intelligence + analytics, Analysis Services (preview)

Fill in the information about your analysis server



Step 3: Create a Data Source

 From the Azure Portal, click + New, Databases, SQL Database


Fill in the information about your SQL database including the two required subsections.





Step 4: Create a Data Model in SQL Server Data Tools 

Open SQL Server Data Tools which will open within Visual Studio.
Click File, New, Project…, Analysis Services, Analysis Services Tabular Project, OK



Click Integrated Workspace, OK



Click Model, Import from Data Source…, Microsoft SQL Azure, Next



Connect to the SQL database using the properties page of your Azure SQL database.  Click Test Connection when complete.



Make sure you have opened up your firewall in Azure 🙂





If you need to open up your firewall for your SQL database, choose “Set server firewall” at the top of the properties page for your database.






Click Next and select Service Account



Choose the option to select the tables to import and click Next



Select the tables for analysis and click Finish and the tables will be imported.





Click Close.  Your tables will all be imported into the cache into separate tabs.



You can also switch to Diagram View to see relationships between tables



You can even edit fields, tables or relationships in Azure from within SQL Server Data Tools






Step 5: Deploy the Data Model to Azure Analysis Service

Copy the URL of your Azure Analysis Server from the Azure Console

Click View, Solution Explorer in SQL Server Data Tools
Right click your project and choose Properties
Paste your Analysis Server URL into the Server field and click OK


Right click the project again and click Deploy

You might be prompted to authenticate to Azure

You can then watch the Deploy progress window

When complete, click Close
You now have an Azure data model that can be queried from tools such as PowerBI 


Step 6: Query the Data Model in PowerBI



Open PowerBI Desktop


Click Get Data, Analysis Services

Paste the URL of your Azure SQL Analysis Service and click OK
Select your model and click OK

Your Azure Analysis Server fields will be listed in PowerBI for visualization

Here is an example of the map visualization using the City field from Azure zoomed into the Seattle area.  I think map visualizations are very cool 🙂
Here is another map visualization based on Zip Codes zoomed out to the entire US
Here is an example of a tree map based on the count of colors in the Product table.  Tree maps are one of the fastest visualizations to comprehend.
That should give you an idea as to how to connect your favorite tools to the Analysis Service in Azure.  For a more detailed example, take a look at this video.  I hope you have found this blog post informative and helpful.  I welcome your comments and suggestions.