Wednesday, January 24, 2024

Change SSAS server mode from Multidimensional to Tabular

I had to change a SQL Server Analysis Service (SSAS) instance from Multidimensional to Tabular today, and want to share the steps with you, my loyal readers.  😊

Why was this needed?  Because I didn't ask beforehand what deployment mode was desired, and I just used the default of 0, which is multidimensional.  My mistake.  Haste makes waste, I believe they say.  But fortunately, the fix is easy.  No reinstallation needed.  

These are my steps:

1.      Backup any databases and detach (Multidimensional databases are not usable in Tabular instance).

2.      Stop SSAS service

3.      Open notepad as administrator, then File + Open, browse to your \OLAP\Config directory:

D:\Program Files\Microsot SQL Server\MSAS16.MSSQLSERVER\OLAP\Config

4.      Open the msmdsrv.ini file, change DeploymentMode to 2, save and close file.

5.      Restart SSAS service

6.      All done


This is where your msmdsrv.ini file is, and the location of DeploymentMode setting within it:



There are 3 DeploymentModes, and the same steps can be used to change to Multidimensional or SharePoint:

0  Multidimensional
1  SharePoint
2  Tabular
    

Unsure what your current SSAS DeploymentMode is?  Launch SSMS, connect to Analysis Services, right click server, choose 'Properties' and here you go:


Any reference up there to the \OLAP\Config directory will change based on your SSAS build.  Mine is at \MSAS16.MSSQLSERVER\OLAP\Config for v2022, but yours will vary if your version is different.

Further reading on your SSAS server mode:

https://learn.microsoft.com/en-us/analysis-services/instances/determine-the-server-mode-of-an-analysis-services-instance?view=asallproducts-allversions&viewFallbackFrom=asallproducts-allversionshttps%3A%2F%2Fwww.google.com

Hope to have helped!

No comments:

Post a Comment