• Home
• Azure
• Analysis Services
• Reference

Azure Analysis Services Reference

This section provides detailed reference information for Azure Analysis Services.

Key Concepts and Components

Azure Analysis Services is a fully managed Platform as a Service (PaaS) that provides enterprise-grade semantic modeling capabilities. It helps you consolidate data from disparate sources and define business logic to deliver critical business insights.

Models

Analysis Services supports two primary modeling approaches:

• Tabular models: These models use an in-memory columnar database that leverages the VertiPaq engine. They are well-suited for interactive analysis and are often used with tools like Power BI, Excel, and other client reporting tools.
• Multidimensional models: These models are based on the traditional OLAP (Online Analytical Processing) architecture, using cubes, dimensions, and measures.

Data Sources

Analysis Services can connect to a wide variety of data sources, including:

• Azure SQL Database
• Azure Synapse Analytics
• Azure Data Lake Storage
• SQL Server
• On-premises data sources (via an on-premises data gateway)
• And many more...

Query Languages

Depending on the model type, you will use different query languages:

• DAX (Data Analysis Expressions): Used primarily with Tabular models. DAX is a formula expression language used to create custom calculations and queries.
• MDX (Multidimensional Expressions): Used primarily with Multidimensional models. MDX is a query language for OLAP cubes.
• Tabular Object Model (TOM): A .NET library for programmatically interacting with Tabular models.

Key APIs and Tools

Azure Portal

The Azure portal is the primary interface for managing your Analysis Services server, including deployment, configuration, scaling, and monitoring.

SQL Server Management Studio (SSMS)

SSMS provides a graphical interface for developing, managing, and administering Analysis Services databases. You can use it to:

• Connect to your Analysis Services server.
• Create and manage models.
• Write and execute DAX and MDX queries.
• Perform administrative tasks.

Visual Studio with Analysis Services projects

For more complex model development, Visual Studio with the SQL Server Data Tools (SSDT) provides a rich development environment for creating, deploying, and managing Tabular and Multidimensional models.

Azure PowerShell

Automate Azure Analysis Services management tasks using Azure PowerShell cmdlets.

Azure CLI

Use the Azure Command-Line Interface for scripting and automating Analysis Services operations.

REST API

Programmatically manage Azure Analysis Services resources using the Azure Resource Manager REST API.

Common Operations and Scenarios

Creating a New Model

You can create new Tabular or Multidimensional models using Visual Studio or directly within SSMS.

Connecting Power BI

Analysis Services models serve as a powerful semantic layer for Power BI. You can connect to an Azure Analysis Services model from Power BI Desktop to create interactive reports and dashboards.

Scaling and Performance

Understand how to scale your Azure Analysis Services instance to meet performance demands and monitor key performance metrics.

Security

Learn about role-based security, row-level security, and other security features to protect your data.

Syntax Examples

DAX Example (Calculated Column)

TotalSales = SUMX(Sales, Sales[Quantity] * Sales[Price])
            

MDX Example (Basic Query)

SELECT
  {[Measures].[Sales Amount]} ON COLUMNS,
  {[Dim Product].[Product Name].Members} ON ROWS
FROM [Adventure Works]
            

Reference Tables

Supported Data Types

Analysis Services Type	SQL Server Type	Description
String	VARCHAR, NVARCHAR	Text data.
Integer	INT, BIGINT	Whole numbers.
Decimal	DECIMAL, NUMERIC	Numbers with fractional parts.
Boolean	BIT	True or False values.
DateTime	DATETIME, DATETIME2	Date and time values.