Changing sql-server DB from tabular to multidimensional

9
votes

I have following problem: When I try to deploy my SSAS project (with cube, dimensions and all that jazz) to sql-server, it throws error saying that

You cannot deploy the model because the DB deployment server is not running in multidimensional mode.

I'm new to this, so it might be a dumb question, but how do I change database mode from tabular to multidimensional?

4
sql-servermultidimensional-arrayssassql-server-2014tabular
I think you mean SSAS, and not SSIS. – Tab Alleman
This is highly probable, as I've written, I'm really new to this. Going to edit tag. – Krzysztof Piszko
Are you trying to change the mode of the server or change the mode of your project? – GregGalloway

4 Answers

6
votes

Tabular and Multi Dimensional are completely different thing. When you install SQL Server, you have to choose which one you are going to install.

So, if you create a Tabular model, you only can deploy it to Tabular installation of SSAS and the same for Multi Dimensional

you can not convert those model to each other.

9
votes

It is possible to stop SSAS, edit the msmdsrv.ini and change DeploymentMode from 2 to 0. Empty the DataDir folder. Then start SSAS.

Cathy Dumas describes the reverse here.

6
votes

Best recommendation is to reinstall only SQL Server Analysis Services (without touching other components like SQL Server Engine) with Multidimensional and Data Mining mode. The whole reinstall process takes less than 10 minutes. So, it is easiest and safest approach.

I'm enlisting all the steps for SQL Server 2017 installation:

  1. Go to Add Remove Program (ARP) Window in control panel. Select the row for Microsoft SQL Server 2017 (64-bit) and click on Uninstall/Change
  2. It'll open the SQL Server 2017 change wizard as shown in the screenshot below: enter image description here
  3. Click on Remove
  4. Select Analysis Services for removal: enter image description here
  5. Complete remove action by following the wizard.
  6. Restart from step 1 above and go to Add feature wizard.
  7. During feature addition it'll ask for the location of SQL Server Setup files (which can be on a CD, or a mounted virtual drive)
  8. Reinstall SQL Server Analysis Services feature. This time install it in multi-dimensional and Data Mining Mode as shown in the screenshot below:

enter image description here

Click Next and complete the wizard.

1
votes

All you have to do it go edit the MSMDSRV.ini and change the deployment mode to either (0,1,2,3) depending on what you are trying to use. Also remember that you have to log off the SQL studio and log back in.