Azure Analysis Services Documentation

Introduction to the Azure Analysis Services Tutorial

This tutorial guides you through the process of creating and deploying a tabular model in Azure Analysis Services. You'll learn how to create a server instance, connect to it, design a model, add data, and connect to client reporting tools.

Azure Analysis Services provides enterprise-grade data modeling capabilities. It enables developers to create semantic models that power business intelligence applications, providing a single source of truth for your organization's data.

Prerequisites

Before you begin this tutorial, ensure you have the following:

• An Azure subscription. If you don't have one, you can create a free account.
• Permissions to create resources in your Azure subscription.
• SQL Server Management Studio (SSMS) installed. You can download the latest version from the official Microsoft SQL Server download page.
• SQL Server Data Tools (SSDT) for Visual Studio installed, with support for Analysis Services. This is typically installed as part of a Visual Studio workload.
• A sample data source (e.g., a SQL Server database, Azure SQL Database). For this tutorial, we'll use the AdventureWorksDW sample database.

Step 1: Create an Azure Analysis Services Server

The first step is to provision an Azure Analysis Services server instance in your Azure subscription. This server will host your tabular models.

1. Navigate to the Azure portal.
2. Click Create a resource.
3. In the search box, type "Azure Analysis Services" and select it from the results.
4. Click Create.
5. Fill in the required details:
   • Subscription: Select your Azure subscription.
   • Resource group: Create a new one or select an existing one.
   • Server name: Enter a unique name for your server.
   • Location: Choose a region close to you or your users.
   • Pricing tier: Select a suitable tier (e.g., Developer for testing, or a higher tier for production).
6. Review the settings and click Create.

Provisioning the server may take a few minutes. Once complete, you'll find your server resource in the Azure portal.

Step 2: Connect to the Server using SQL Server Management Studio (SSMS)

Once your server is provisioned, you can connect to it using SSMS to manage it and deploy models.

1. Open SSMS.
2. In the Connect to Server dialog, enter your server name in the Server name field. The server name is typically in the format asazure://your-region.asazure.windows.net/your-server-name. You can find the full server name in the Azure portal overview page for your Analysis Services instance.
3. Ensure the Authentication is set to Azure Active Directory - Universal with MFA or Azure Active Directory - Password, and provide your Azure AD credentials.
4. Click Connect.

If the connection is successful, you will see your Analysis Services server listed in the Object Explorer.

Step 3: Create a Tabular Model Project

We will use Visual Studio with SQL Server Data Tools (SSDT) to create a tabular model project.

1. Open Visual Studio.
2. Select Create a new project.
3. Search for "Analysis Services Tabular Project" and select it.
4. Click Next.
5. Enter a project name (e.g., "AdventureWorksDWTabular") and a location.
6. Click Create.
7. In the project dialog, select In-memory as the Database engine and Blank as the Model.
8. Choose the compatibility level (usually the latest available is recommended).
9. Click OK.

This will create an empty tabular model project. The model designer will open, showing an empty workspace.

Step 4: Add Data to the Model

Now, you'll connect to your data source and import tables into your tabular model.

1. In Solution Explorer, right-click on the Models folder and select Add Table.
2. Choose From Power Query (or From Database depending on your SSAS version and data source).
3. Select your data source type (e.g., SQL Server).
4. Enter the server and database name for your AdventureWorksDW sample database.
5. Click Connect.
6. In the Navigator window, select the tables you want to import (e.g., DimCustomer, DimProduct, DimDate, FactInternetSales).
7. Click Load.

SSDT will import the selected tables into your model. You can view them in the model designer.

You can now define relationships between these tables to create a star schema or snowflake schema, which is crucial for performance and usability.

Step 5: Deploy the Model to Azure Analysis Services

After designing your model, you need to deploy it to your Azure Analysis Services server.

1. In Visual Studio, right-click on the tabular model project in Solution Explorer and select Properties.
2. Under Configuration Properties, select the appropriate configuration (e.g., Debug or Release).
3. In the Project Properties window, under Deployment, set the Server Name to the full server name of your Azure Analysis Services instance.
4. Ensure the Database Name is set to a name for your model database.
5. Click OK.
6. Right-click on the project again and select Deploy.

Visual Studio will build and deploy your model to the Azure Analysis Services server. Monitor the deployment progress in the Output window.

Step 6: Process the Model

After deployment, you need to process the model to load the actual data into the tabular model's memory.

1. In SSMS, connect to your Azure Analysis Services server.
2. Right-click on the deployed database and select Process Database.
3. In the Process Database dialog, ensure Process Option is set to Process Full.
4. Click OK.

Processing can take some time depending on the size of your data. You can monitor the status in the SSMS progress window.

Step 7: Connect a Client Tool

Now that your model is deployed and processed, you can connect to it from various business intelligence client tools, such as Power BI, Tableau, or Excel.

Connecting with Power BI Desktop:

1. Open Power BI Desktop.
2. Click Get data.
3. Search for and select Azure Analysis Services.
4. Enter your server name (the same one used in SSMS and Visual Studio).
5. Choose the connection mode (e.g., Live Connection).
6. Click OK.
7. Sign in with your Azure AD credentials.
8. Select the tabular model database you deployed.
9. Click Connect.

You can now build reports and dashboards using the data from your Azure Analysis Services model.

Next Steps

Congratulations! You have successfully created, deployed, and connected to an Azure Analysis Services tabular model.

From here, you can explore more advanced features:

• Designing complex DAX calculations
• Implementing row-level security
• Configuring partitions for performance optimization
• Automating deployments using Azure DevOps
• Integrating with Azure Data Factory for data refreshes