Analysis Services


Analysis Services is an online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.

A typical workflow for Analysis Services includes authoring a Multidimensional or Tabular data model, deploying the model as a database to an Analysis Services instance, processing the database to load it with data or metadata, setting up data refresh, and assigning permissions to allow data access by end-users. When it's ready to go, this multi-purpose semantic data model can be accessed by any client application supporting Analysis Services as a data source.

Models are populated with data from external data systems, usually data warehouses hosted on a SQL Server or Oracle relational database engine (Tabular models support additional data source types). Models specify query objects, such as cubes, but also specify dimensions that can be used in multiple cubes, calculations and KPIs that encapsulate business logic, and interactions such as navigation and drill-through behaviors.

Analysis Services is now available in the cloud as an Azure service. Currently in preview, Azure Analysis Services supports tabular models at the 1200 compatibility level. DirectQuery, partitions, row-level security, bi-directional relationships, and translations are all supported. To learn more and give it a try for free, see Azure Analysis Services.

When installing Analysis Services by using SQL Server 2016 Setup, during configuration you specify a server mode for that instance. Each mode includes different features unique to a particular Analysis Services solution.

  • Multidimensional and Data Mining Mode - Implement OLAP modeling constructs (cubes, dimensions, measures).

  • Tabular Mode - Implement in-memory relational data modeling constructs (model, tables, columns).

    Tabular models can be created at the default compatibility level 1200; using the latest functionality, or at the older 1103 compatibility level. There are significant differences between compatibility levels. See Compatibility Level for Tabular models in Analysis Services for information on how the levels compare.

  • Power Pivot Mode - Implement Power Pivot and Excel data models in SharePoint (Power Pivot for SharePoint is a middle-tier data engine that loads, queries, and refreshes data models hosted in SharePoint).

A single instance can be configured with only one mode, and cannot be changed later. You can install multiple instances with different modes on the same server, but you'll need to run Setup and specify configuration settings for each instance.

Analysis Services features vary by edition. For more information, see Editions and Supported Features for SQL Server 2016

To create a model, you use SQL Server Data Tools (see Tools and applications used in Analysis Services), choosing either a Tabular or Multidimensional and Data Mining project template. The project template contains folders for all of the objects needed in a model. Wizards help create many of the basic elements, such as data sources, data source views, dimensions, cubes, and roles.

Documentation for Analysis Services is organized into sections that correspond to the type of project you are building. Choose from the following links to learn more about each mode or feature area.

Small File Folder Icon What's New

Small File Folder Icon Comparing Tabular and Multidimensional Solutions

Small File Folder Icon Tabular Models

Small File Folder Icon Multidimensional Models

Small File Folder Icon Data Mining

Small File Folder Icon Power Pivot for SharePoint

Small File Folder Icon Analysis Services Instance Management

Small File Folder Icon Analysis Services Tutorials

Small File Folder Icon Analysis Services Developer Documentation

Small File Folder Icon Technical Reference (SSAS)

Community Additions