SQL Server Analysis Services: The Complete DBA Guide

SQL Server Analysis Services (SSAS) is the analytical engine built into SQL Server. It sits between the relational database and BI tools like Power BI, Excel, and SSRS, delivering fast query responses over large volumes of data by storing pre-aggregated or compressed analytical models. SSAS runs as a separate Windows service from the Database Engine and is installed and managed independently.

Understanding SSAS means understanding the two distinct modes it supports, the query languages each mode uses, the compatibility level system for tabular models, how SSAS integrates with Power BI and Excel, and where SSAS fits in Microsoft’s current analytics roadmap versus Azure Analysis Services and Microsoft Fabric.

SQL Server Analysis Services installs in one of two server modes: Tabular or Multidimensional. The mode is selected during installation and cannot be changed afterward without uninstalling and reinstalling. Each SSAS instance runs in exactly one mode. To run both modes, install two separate SSAS instances on the same server — though this is resource-intensive and only recommended on high-end hardware.

A tabular model organizes data into tables, columns, relationships, and measures — a structure familiar to anyone who has worked with relational databases or Power BI Desktop. Tabular models are developed in Visual Studio using the Analysis Services Projects extension, then deployed to an SSAS Tabular instance where they run as a database.

The core objects in a tabular model:

Tabular models have a compatibility level that determines which features are available. The compatibility level is set when the model project is created in Visual Studio and can be upgraded — but not downgraded — after deployment. Higher compatibility levels unlock modern features but reduce backward compatibility with older SSAS server versions.

In DirectQuery mode, the tabular model stores no data locally. When a client queries the model, SSAS translates the DAX query into SQL and sends it to the source relational database. The source executes the SQL and returns results to SSAS, which assembles the final response.

DirectQuery is appropriate when:

• The dataset is too large to fit in server RAM in VertiPaq mode
• Real-time or near-real-time data freshness is required (no processing latency)
• The source database has its own caching and performance optimization

DirectQuery has trade-offs: query performance depends entirely on the source database, some DAX functions and model features are not supported in DirectQuery mode, and every user query results in a relational query against the source. For mixed scenarios, SSAS supports hybrid partitions where some table partitions use VertiPaq and others use DirectQuery.

Multidimensional models have been the foundation of enterprise OLAP analytics since SQL Server 2000. They organize data using a cube metaphor: a central fact table (measures) is surrounded by dimension tables that provide context. The model is processed — queries are run against the source, aggregations are pre-computed — and the result is stored in SSAS’s MOLAP format for fast query response.

Multidimensional models are the correct choice when:

• Complex MDX calculations are required: named sets, custom rollups, many-to-many dimension relationships using intermediate measure groups
• Write-back capability is needed (users write values back to specific cells for what-if analysis or budgeting)
• The organization has an existing multidimensional estate that would be cost-prohibitive to redesign as tabular
• Large volumes of pre-aggregated data need to be served consistently to many concurrent users

SSAS is installed through the SQL Server installation wizard as a separate feature from the Database Engine. On the Feature Selection page, select Analysis Services. On the Analysis Services Configuration page, choose the server mode and add the Windows accounts or groups that will be SSAS server administrators.

The mode selected at installation sets the DeploymentMode property in the msmdsrv.ini configuration file: 0 = Multidimensional, 2 = Tabular. This property must not be changed manually after installation — doing so is not supported and corrupts the server state. If the wrong mode was selected, uninstall and reinstall.

-- Verify SSAS server mode and version via DMV query
-- Connect to the SSAS instance in SSMS, open a new MDX or DAX query
-- For tabular instances, use DAX Studio or the SSMS DAX query window

-- Query server properties via XMLA (works for both modes in SSMS)
-- In SSMS: connect to Analysis Services > New Query > MDX or XMLA

SELECT * FROM $SYSTEM.DISCOVER_PROPERTIES
WHERE PROPERTYNAME = 'ServerMode'

-- Returns: 'Multidimensional' or 'Tabular'

-- Check SSAS version
SELECT * FROM $SYSTEM.DISCOVER_PROPERTIES
WHERE PROPERTYNAME = 'ProductVersion'

SSAS uses its own role-based security model at two levels: server level and database level. Server administrators are configured during installation and have full control. Database roles control access to model data.

-- Example tabular row-level security DAX filter
-- Applied to the Customer table within a Read role
-- Users in this role only see customers in their assigned region

-- In the model role definition, set the DAX filter on the Customer table:
[Region] = USERNAME()

-- Or map to a security table:
[CustomerID] IN SELECTCOLUMNS(
    FILTER(SecurityMapping, SecurityMapping[UserPrincipalName] = USERNAME()),
    "CID", SecurityMapping[CustomerID]
)

Processing is the operation that loads data from the source into the SSAS model. For tabular models this means refreshing the VertiPaq in-memory store. For multidimensional models this means re-reading fact and dimension data and recomputing aggregations.

Processing is typically scheduled through SQL Server Agent jobs that call SSAS processing via XMLA, TMSL, or PowerShell. The Analysis Services PowerShell module provides Invoke-ProcessASDatabase and related cmdlets for scripting processing operations.

Microsoft’s analytical platform strategy has shifted toward cloud-native services. Understanding where SSAS fits versus Azure Analysis Services and Microsoft Fabric is important for both new project decisions and long-term planning.

SQL Server Analysis Services 2025 introduced the following changes, all verified against Microsoft Learn:

Total Sales := SUM(FactInternetSales[SalesAmount])

Sales YTD :=
TOTALYTD(
    [Total Sales],
    DimDate[FullDateAlternateKey]
)

EVALUATE
SUMMARIZECOLUMNS(
    DimProduct[EnglishProductCategoryName],
    "Total Sales", [Total Sales],
    "Sales YTD",   [Sales YTD]
)
ORDER BY [Total Sales] DESC

The technical information in this article was verified against Microsoft documentation at the time of publication. SQL Server features, cloud service capabilities, licensing terms, and configuration requirements can change between versions and cumulative updates. Always validate implementation details against current Microsoft Learn documentation before deploying to production. References in this article link directly to the authoritative Microsoft sources.