Step by step guide to create and configure Analysis Services in Azure (PaaS) – Part II

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.

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.

You need to select the 3rd option 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.

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

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.

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

The next step is where you need to provide the 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.

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

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

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

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

The next step is to create the gateway, click on the create button

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

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.

In next step you can see below

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.

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.

Select the SQL Server instance name

It will show as below

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)

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

In next step it will normalize the query.

And will show this screen where we stuck last time

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

In the next step you can see the 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.

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.

I hope you have liked this post, stay tuned for my next post on the gateway installation.

Thanks and you have a nice rest of your day/night.