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:

  1. Kev And CharlotteAugust 7, 2015 at 7:08 PM

    Thank you! most helpful.

    ReplyDelete
  2. sammydeprezSeptember 10, 2015 at 7:22 PM

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

    ReplyDelete
  3. from A True GuyJanuary 23, 2016 at 9:32 AM

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

    ReplyDelete
  4. UnknownJune 22, 2017 at 9:44 PM

    Thanks for the tip. Saved me a lot of time

    ReplyDelete
  5. prashantiyerSeptember 21, 2017 at 4:59 PM

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

    ReplyDelete
  6. UnknownMay 28, 2018 at 11:29 AM

    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.

    ReplyDelete
  7. AnonymousApril 29, 2019 at 2:40 PM

    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

    ReplyDelete
  8. AnonymousApril 29, 2019 at 2:41 PM

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

    ReplyDelete
  9. AnonymousApril 29, 2019 at 2:42 PM

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

    ReplyDelete
  10. indomakmurMay 2, 2020 at 1:39 PM

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

    ReplyDelete