By: Haroon Ashraf | Comments (2) | Related: 1 | 2 | > 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.
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:
- Developers can connect to their own workspace (SSAS) server (installed on their machine or somewhere on the network)
- 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.
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:
- SQL Server Data Tools (SSDT) to create and deploy a tabular model project
- 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.
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:
- You have an active Azure subscription to create Azure SQL Database(s) and Azure Analysis Services in-memory database (Tabular Data Model).
- 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:
Have a look at the newly created Azure SQL Database:
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:
Login as Admin and type the password you setup for admin when installing SQL Server resource group:
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:
Populate Azure SQL Database Objects
Run the attached script in the Query Editor (Preview) of Azure SQL Database to populate the database:
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:
Development Steps Summary
So far, we have done the following steps including the pre-requisites:
- Created a new Azure Subscription (Pre-requisite)
- Sign into Azure Portal (Pre-requisite)
- Created SQL Server Resource Group (Pre-requisite)
- Created Azure SQL Database (Resource) OfficeSuppliesSampleV4
- Setup Azure SQL Database Objects
- Populated Azure SQL Database
- 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:
- Create Azure Analysis server resource
- Create data model based on Azure SQL Database
- Manage data model using Azure Analysis Services Web Designer
Next Steps
- Please reset your Azure Portal try creating a new Azure SQL Database from scratch on Azure following the steps mentioned in this tip
- Please try creating the University sample database mentioned in my tip Adding a Primary Key to a Prepopulated Table using SQL Server Object Explorer in SSDT as an Azure SQL Database using Azure Portal
- Please refer to my previous tip Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT) and try to create Client, Service and ServiceOrder tables in an Azure SQL Database called MultiCustomerDatabase.
About the author
Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
