Introduction
Hey guys!
In this post I would like to show you how to change the DeploymentModel of the instance between Multidimensional and Tabular after the instance has been created, without having to create a new instance with the mode you want.
This is useful in cases where you ended up installing the wrong mode or want to change it with as little effort as possible.
Tabular or Multidimensional?
As cubes created in Tabular and Multidimensional modes are not compatible with each other, you will need to delete existing cubes before making this change, so please do not consider this post as a migration.
If you want to migrate the cubes from Tabular to Multidimensional or migrate from Multidimensional to Tabular (highly recommended), you will need to make this change mentioned in this post, and with the instance changed and without any cube created, you will need to create a new project for each database /cube in the desired mode, build the cube again from scratch and publish.
I don't know of an easier or faster way to do this cube migration without having to recreate it from scratch in a project that uses the mode you want to migrate to.
And there goes my recommendation to always use Analysis Services Tabular mode, unless there is some technical impediment to not doing so.
Advantages of using tabular mode:
- Much newer technology widely supported by Microsoft
- It's easier to develop
- It's faster to develop
- It has much more documentation and material in the technical community
- Uses DAX to perform the calculations (Multidimensional uses the complex and poorly documented MDX)
- You can use PowerQuery to do transformations
- Compresses data about 3x more than Multidimensional mode
- Supports edits with Tabular Editor
- Supports creating new measures via Power BI Desktop when connected in Live Connection mode
- No data duplication required to use DISTINCT functions (Multidimensional creates multiple measure groups for this)
- Same engine as Azure Analysis Services and Power BI
- It's easy to migrate the model to Power BI or Azure Analysis Services
If you want to know more details about Tabular vs Multidimensional, check out a video I made explaining these differences:
What is the DeploymentModel of the current instance?
To identify the deploymentModel of the current instance, open SQL Server Management Studio (ssms.exe)
In the Object Explorer screen, click on “Connect” and then on “Analysis Services”
Connect to the Analysis Services instance you want to change the deployment mode
In the Object Explorer screen, click on the Analysis Services instance, press the right mouse button and select the “Properties” option
On the properties screen, just look at the value of the “Server mode” property to find out if it is Tabular or Multidimensional.
How to change instance DeploymentModel between Multidimensional and Tabular
To change the instance's DeploymentModel between Multidimensional and Tabular, we first need to remove or detach all existing cubes, as there is no compatibility between tabular and multidimensional cubes. I suggest performing a backup of all cubes before.
After backing up the cubes and detaching or deleting the existing cubes, go to the “Config” folder of the Analysis Services instance you want to change. This folder is usually “C:\Program Files\Microsoft SQL Server\MSAS.\OLAP\Config”.
In my case, the path is “C:\Program Files\Microsoft SQL Server\MSAS16.SQL2022\OLAP\Config”, where the SQL Server version is 16 (SQL 2022) and the instance ID is “SQL2022 ”.
Copy the “msmdsrv.ini” file to your desktop or another directory that you have write access to (or you can edit it in that path, if you use an editor like Notepad++ and a Save as Admin extension).
Open the “msmdsrv.ini” file you copied and change the value of the “DeploymentMode” tag to the type you want, where:
- 0 = Multidimensional / Datamining
- 1 = Sharepoint
- 2 = Tabular
In my case, I'm going to change it from Tabular to Multidimensional (just for demonstration), and because of that, I'm going to change the value from 2 (Tabular) to 0 (Multidimensional).
Now copy back the file you edited to the original directory you copied it from, replacing the existing file.
The next step is to restart the Analysis Services service. To do so, access the “SQL Server Configuration Manager”
Click on the “SQL Server Services” menu, in the left corner, select the SQL Server Analysis Services service that is running the instance where you will move the cubes, right-click and select the “Restart” option to restart the service.
Ready! Now the type of Analysis Services has changed. Let's view the Analysis Services instance properties again
And as shown in the print below, the mode was successfully changed.
Please note that this solution is NOT officially supported by Microsoft. Use at your own risk.
If you try to detach the cube before changing the instance type, make that change and try to attach the cube again (which is not compatible), you will see the error message below:
I don't know of an easier or faster way to do this cube migration without having to recreate it from scratch in a project that uses the mode you want to migrate to.
And that's it, folks!
I hope you enjoyed this tip and until next time.
Portuguese 
English