How to modify SQL Server Analysis Services Modes

SQL Server Analysis Services can be installed in one of three modes.

  • Tabular

  • Multidimensional

  • SharePoint

How to check the current analysis server mode?

We can check the current mode by checking the properties of the analysis server.

Connect to the Analysis server and right-click to go to the properties option.

Current SQL Server analysis server mode

Check the Server mode option. Currently, it's showing in tabular mode.

Tabular mode

Alternatively, the DeploymentMode property in the msmdsrv.ini file also has details.

The default path of the msmdsrv.ini file is %\Program Files\Microsoft SQL Server\MSAS15.MSSQLSERVER\OLAP\Config

% can be a location where SQL Server and Analysis services are installed.

msmdsrv.ini lcoation

Right-click and edit msmdsrv xml configuration setting file .

Edit msmdsrv xml configuration

The value of this property identifies the server mode.

Valid values are,

  • Multidimensional =0

  • SharePoint =1

  • Tabular =2

How to change the current tabular mode to multidimensional or SharePoint?

Edit msmdsrv XML (shown above) and change value according to the requirement.

I require to change it to Multidimensional, and it needs to update the DeploymentMode option msmdsrv.xml file to 0.

change the current tabular mode to multidimensional or SharePoint

Save the updated file and restart the Analysis Services.

Restart SQL Server Analysis Service

Now again, check the Analysis Server properties to confirm the updated mode.

The multidimensional mode is now ready to use.

check the Analysis Server properties

In the same way, we can change to Sharepoint mode by updating the value to 1.

SharePoint mode

As shown below, the analysis server is in SharePoint mode.

10 views0 comments