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:
- Data isn't much good without information
- 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.
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.
Comments