In my last post I have shown you how to create and configure the Analysis services in Azure. Today I will show you how to connect to the Analysis Services from Visual Studio SSDT (SQL Server Data Tools).

In my lab environment the initial Architecture was as follows.

Azure Analysis Services Architecture

Fig: Azure Analysis Service Initial Architecture.

The steps to connect the Azure Analysis Services is shown below

Open the SSDT (SQL Server Data Tools) from your program files. And create a new project.

Create a new project

You need to select the 3rd option Analysis Services Tabular Project.

Analysis Services Tabular Project

In the next step you need provide the URL of the Analysis Services which we have created in my last post.

Workspace Server

Once you click on the test connection it should show that the test connection is succeeded.

Microsoft Visual Studio

The visual studio will create the Tabular Project.

In the next step we need to connect a SQL server data source from where we will fetch the data from a test table for the Analysis Services. In our case we have a data source in SQL Server which resides in an IaaS VM in Azure.

Connect to Data Source

In the next step you need to provide the connection name and the SQL Server instance name to connect.

Connection name and the SQL Server

The next step is where you need to provide the impersonation information.

Impersonation information

The next step will be as below where it will show the list of tables which you can choose to import the data.

Choose to import the data

The next step in the table import wizard it will show the table name

Select tables and views

And when you have reached the last step thinking it will be successful.

Importing

You will get this error about which I have mentioned in my last post.

Error

The above error is confusing since it is indicating ‘On-Premise Gateway is required to access the data source’. Since our SQL database is located in an Azure VM so we got confused why it’s complaining. We have searched google and didn’t find an answer to this question. Later we thought to deploy the Gateway based on the below statement which was in our mind.

“SQL Analysis services thinking any IaaS based SQL data source as the on premises data source”

The next step is to install the on-premises Data Gateway. To know more about enterprise data gateway I am going to write a separate post of how to create an on premises gateway for the SQL Server Analysis Services in my next post in this blog.

Assuming the gateway is created and installed in an IaaS or On premises VM, you have to create the same on premises data gateway in Azure as well.

Please follow the below steps to add the gateway in the Azure Portal

Go to Home-> New -> Marketplace -> Enterprise Integration

Azure portal

Enterprise Integration

Create the gateway

Here is the screen you will find once you click on the create button

Connection gateway

You need to provide the gateway name in the resource name field and you should choose the same resource group where the SQL Server VM is located.

SQL Server VM

In next step you can see below

On premises Data gateway

Once the Gateway is created in the Azure Portal you need to go to the Analysis Services and need to connect this gateway as shown below. In the Analysis Services please choose the On-Premises Data Gateway and from the drop down list you can choose the gateway name.

Connect selected gateway

Once it’s configured we went to SSDT and have tried to import the table again. This time we have used VS 2017 data source from the drop down list so the UI will be little different but it will work with VS 2015 also.

Assuming you have already created the Analysis Service Tabular project by following the steps I shown in the beginning of this article and you are in a stage where you need to import the data from a table here is what you need to do.

Select the SQL Server database.

Get data

Select the SQL Server instance name

SQL Server

It will show as below

SQL Server Database

The next step is to select the table and it will show the table data. (For security reasons I can’t show the table data)

Navigator

In the next step you need to click on the Load Button

Load button

In next step it will normalize the query.

Operation in progress

And will show this screen where we stuck last time

Data processing

The next is no error, you will get the success message.

Progressing Progress

In the next step you can see the data model in Visual Studio

Data model in visual studio

So looks like the below statement is true

“SQL Analysis services thinking any IaaS based SQL data source as the on premises data source”

So there is a change in the Architecture when you need to connect SQL Server IaaS data source or on premises data source. The Architecture will look like as below.

Azure Analysis Services Architecture

Fig: Analysis Services with Gateway to connect SQL Database in an IaaS VM

Conclusion: Azure Analysis Services is a very nice PaaS offering and very fast and easy to configure. For connecting on premises data source as well SQL Server data stored in any IaaS VM in Azure you need the on-premises data gateway. For connecting the PaaS instance of SQL Server, Gateway is not a requirement.

About the Author:

Aavisek Choudhury is working for Unisys as a Sr. Solution Architect Manager for the Hybrid Cloud Infrastructure Platform and residing at Bangalore, India. He is working with multiple MS Technologies from 2000 which includes Azure, Windows Server, Exchange, Active Directory, Office Communication Server, TMG, MS Lync, Skype for Business Server, SQL Server, SCOM, SCVMM,SCDPM, MSBI and SharePoint. He is holding multiple MS certifications like MCSE, MCTIP, MCSA, MCTS etc., He has also won the Community Contributor Award from Microsoft in 2011. In 2017 he has been recognised as Azure Champs by Microsoft Association of Practising Architects (MAPA) and Awarded the Azure Master of the Month Feb. 2018 by Microsoft.

Reference: Choudhury, A. (2018). Step by step guide to create and configure Analysis Services in Azure (PaaS) – Part II. Available at: http://whyazure.in/step-by-step-guide-to-create-and-configure-analysis-services-in-azure-paas-part-ii/  [Accessed 23 April 2018].