Change SSAS Deployment mode from multidimensional to tabular mode without reinstalling Analysis Services | SansSQL

Monday, November 25, 2013

Change SSAS Deployment mode from multidimensional to tabular mode without reinstalling Analysis Services

It so happens that sometimes minds change easily and things needs to be done without reinstalling.
One such case is changing the Deployment mode of SSAS from multidimensional to tabular mode in SQL Server 2012.
To change the deployment mode
  • Backup the multidimensional Analysis services databases on the instance (if any)
  • Detach the multidimensional Analysis services databases from the instance (if any). These databases will not be usable in tabular mode
  • Navigate to the path "<Install Location>:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config" and backup the file "msmdsrv.ini"
  • Open the file "msmdsrv.ini" and change the value of DeploymentMode to 2.
    0 - Multidimensional
    1 - SharePoint
    2 - Tabular
  • Re-Start the SQL Server Analysis services 
SSAS multidimensional mode - Before Change

SSAS tabular mode - After Change

10 comments:

Kev And Charlotte said...

Thank you! most helpful.

sammydeprez said...

Any one ever had any consequences because of changing this.
Am not sure this is the issue of my problem. But if I have a Role with RowFilter that uses USERNAME().
USERNAME() never returns the correct user...

from A True Guy said...

I was wondering how to get back to multidimensional mode from tabular mode. Such a wonderful post. Thanks mate...

Unknown said...

Thanks for the tip. Saved me a lot of time

prashantiyer said...

Hi, I'm getting an error MSSQLOLAPService . Client doesnt have required privelege
Can u please help with what should I change

Unknown said...

Hi, I am not able Open the file "msmdsrv.ini" and change the value of DeploymentMode to 2.
getting error like "You do not have permission to open this file. contact adminstrator". i am trying in personal laptop. please help me out issue.

Anonymous said...

old post but a scenario that has not been described in any comment or the article
this can however leave you with the scenario that there are no "create new database" option when right clicking the database node

Anonymous said...

@unknown open the editor as "run as administrator"...

Anonymous said...

@prashant ivyer you need to reset the groups in the security tab of the as instance properties e.g. add domain admins again

indomakmur said...

I’m a blog crazed person and i love to read cool blog like yours.~`;., https://royalcbd.com/product/cbd-oil-1000mg/

Post a Comment

Ads