Create Models in Azure Analysis Services

This document guides you through the process of creating tabular models in Azure Analysis Services. Tabular models provide a powerful way to organize and analyze data, enabling rich business intelligence solutions.

Tip: Azure Analysis Services supports creating tabular models directly using tools like Visual Studio with the Analysis Services projects extension or by deploying existing tabular model projects from SQL Server Analysis Services.

Prerequisites

Methods for Creating Models

1. Using Visual Studio with Analysis Services Projects Extension

This is the most common and recommended method for developing tabular models from scratch.

Step 1: Install the Extension

If you don't have it already, install the "Analysis Services projects" extension for Visual Studio from the Visual Studio Marketplace.

Note: Ensure you are using a compatible version of Visual Studio (e.g., Visual Studio 2019 or 2022).

Step 2: Create a New Project

  1. Open Visual Studio.
  2. Go to File > New > Project.
  3. In the "Create a new project" dialog, search for "Analysis Services".
  4. Select "Analysis Services Tabular Project" and click Next.
  5. Provide a Project name, Location, and Solution name, then click Create.

Step 3: Configure Project Properties

Once the project is created, you need to configure its properties to connect to your Azure Analysis Services server.

  1. In Solution Explorer, right-click on your project and select Properties.
  2. Under the General tab, configure the Deployment Server Name to your Azure Analysis Services server name (e.g., yourservername.eastus.asazure.windows.net).
  3. Set the Database Name to the desired name for your model.
  4. Choose the Data Modeling option (e.g., "Compatible with SQL Server 2022 Analysis Services").

Step 4: Add Data Sources

Connect to your underlying data sources to import data into your model.

  1. In Solution Explorer, right-click on the Data Sources folder and select Add Data Source.
  2. Choose your data source type (e.g., "SQL Server", "Azure SQL Database").
  3. Configure the connection string and credentials.

You can also use the Model menu to add tables and relationships directly.

Step 5: Create Tables and Relationships

Import tables from your data sources and define relationships between them to create a semantic model.

Visual Studio provides a drag-and-drop interface to create tables and a visual diagram to define relationships.

// Example: Adding a table through code (simplified representation) // In a real scenario, this is done visually or via scripting Add-Table -Name "DimProduct" -DataSource "MyDataSource"

Step 6: Define Measures and Calculations

Create measures using DAX (Data Analysis Expressions) to perform calculations and aggregations.

For example, to calculate total sales:

Total Sales = SUM(Sales[Amount])

Step 7: Deploy the Model

Deploy your model to your Azure Analysis Services server.

  1. Right-click on the project in Solution Explorer and select Deploy.
  2. Confirm the deployment settings and click Deploy.

2. Deploying Existing Tabular Model Projects

If you have an existing tabular model project (e.g., from SQL Server Analysis Services), you can deploy it directly to Azure Analysis Services.

  1. Open the project in Visual Studio.
  2. Configure the Deployment Server Name and Database Name in the project properties to point to your Azure Analysis Services instance.
  3. Build and deploy the project as described above.

3. Using Tabular Editor

Tabular Editor is a popular third-party tool for working with tabular models. It offers a powerful, scriptable interface for creating, managing, and deploying models.

  1. Download and install Tabular Editor.
  2. Connect to your Azure Analysis Services server.
  3. You can either create a new model from scratch within Tabular Editor or open an existing model for editing.
  4. Use Tabular Editor's interface or its C# scripting capabilities to define tables, relationships, measures, and other model objects.
  5. Save or deploy your changes directly to the Azure Analysis Services server.
Note: Azure Analysis Services uses the tabular model object model (TOM). This means that tools and scripts designed for SQL Server Analysis Services tabular models are generally compatible with Azure Analysis Services.

Best Practices