DeploymentMode = 2

How to easily put SQL Server Analysis Services (SSAS) to Tabular Mode

Fabian Henzler SQL Server 0 Comments

You can follow this tutorial. It shows how to install a new instance of SQL Server Analysis Services (SSAS) in Tabular Mode. But most of all this helps you if you just installed the wrong mode by accident. Also if you want to just easily put SQL Server Analysis Services (SSAS) to Tabular Mode this is how you can do it.

What is the SQL Server Analysis Services Tabular Mode?

Microsoft wrote a nice article “Comparing Tabular and Multidimensional Solutions”. In addition Daniel Calbimonte wrote on “Tabular vs Multidimensional models for SQL Server Analysis Services”.

Step 1 – Login to Analysis Service Instance

First of all make sure that if you logged in your SQL Server Management Studio (SSMS) into your Analysis Services Instance (SSAS).

SSMS Login to SSAS SSMS Connect

Step 2 – Detach all Analysis Services Databases

Backup

Now don’t worry – detaching them won’t delete them. But just be sure you should back them up quickly.

Detach

Open the Databases node.

SSAS DB

Then right click on the Database and click Detach.

 

SSAS DB Detach

Click OK to start removing the SSAS DB from your SSAS Instance

SSAS DB Detach OK

And this is how your blank SSAS Instance should look like.

SSAS DB Empty

Finally this is how my SQL Server 2016 Analysis Service and Database Engine look like.

Analysis Service Databases in SQL Server Management Studio

Step 3 – Edit msmdsrv.ini

No matter which version of MSSQL you installed, go to C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Config and you’ll find the msmdsrv.ini. Because you’ll always find the file we’re looking for in that exact place.

 

Backup the msmdsrv.ini

Create a copy of the msmdsrv.ini, rename it to msmdsrv.backup and leave it in the folder. As a result you can always come back to it if something went wrong.

Location of msmdsrv.ini

Change the Deployment Mode in the msmdsrv.ini

And now open the file, which is written in a XML notation and look for <DeploymentMode>. I use Visual Studio Code, because it’s a fast, reliable and cross platform editor by Microsoft. So if you want to follow this tutorial exactly, just quickly download it and we’re ready to go. But you’re totally free to use whatever editor you like of course!

DeploymentMode = 0

Replace the <DeploymentMode>0</DeploymentMode> with <DeploymentMode>2</DeploymentMode>. This sets it to Analysis Services Tabular Mode.

DeploymentMode = 2

If you’re running into any trouble regarding and Error on saving the file, don’t worry. Seems like Microsoft tries to protect you from harming the system, which we’re clearly not intending to. Save the file to the Desktop. Then replace it in the folder C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Config.

SaveToFilesystemError

Furthermore you can find all configuration option for the msmdsrv.ini in Server Properties in Analysis Services.

Step 4 – Restart Analysis Services

Last but not least you just have to restart the SQL Server Analysis Services (SSAS) and that’s it.

Restart SSAS

And now you can see the little Tabular icon appear in your SQL Server Management Studio (SSMS). Hence it worked and we’re done!

SSAS Tabular Mode

[et_bloom_inline optin_id=optin_2]

Leave a Reply