Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 762
Krzysztof Piszko Avatar asked Apr 07 '16 18:04

Krzysztof Piszko


People also ask

Is tabular better than multidimensional?

Whenever you need to report aggregated data with predefined aggregations on Warm Cache, the Multidimensional Model is more performant. Tabular models outperform Multi-dimensional Models whenever you need a report on low granularity data. This is because they read columnar data from RAM.

Is SSAS multidimensional deprecated?

Deprecated features in SSAS 2019There are no deprecated features announced with this release.

How do I change an analysis service instance to Tabular mode?

Go to services. msc and restart the Analysis Services instance. Connect to the server in SQL Server Management Studio and right click on Analysis Services, then select Properties. You can now see the Analysis Services server mode is changed to Tabular mode.

How do I update my tabular model?

You can change the default compatibility level in SSDT in Tools > Options. To upgrade a tabular model project in SSDT, set the Compatibility Level property in the model Properties window. Keep in-mind, upgrading the compatibility level is irreversible.


2 Answers

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 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 link

  4. Select Analysis Services for removal:

    enter image description here

  5. Complete remove action by following the wizard.

  6. Restart from step 1 but this time click on Add link to start installation wizard.

  7. During feature addition it'll ask for the location of SQL Server setup files (which can be in your hard disk, CD, or a mounted virtual drive)

  8. Reinstall SQL Server Analysis Services feature. On the Analysis Services Configuration step, go to Server Configuration tab and select Multi-dimensional and Data Mining Mode as shown in the screenshot below:

    enter image description here

  9. Click Next > and complete the wizard.

like image 177
RBT Avatar answered Oct 20 '22 21:10

RBT


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.

like image 38
GregGalloway Avatar answered Oct 20 '22 21:10

GregGalloway