Analysis Services: Can we change the mode of an instance after installation?

SQL Server Analysis Services allows us to install the instance in three different modes;

• Multidimensional
• Tabular
• PowerPivot for SharePoint

Once the instance is installed with the selected mode, it cannot be changed to another and no interface is given for changing it too. However, there is way of changing the mode if you have not yet created a database on that instance.

Microsoft does not recommend to change mode after the installation but it can be done by changing the msmdsrv.ini file. If you are to change this file for changing the mode, you need to make sure that it has no added objects (databases). You may remove all added objects and change the type but it does not guraentee that it will work as expected hence not recommended.

The msmdsrv.ini is located in C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Config path.

You may experience the following issue:

If so, open your text editor (Notepad) as an administrator and open the file.

Once opened, locate the property named DeploymentMode. Value of it can have following:

• 0 for Multi-dimensional
• 1 for Power Pivot for SharePoint
• 2 for Tabular

You can change as you want and save it.

Once it is changed, you can easily see whether it has accepted your change by looking at the icon set to the instance: