How to easily put SQL Server Analysis Services (SSAS) to Tabular Mode
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).
Step 2 – Detach all Analysis Services Databases
Now don’t worry – detaching them won’t delete them. But just be sure you should back them up quickly.
Open the Databases node.
Then right click on the Database and click Detach.
Click OK to start removing the SSAS DB from your SSAS Instance
And this is how your blank SSAS Instance should look like.
Finally this is how my SQL Server 2016 Analysis Service and Database Engine look like.
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.
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!
Replace the <DeploymentMode>0</DeploymentMode> with <DeploymentMode>2</DeploymentMode>. This sets it to Analysis Services Tabular Mode.
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.
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.
And now you can see the little Tabular icon appear in your SQL Server Management Studio (SSMS). Hence it worked and we’re done!