Switching SSAS Instances between Multidimensional and Tabular
The other day we were at a client having trouble connecting to a tabular instance that was not the default SSAS instance on an Azure deployment. Thinking we had to uninstall the main MD instance and install Tabular, we found a real easy way to switch between MD and Tabular.
When installing MD as the default instance in SQL Server 2014, the installer will install SSAS in the following default location:
<DRIVE>\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP.
In this directory you will have the following directory tree:
Moving into the Config directory you will see the following files:
Open the msmdsrv.ini file in your favorite text editor (mine is Sublime Text) and you will see the following snippet:
The DeploymentMode element can have one of three values
- 0 = Multidimensional
- 1 = Power Pivot for SharePoint
- 2 = Tabular
We changed the value from 0 (Multidimensional) to 2 (Tabular) and saved the file. Next we went into Services in the Management Console and restarted the service for the SSAS default instance.
We launched SSMS and connected to the default instance and we saw the following:
That's right, a Tabular instance of SSAS.
Make sure to backup your existing databases before switching deployment modes.