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


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

Problem

As a 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.  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). What are my options?

Solution

The solution is to perform all the development steps starting from creating the database on Azure (as an Azure SQL Database) to deploying it (as a Tabular Data Model) on Azure Analysis Services by using Azure Resources including the Azure Analysis Services Web Designer (Preview).

Background

It is important to understand the background of the above scenario before we jump into the solution.

Database Development Sandbox

In normal circumstances, developers (Business Intelligence developers in our case) are provided with fairly powerful machines with all the necessary development and test tools to help them speed up the development process. A developer sandbox environment comprises all the necessary tools and technologies available locally (on his/her machine) which can help him/her build, test and share code quickly and efficiently.

For example, a database developer using SQL Server Data Tools (SSDT) to design, develop, test and deploy databases might be equipped with debug database, a local development database followed by pushing his/her changes to the shared development database.

Please refer to my tips SQL Server Reference Data Best Practices - Part 1 and Part 2 to understand how database developers utilize development environments to create and deploy database objects.

developer sandbox

Data Model Development Sandbox

When it comes to Tabular Data Models, developers can make use of an integrated workspace.

According to Peter Myers, to begin development you need to connect to an in-memory engine (VertiPaq engine) and there are couple of ways to do this:

  1. Developers can connect to their own workspace (SSAS) server (installed on their machine or somewhere on the network)
  2. Integrated Workspace is an embedded tabular analysis server available within Visual Studio.

Once the developer has successfully created, populated and tested a database, the next thing is to add the database to the tabular model project using SQL Server Data Tools (SSDT).

Integrated Workspace server is available with the latest versions of Visual Studio (for example, Visual Studio 2015 and Visual Studio 2017 offer integrated workspace server) otherwise you have to install Tabular Analysis Server on your development machine or on the network.

Whether the developer installs tabular analysis server on a development machine or makes use of an integrated workspace it can easily be managed within the developer sandbox environment.

Please refer to my tip about Create Tabular Model Sample from SQL Server Database - Part 1 for further information.

developer sandbox

Database Development Sandbox in Azure (Cloud)

Another interesting example of a developer cloud powered sandbox is when a development environment is connected with Azure development/test environment. For example, a Database is created by first creating a SQL Database Project in SQL Server Data Tools (SSDT) and then database changes are later deployed to a development database which is Azure SQL Database.

Data Model Development Sandbox in Azure (Cloud)

Just like Azure SQL Database serving as a development database connected through SQL Server Data Tools (SSDT) we can create and manage a tabular analysis services project by setting up Azure Analysis Services as a development server.

Development Tools for Designing Tabular Data Models

So, essentially the following development toolset is required when working with Tabular Data Models:

  1. SQL Server Data Tools (SSDT) to create and deploy a tabular model project
  2. SQL Server Management Studio (SSMS) to manage tabular analysis databases

Azure Only Development Scenario

In all the above cases, the development tools such as SQL Server Data Tools (SSDT) provided to the developers are installed locally or somewhere on the network. The developers use development tools to do on-premises development work such as creating a SQL Server Database and connecting to on-premises Tabular Analysis Server or they can work on the cloud such as connecting to Azure SQL Database and Azure Analysis Services, however, in both cases the development tools are essential.

This tip is focused on a somewhat exceptional scenario where the developer is limited to use whatever is available in Azure (Portal) to develop a database and then create a Tabular Data Model using Azure Analysis Services.

azure tools

Pre-Requisites

This tip assumes that the readers are familiar with basic data warehouse concepts and have basic knowledge of working with tabular analysis projects both on-premises and on Cloud (Azure).

This tip also assumes the following requirements are already met if you are interested in following the steps:

  1. You have an active Azure subscription to create Azure SQL Database(s) and Azure Analysis Services in-memory database (Tabular Data Model).
  2. A Resource Group has already been setup under which Azure SQL Database and Azure Analysis Services server resource is going to be created.

Please create a free Azure account if you don’t have an Azure Subscription and would like to follow the steps in this tip.

Begin Azure Only Development through Azure Services

As discussed earlier, the developer is not able to use any of the available development tools such as SSMS to create databases or SQL Server Data Tools (SSDT) to create BI semantic BI Models, so the only option left is to perform all the development through the Azure portal.

The first step in this regard is to create an Azure SQL Database which is going to be fed into Azure Analysis Services later on.

Create Azure SQL Database

To create an Azure SQL Database through the Azure portal please first sign into your Azure Portal.

Once you are in your desired Resource Group please click New Database as shown below:

Type the database name OfficeSuppliesSampleV4 and select the pricing tier and other options as follows and finally click OK:

azure portal

Have a look at the newly created Azure SQL Database:

azure portal

Open Azure SQL Database Query Editor

According to the Microsoft documentation, SQL Query Editor is a browser query tool that provides an efficient and light weight way to execute SQL queries against your Azure SQL Database without leaving the Azure Portal.

Click OfficeSuppliesSampleV4 Azure SQL Database and on the database page Click Query Editor in the left-hand menu:

azure portal

Login as Admin and type the password you setup for admin when installing SQL Server resource group:

azure portal

Setup Azure SQL Database Objects

Once you are in the Query Editor (Preview) setup Azure SQL Database objects by running the following script in Query Editor:

--Open Azure SQL Database OfficeSuppliesSampleV4 Query Editor in Azure Portal

-- (1) Drop tables if already exist
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Orders') DROP TABLE  Orders
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Customer') DROP TABLE  Customer
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Product') DROP TABLE  Product;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='OrderType') DROP TABLE  OrderType;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Date') DROP TABLE  [Date];
GO

-- (2) Create Product table
CREATE TABLE [dbo].[Product] (
    [ProductID] INT             IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (50)    NULL,
    [Stock]     INT             NULL,
    [Price]     DECIMAL (10, 2) NULL,
    CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC)

);

-- (3) Create Customer table
Create Table Customer
(CustomerID INT IDENTITY(1,1),
Name VARCHAR(70),
Email VARCHAR(320),
Phone VARCHAR(50)
CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

-- (4) Create OrderType table
CREATE TABLE [dbo].[OrderType]
(
   [OrderTypeID] INT NOT NULL IDENTITY(1,1)
    CONSTRAINT [PK_OrderType] PRIMARY KEY ([OrderTypeID]),
   [Name] VARCHAR(40) NOT NULL,
   [Detail] VARCHAR(300) NULL
)


-- (5) Create Orders table 
CREATE TABLE [dbo].[Orders] (
    [OrderID]    INT             IDENTITY (1, 1) NOT NULL,
    [OrderTypeID] INT,
   [OrderDate]  DATETIME2 (7)   NULL,
    [CustomerId] INT             NULL,
    [ProductId]  INT             NULL,
    [Quantity]   INT             NULL,
    [TotalPrice] DECIMAL (10, 2) NULL,
    CONSTRAINT [PK_Orders_OrderID] PRIMARY KEY CLUSTERED ([OrderID] ASC),
    CONSTRAINT [FK_Orders_Customer] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([CustomerID]),
    CONSTRAINT [FK_Orders_Product] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Product] ([ProductID]), 
    CONSTRAINT [FK_Orders_OrderType] FOREIGN KEY ([OrderTypeID]) REFERENCES [dbo].[OrderType]([OrderTypeID]) ON DELETE CASCADE ON UPDATE CASCADE, 
);
GO

-- (6) Create Date table
CREATE TABLE [dbo].[Date] (
    [Date_Key]              DATETIME      NOT NULL,
    [Date_Name]             NVARCHAR (50) NULL,
    [Year]                  DATETIME      NULL,
    [Year_Name]             NVARCHAR (50) NULL,
    [Quarter]               DATETIME      NULL,
    [Quarter_Name]          NVARCHAR (50) NULL,
    [Month]                 DATETIME      NULL,
    [Month_Name]            NVARCHAR (50) NULL,
    [Day_Of_Year]           INT           NULL,
    [Day_Of_Year_Name]      NVARCHAR (50) NULL,
    [Day_Of_Quarter]        INT           NULL,
    [Day_Of_Quarter_Name]   NVARCHAR (50) NULL,
    [Day_Of_Month]          INT           NULL,
    [Day_Of_Month_Name]     NVARCHAR (50) NULL,
    [Month_Of_Year]         INT           NULL,
    [Month_Of_Year_Name]    NVARCHAR (50) NULL,
    [Month_Of_Quarter]      INT           NULL,
    [Month_Of_Quarter_Name] NVARCHAR (50) NULL,
    [Quarter_Of_Year]       INT           NULL,
    [Quarter_Of_Year_Name]  NVARCHAR (50) NULL,
    CONSTRAINT [PK_Date1] PRIMARY KEY CLUSTERED ([Date_Key] ASC)
);
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS V WHERE V.TABLE_NAME='OrdersView') DROP VIEW [dbo].[OrdersView];
GO

CREATE VIEW [dbo].[OrdersView]
   AS SELECT O.OrderID,T.Name AS OrderType,O.OrderDate,C.Name as Customer,P.Name as Product,O.Quantity,O.TotalPrice FROM Orders O
INNER JOIN OrderType T
ON T.OrderTypeID=O.OrderTypeID
INNER JOIN Customer C
ON C.CustomerID=O.CustomerID
INNER JOIN Product P
ON P.ProductID=O.ProductId;
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES R WHERE R.ROUTINE_NAME='AddCustomer') 
Drop PROC AddCustomer;
GO

CREATE PROC AddCustomer 
@Name VARCHAR(70),
@Email VARCHAR(320),
@Phone VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO [dbo].[Customer]
           ([Name]
           ,[Email]
           ,[Phone])
     VALUES
           (@Name
           ,@Email
           ,@Phone)
END
GO

Refresh Azure SQL Database and view the newly created database objects through the Query Editor while staying within the Azure Portal:

azure portal

Populate Azure SQL Database Objects

Run the attached script in the Query Editor (Preview) of Azure SQL Database to populate the database:

azure portal

Data Check

Click New Query and then run the code below against the created view as follows:

-- Data Check
SELECT 
[OrderID], [OrderType], [OrderDate], [Customer], [Product], [Quantity], [TotalPrice]
From [dbo].[OrdersView]

The output clearly indicates data is present in the database as shown below:

azure portal

Development Steps Summary

So far, we have done the following steps including the pre-requisites:

  1. Created a new 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. Did a quick data check by querying the Azure SQL Database

Congratulations, you have successfully created and populated an Azure SQL Database without leaving the Azure Portal and without using any database development tools such as SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS).

About Azure Analysis Services Web Designer

Let's have a very quick introduction of Azure Analysis Services web designer.

It is a browser-based service to add Tabular Data Model, create relationships and run queries against in-memory databases from within Azure Portal. It is the fastest approach to create BI Semantic Models on Azure.

We can very quickly add new business logic (measures) to the development or production Azure Analysis Services model using Azure Analysis Services Web Designer.

Please stay tuned for the next part of this tip we are going to do the following:

  1. Create Azure Analysis server resource
  2. Create data model based on Azure SQL Database
  3. Manage data model using Azure Analysis Services Web Designer
Next Steps


Last Updated: 2018-12-11


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, December 13, 2018 - 6:21:46 AM - Raja Imran Manzoor Khan Back To Top

 Very well written and to the point. Even a novice like me can easily understand and implement. Keep the good work going. 


Wednesday, December 12, 2018 - 2:05:15 PM - Adeela Ashraf Back To Top

A very informative detailed article  


Learn more about SQL Server tools