Sunday, 6 September 2015

SSAS- Change the SSAS Service Tabular to Multidimensional


Table of Content

1.      Problems. 2

2.      DeploymentMode Value as per Model Service. 2

3.      Steps to change the DeploymentMode property of SQL Server


Problem –
If you want to change SQL Service from tabular mode to Multidimensional Mode or Multidimensional Mode to Tabular Mode

 
DeploymentMode Value as per Model Service -
  • DeploymentMode=2 for Tabular Mode
  • DeploymentMode=0 for Multidimensional Mode
  • DeploymentMode=1 is for PowerPivot for SharePoint instances.


Steps to change the DeploymentMode property of SQL Server
  • Backup 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.
  •  Copy the msmdsrv.ini file to your desktop. For my instance (which I called TABULAR, I installed it like that from setup), I found the config file in C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Config.
  •  Open the config file in Notepad. Change the DeploymentMode property from 0 (multidimensional) to 2 (tabular), as pictured. Save and close the file.
  •  Copy the msmdsrv.ini file back to the OLAP\Config directory.
  •  From services.msc, restart the SQL Server Analysis Services instance.



Note - You can verify that the server mode has changed from SSMS. 

When you connect to the server instance, you can see that the icon for the server instance changed from the yellow cube icon to the blue tabular icon.