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

This post I have targeted to the BI developers and system admins who are interested to configure and work with the SQL Server Analysis Services in Azure (Called Analysis Services in Azure)

What is SQL Server Analysis Services?

SQL Analysis Service is the PaaS instance of SQL Server Analysis Services. It’s an analytical data engine which supports business analytics and helps in business decision making. It provides enterprise-grade semantic data models for business reports. You can view the reports by the following client applications.

1. MS Excel.
2. MS Power BI
3. Tableu and other data visualization tools.

How the data models are developed?

The data model development is generally carried out in SSDT (SQL Server Data Tools for Visual Studio) which is available as part of the Visual Studio Add on installations. Developers generally build tabular or multidimensional data model project in Visual Studio, deploying the model as a database to a server instance, setting up recurring data processing, and assigning permissions to allow data access by end-users. When it’s ready to go, your semantic data model can be accessed by client applications supporting Analysis Services as a data source.

The following tools are also used.

1. SSMS (SQL Server Management Studio)
2. PowerShell

What is Azure Analysis Service?

Azure Analysis Services provides enterprise-grade data modeling in the cloud. It is a fully managed platform as a service (PaaS), integrated with Azure data platform services.

What is the advantage of choosing Azure Analysis Service instead of SQL Server Analysis Services?

Azure Analysis Services has many advantages with Azure. As per Microsoft the Azure Analysis Services integrates with many Azure services enabling you to build sophisticated analytics solutions. Integration with Azure Active Directory provides secure, role-based access to your critical data. Integrate with Azure Data Factory pipelines by including an activity that loads data into the model. Azure Automation and Azure Functions can be used for lightweight orchestration of models using custom code. It’s also complete PaaS solution offered by MS so it’s super easy to deploy and can be scale out and scale in.

Will my data is secure with Azure Analysis services?

As per Microsoft the Azure Analysis Services utilizes Azure Blob storage to persist storage and metadata for Analysis Services databases. Data files within Blob are encrypted using Azure Blob Server Side Encryption (SSE). When using Direct Query mode, only metadata is stored. The actual data is accessed from the data source at query time.

Let’s dirty our hand and see how we have configured the Azure Analysis Services.

If you go to all services and type anal it will show the analysis services as you can see below.

Once you click on the Analysis Services you can see the following thing

You can click on the + Add button above to configure the Analysis Services.

Next step is to click on Create

Please remember if you don’t want to use an existing storage account you can create a new storage account and should add a container in it for the backup.

As you have seen the blob storage container which I have created I have kept the name as backup.

Once the Analysis services is ready you can view the following screen

The next step is to view what is created.

The above screen will show the analysis service which has been created. The server name is the one which is required to connect this Analysis Service from VS SSDT or Power BI.

For connecting from SSDT you need to download and install SSDT from internet. Here is the download URL for SSDT. In my next post how we can connect this URL from SSDT and import the data. In the next post we also need to bypass an error related to connecting a SQL Server data source located in an IaaS VM in Azure, which will lead to the installation of unified gateway. The step by step installation of the gateway also I will cover in my next post. Stay tuned till then.