Monday, September 14, 2015

SQL Server Brain Basher of the Week #029 - SSAS

Analysis Services can be installed in one of three modes; Multi-dimensional, Power Pivot for SharePoint, and Tabular. One instance can be run with only one mode and it has to be set during the installation.

Once installed with a mode selected, can we change or switch the mode?

There is no defined method for doing it with a specific menu but there is a way of doing it which is not a solution provided by Microsoft. Microsoft does not recommend to change mode after the installation but it can be done by changing the msmdsrv.ini file. It requires no added objects (databases), in some situations, removing added objects (databases) and changing the mode has worked fine but specially with the mode set for Power Pivot may produce an unexpected result.

The msmdsrv.ini is located in C:\Program Files\Microsoft SQL Server\MSAS12.SQL2014\OLAP\Config path and the property called DeploymentMode can be changed as 0 (Multi-dimensional), 1 (Power Pivot for SharePoint), or 2 (Tabular).

Note that, though this works in many cases, it is NOT recommended.

And if you want to know the mode you have installed, DeploymentMode can be used for determining it but easiest way is by looking at the icon of the instance. This shows multi-dimensional, Power Pivot for SharePoint and Tabular in order.

No comments: