TechBubbles Microsoft Technology BLOG

Analysis Services Server Modes in SQL Server 2012


SQL Server 2005 and 2008 supports only one mode that is multidimensional mode, Where as SQL Server 2008 R2 supports additional mode PowerPivot for SharePoint. In SQL Server 2012 you have an option to deploy an analysis services instance in tabular mode. This post discusses the Analysis server modes and enhancements in SQL Server 2012.

Server Modes

Each server mode in SQL Server 2012 has different type of database and storage structure, an analysis services instance can run in one of the following server modes

  • Multidimensional
  • Tabular
  • PowerPivot for SharePoint

The below table compares the three server modes

Feature Multidimensional Tabular PowerPivot for SharePoint
Data Sources Relational Database Relational Database
Analysis Services
Reporting Services
Azure Market Dataset
Excel file
Relational Database
Analysis Services
Reporting Services
Azure Market Dataset
Excel file
Development Tool SQL Server Data Tools SQL Server Data Tools PowerPivot for Excel
Query Language MDX for calculations
DMX for data-mining queries
DAX for calculations and queries DAX for calculations and queries
Security Cell-level security and Role based permissions in SSAS Row-level security and Role based permissions in SSAS File-level security using SharePoint permissions

Before you deploy SQL Server Analysis Services instance, you must decide what type of functionality you want and install the appropriate server mode.

Server Mode is assigned during the installation of analysis services, In setup page select SQL Server Feature installation option for multi-dimensional or tabular mode.

Multiple instances of Analysis Services can co-exist on same server, each running on different server mode.

Analysis Services Projects 

SQL Server Data Tools (SSDT) can be used to develop multi-dimensional models. The new project dialogue box in SSDT looks as below


Five templates are available in dialogue box for Analysis Services projects

  • Analysis Services Multidimensional and Data Mining Project –  use this template for developing traditional type of analysis services project which now called multi-dimensional model.
  • Import from Server (Multidimensional and Data Mining) – use this template when a multi-dimensional model exist on server and you want to create a project using that model.
  • Analysis Services Tabular Project use this template to create new tabular model project.
  • Import from PowerPivot  use this template to import a work book that deployed to a PowerPivot for SharePoint instance of analysis services.
  • Import from Server (Tabular) use this template when Tabular model is exist on server

Share this post :

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

Add Comment

TechBubbles Microsoft Technology BLOG

Follow me


Tag Cloud