Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Microsoft Azure Analysis Services Web Development Tools - Part 2


By:   |   Last Updated: 2018-12-17   |   Comments (1)   |   Related Tips: 1 | 2 | More > Azure

Problem

As a SQL Server Business Intelligence (BI) developer I have been tasked to create an Azure SQL Database followed by creating a Tabular Data Model (by deploying the database) on Azure Analysis Services while my development machine is limited to using Azure resources only which means I cannot use any of the on-premises development tools such as SQL Server Data Tools (SSDT) or SQL Server Management Tools (SSMS).

Solution

The solution is to perform all the development steps starting from creating the database on Azure (as Azure SQL Database) to deploying it (as Tabular Data Model) on Azure Analysis Services by using Azure Resources including Azure Analysis Services Web Designer which is in preview mode at the time of writing this tip.

Web Designer Part-1 Review

Before we move further, it would be good to have a quick recap of part 1 of this tip.

In the first part of this tip we discussed the following things:

Database and Data Mode Development Environments

We discussed different development scenarios including the one which is applicable to this tip where the business intelligence (BI) developer’s development environment is limited in such a way that the developer can only use the Azure based tools to develop a SQL Database and a Tabular Data Model based on the database along with managing the data model.

Pre-Requisites

The pre-requisites were also mentioned which also apply to this tip.

In the pre-requisites it was mentioned to best understand the tip, the reader must have basic know how of a data warehouse and tabular analysis modeling concepts plus a valid Azure subscription which can be obtained free, but there are terms and conditions that apply.

You can create a free Azure account if you don’t have an Azure Subscription and would like to follow this tip.

Azure SQL Database Development through Azure Portal

The Azure SQL Database was totally developed using Azure Portal as follows:

  1. Signed up for Azure Subscription (Pre-requisite)
  2. Sign into Azure Portal (Pre-requisite)
  3. Created SQL Server Resource Group (Pre-requisite)
  4. Created Azure SQL Database (Resource) OfficeSuppliesSampleV4
  5. Setup Azure SQL Database Objects
  6. Populated Azure SQL Database
  7. A quick data check was done by querying the populated Azure SQL Database

Azure Analysis Service Web Designer Overview

Next, we mentioned that Azure Analysis Services Web Designer is available in preview mode (at the time of writing this tip) to create a BI Semantic Model as fast as possible.

Azure Analysis Services Web Designer is available as service to manage the data model by creating relationships, measures and so on without leaving the Azure Portal.

Please refer to the first part of this tip to see detailed information about the things mentioned in this section.

Web Designer Next Steps Summary

In this tip we are going to do the following:

  1. Creating (Azure) Analysis Services server resource
  2. Creating data model based on Azure SQL Database
  3. Creating Relationships between tables of the Data Model
  4. Querying the Data Model to get desired results
  5. Adding Business Logic by Creating Measure in the Data Model
creating and managing tabular data model on azure

Create Azure Analysis Server Resource

The first thing we need to do after the Azure SQL Database is created (in the previous part of this tip) is to create an Azure Analysis Services server resource so that we can deploy (add) the Data Model to it the same way we deploy a data model in tabular analysis services project for an on-premises Azure Analysis Services.

Launch Azure Portal and click + Create a resource from left bar.

creating a new resource in azure portal

Choose Analytics under Azure Marketplace and select Analysis Services as shown below:

choosing analysis services resource

Create the Analysis Services Server resource as shown below and click Create:

  • Server: ssasaway
  • Subscription: Please select your subscription from drop down list
  • Resource Group: Please select your resource group
  • Location: UK South (Please select your location originally setup with the resource group by you)
  • Pricing Tier: D1 (20 Query Processing Units)
 setting up azure analysis services server resource

Click Notifications (right corner of the top bar) to check for a successful deployment message and click Pin to dashboard and Go to resource:

notfications

The Azure Analysis Services resource has been successfully configured so we are good to add the Data Model to it. 

Create Data Model Based on Azure SQL Database

Go to the newly created Analysis Services Server resource and click Open under Web designer - preview:

opening web designer to create data model

In Microsoft Azure Analysis Web designer - preview click + Add under Models:

adding a new data model using azure web designer

Type in the New model name as OfficeSuppliesSampleV4Model and fill in the required fields to connect to the OfficeSuppliesSampleV4 Azure SQL database and click Next:

add new model based on azure sql database

Select all the tables to be included in the Tabular Data Model and click Create:

selecting database tables to be included in the data model

Creating Relationships Between Tables of the Data Model

Next, we are going to create relationships between the Tabular Data Model tables.

Switch to Diagram View to create tabular relationships by clicking on the Diagram icon at the top:

creating relationships

Click automatically arrange once the diagram view is active:

quickly arranging tables in the diagram

Create relationships by joining the Customer table CustomerID with the Orders table CustomerID using the drag and drop method and finally click Save and Close:

creating relationships between orders table and customer table

Next join the OrderType table with the Orders table through using OrderTypeID as follows:

creating relationships between ordertype table and orders table

Similarly create tabular relationships between the Product table and the Orders table using ProductID followed by joining the Date tables Date_Key with the Order tables OrderDate and click Save Layout:

data model tabular relationships Source control Add Tables Edit Relationships Save Layout

Querying the Data Model to Get Desired Results

Now that we have added the data model for the Azure SQL Database and created relationships between the Data Model tables, we can query the Data Model the same way we query a SQL Database using the DAX (Data Analysis Expressions) formula language or the drag and drop method:

Switch to Query mode, expand the Orders table and select TotalPrice (the sale price) field under Orders and click Run:

querying data model to get sum of totalprice

Getting Sum of Sale Price by Quarter_Name

Let’s analyze the further by adding the Quarter_Name field from the Date table to see the Sum of Price by Quarters (running the query):

viewing sum of price by quarters

Getting Sum of Sale Price by Customer

Now uncheck the Quarter_Name field and add the Name field from the Customer table to see the sum of (sale) price per customer as follows:

sum of price for the orders

Creating Measures

Let's add some business logic by creating a measure.

Create measure TotalCustomers by expanding the Customer table and clicking Measures in the Query Tab:

creating measure

Click Add Measure and type the measure name as "Total Customers" and add the DAX formula for the measure as follows and click Save:

COUNT('Customer'[CustomerID]))
total customers measure name

Check the Total Customers measure that was just created and click Run to see it working:

viewing total customers measure results

Congratulations, you have successfully created and analyzed a Tabular Data Model based on an Azure SQL Database without even leaving the Azure Portal.

We have used the Azure Analysis Services Web Designer to create a Tabular Data Model from an Azure SQL Database along with creating tabular relationships, a measure and querying the model while staying in the Azure Portal.

Please note that since Azure Analysis Services Web Designer is in preview mode at the time of writing this tip, it is not recommended to use for Production Environments yet, but please feel free to explore and use for development and testing.

Please don’t forget to free up resources by deleting the Azure SQL database, the Tabular Data Model and the Azure Analysis Services server resource after you are done testing with them.

Part-1 and Part-2 Summary

The overall steps mentioned in part 1 and part 2 are summarized as follows:

  1. Create a new Azure Subscription (Pre-requisite)
  2. Sign into Azure Portal (Pre-requisite)
  3. Create SQL Server Resource Group (Pre-requisite)
  4. Create Azure SQL Database (Resource) OfficeSuppliesSampleV4
  5. Setup Azure SQL Database Objects
  6. Populate Azure SQL Database
  7. Do a quick data check by querying the populated Azure SQL Database
  8. Create Azure Analysis Server resource
  9. Create data model based on Azure SQL Database
  10. Create Relationships between tables of the Data Model
  11. Querying the Data Model to get desired results
  12. Adding Business Logic by Creating Measure in the Data Model
Next Steps


Last Updated: 2018-12-17


get scripts

next tip button



About the author
MSSQLTips author Haroon Ashraf

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 28, 2019 - 6:33:47 PM - Haroon Ashraf Back To Top

Please note that Microsoft has recently announced to discontinue Azure Anlaysis Services Web Designer as early as March 1, 2019 which has been in public preview since July 2017 due to certain compliance requirements.

However, this will not affect creating and populating Azure SQL Database directly using Query Editor available on Azure Portal mentioned in this tip but to create data model on Azure (cloud), one of the available options is to provision Azure VM to setup dev tools now.


Learn more about SQL Server tools