| 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 : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
