CODEWITHSUNDEEP
sql-servermultidimensional-arrayssassql-server-2014tabular
Krzysztof Piszko
Krzysztof Piszko

Reputation: 1445

Changing SQL Server DB from tabular to multidimensional

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?

Upvotes: 14

Views: 22994

Answers (5)

Hasan Rameh
Hasan Rameh

Reputation: 1

1.Access to C:\Program Files\Microsoft SQL Server\MSAS14.SQLSERVER2017\OLAP\Config and then find msmdsrv 134467-path.png

2.Copy the file to a temp folder, open with notepad. In the below screenshot, the Deployment mode is set to 2 ( tabular mode ), you should change it to 0 ( multidimensional model ).

3.Copy and replace the msmdsrv.ini file back to the OLAP\Config directory.

4.Go to services.msc and restart the Analysis Services instance.

5.Connect to the server in SQL Server Management Studio, and then you could find that the model has been changed.

Upvotes: 0

RBT
RBT

Reputation: 25877

Best recommendation is to reinstall only SQL Server Analysis Services feature without disrupting other features/components like SQL Server Engine. During reinstallation of the feature, we can change the configuration of Analysis Services to Multidimensional and Data Mining Mode. The whole reinstall process takes less than 10 minutes. So, this approach is easy and quick.

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

  1. Go to Add Remove Program (ARP) Window in Control Panel. Alternatively, you run appwiz.cpl command from Windows Run prompt (Refer screenshot).

    enter image description here

    Select the row for Microsoft SQL Server 2017 (64-bit) and click on Uninstall/Change (Refer screenshot).

    enter image description here

  2. It'll open the SQL Server 2017 change wizard (Refer screenshot):

    enter image description here

  3. Click on Remove link

  4. Select the Analysis Services checkbox on Select Features step of uninstallation wizard (Refer screenshot):

    enter image description here

  5. Complete remove action by following the remaining steps in the guided wizard. They are self-explanatory in nature.

  6. Restart from step 1 but this time click on Add link (Refer screenshot in step # 2) to start installation wizard.

  7. During feature addition, it'll ask for the location of SQL Server setup files. Setup files can be present in a folder in your hard disk, Compact Disk (CD), or a mounted virtual drive via an ISO image file.

  8. Reinstall SQL Server Analysis Services feature. On the Analysis Services Configuration step of the installation wizard, go to Server Configuration tab and select the Multi-dimensional and Data Mining Mode option button (Refer screenshot):

    enter image description here

  9. Click Next > and complete the installation by following the remaining steps in the guided wizard. They are self-explanatory in nature.

Upvotes: 12

GregGalloway
GregGalloway

Reputation: 11625

It is possible to stop SSAS, edit the msmdsrv.ini and change DeploymentMode from 2 to 0. Empty the DataDir folder. Then start SSAS. This will change the instance from Tabular mode to Multidimensional mode. It will not convert models.

Cathy Dumas describes the reverse here.

Upvotes: 11

Gursanjit Singh
Gursanjit Singh

Reputation: 31

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.

Upvotes: 3

FLICKER
FLICKER

Reputation: 6683

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.

Upvotes: 8

Related Questions