SSAS: Cube Deployment Issues with Deployment Wizard addressed in SQL Server 2008

If you recently upgraded to SQL Server 2008 or considering an upgrade, you would be glad to know that the Deployment Wizard of Analysis Services works in the way it should (long overdue). In earlier versions, if a new version of the Analysis Service database was deployed over an one, immediately after deployment all the roles would have no permission to the cube even if “retain role and members” option is chosen. Not only that, the role definition especially Dimension Data and Cell Data Access permissions would be lost after deployment. The only saving grace was the role membership remained intact.

With 2008 version, if retain roles and members option is chosen during deployment,

  • Roles retain their existing permission to cubes and mining structures after deployment. Existing permissions are not lost.
  • Dimension Data and Cell Data permissions are preserved in the role definition after deployment.
  • And Role membership stays put.

There is a gotcha though … if you script a role from SQL Server Management Studio as XMLA, the Dimension Data Permission and Cell Data Permission will not be scripted with the role definition. This is because as in previous versions, Dimension Data Permission is considered to be part of Dimension Definition and will be scripted as part of Dimension. Similarly Cell Data Permission is considered to be part of Cube definition and will be scripted as part of Cube.

Benny Austin


  1. I have created package which gives me the complete data required for the report In SSIS Target Table.

    I have created a SSAS cube based on single table(SSIS Target Table) which i made using SSIS package. The table contains millions records.In a cube i used only single table.I havent used dimension and fact table because i am getting complete result from that table which required in report making.

    “will my performance hamper…. if i havent used any dimesion and fact tables here.i used only single table which contains some facts and some dimensions which i required in report”

    I am getting sytem out of memory execptions after previewing the report in SSRS 2008 R2.


    • Tejas, there is no hard and fast rule. I would not know the rationale behind your design decision and hence would not be able to make a call on this.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s