SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Change an Analysis Services Instance to Tabular Mode

By Ganapathi varma Chekuri,

One of my client accidently set up an SQL Server Analysis Services in multi-dimensional mode instead of Tabular mode. In this post I will show a procedure to change Analysis tabular mode in SQL Server 2016. 

Analysis Services Modes 

Analysis Services can be installed in one of three server modes: Multidimensional and Data Mining (default), Power Pivot for SharePoint, and Tabular. The server mode of an Analysis Services instance is determined during setup when you choose options for installing the server.

You can check the DeploymentMode property in the msmdsrv.ini file that is included in every Analysis Services instance. The value of this property identifies the server mode. Valid values are 0 (Multidimensional), 1 (SharePoint), or 2 (Tabular).

Although changing DeploymentMode property it is not supported by Microsoft, it works.

How to Verify the Mode 

Connect to the instance in SQL Server Management Studio and right click on the instance name. Then select Properties.

In the below screenshot, you can see the server mode is in Multidimensional mode.

Before changing the Analysis Services instance to Tabular mode, you have to perform backup of any Multidimensional databases on your server and either detach them or delete them. You will not be able to load them on the tabular instance.

How to Dhange to Tabular Mode

Traverse through the path C:\Program Files\Microsoft SQL Server\MSAS13.TABULAR\OLAP\Config and Copy the msmdsrv.ini file to your desktop as shown in below screenshot.

Open the config file in Notepad. Change the Deployment mode property from 0 (multidimensional) to 2 (tabular). In the below screenshot, the Deployment mode is set to 0 ( Multidimensional mode)

In this screenshot below, the Deployment mode is set to 2 (Tabular mode)

          

Copy and replace the msmdsrv.ini file back to the OLAP\Config directory as shown in below screenshot.

Go to services.msc and restart the Analysis Services instance.

Connect to the server in SQL Server Management Studio and right click on Analysis Services, then select Properties. You can now see the Analysis Services server mode is changed to Tabular mode. In below screenshot, we can see Server mode is changed.

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin

 
Total article views: 621 | Views in the last 30 days: 17
 
Related Articles
BLOG

(SSAS) SQL Server Analysis Services Tabular Basics

What is (SSAS) SQL Server Analysis Services Tabular Mode? SQL Server 2012 ships Analysis Services...

BLOG

How to Change an Analysis Services Instance to Tabular Mode

One of my client accidentally set up an SQL Server Analysis Services in multi-dimensional mode i...

BLOG

SQL Server Analysis Services Tabular KPIs

A few weeks ago, I wrote a blog post that discusses implementing KPIs in SQL Server Analysis Service...

BLOG

Choosing the Right Analysis Services: MOLAP vs. Tabular Recording and Q&A

I hope you were able to attend my free webinar on Choosing the Right Analysis Services: MOLAP vs. T...

FORUM

SQL Server 2005 Analysis Services

SQL Server 2005 Analysis Services

 
Contribute