修改 Analysis Service 服务器模式

原网址：http://cathydumas.com/2012/04/23/changing-an-analysis-services-instance-to-tabular-mode/

 

Say you have already installed an Analysis Services instance of SQL Server 2012. Maybe you made a mistake in setup and accidentally set it up as a multidimensional instance. What do you do?

This is unsupported but it works. You can change the DeploymentMode property in the msmdsrv.ini file to change the mode of the server instance.

Note that you can only run an Analysis Services instance in a single mode at a time. You cannot host multidimensional and tabular databases on the same instance. If you must have both types of databases, you need two instances.

To change the DeploymentMode property:

1. 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.
2. 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.
3. Open the config file in Notepad. Change the DeploymentMode property from 0 (multidimensional) to 2 (tabular), as pictured. Save and close the file.
4. Copy the msmdsrv.ini file back to the OLAP\Config directory.
5. From services.msc, restart the Analysis Services instance.

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.

And if you are wondering why it is DeploymentMode=2 for tabular and not 1, DeploymentMode=1 is for PowerPivot for SharePoint instances. The difference between these two deployment modes is subtle. PowerPivot for SharePoint only supports impersonating the current user for processing, which makes sense since refresh is often an interactive process from the browser. Tabular supports only impersonating a specific Windows user, impersonating the service account, or inheriting permissions from the database when processing data into the model (see my previous post on impersonation for details). This makes sense because processing is mostly an unattended process for server instances. Also, based on the deployment mode, features in SSMS are blocked because some activities do not make sense on a PowerPivot for SharePoint instance (eg Restore, Restore from PowerPivot).

Because there are three different deployment modes in all, if you need multidimensional, PowerPivot for SharePoint, and tabular, you must have three different instances of Analysis Services.