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
