August 28, 2015 at 7:08 am
Hi folks,
Using SSAS in SQL Server 2008R2, my intention is to bulk-update several roles in an SSAS database in one go, using an ALTER-script. I only want to modify the usergroups and useraccounts connected to the roles, everything else in the database should remain unchanged. (This is of course very important.)
What I have done so far:
- I started by creating an ALTER-script of the entire database.
- I removed all unrelated XML before and after the <Roles> and </Roles> tags.
- I changed the value of the ALTER-element's ObjectExpansion-attribute from "ExpandFull" to "ObjectProperties",
To my understanding the value "ExpandFull" would erase all other objects in the SSAS database (except for the ones included in the script). When testing the script, I have also verified that this indeed seems to be the case.
As mentioned before, I only want to change the usergroups and useraccounts in the roles (by using Search&Replace) before executing the script. Nothing else should be changed in the SSAS database. For example, all existing permissions connected to the roles should remain unchanged and of course all cubes, dimensions and DSV's too.
Will the usage of the value ObjectExpansion="ObjectProperties" in the ALTER-element in my script satisfy those requirements?
Here is a text from Microsoft's documention regarding this, but since I don't understand it completely I am asking in this forum instead.
If set to ObjectProperties, the ObjectDefinition element should contain only the complete definition of the major object to be altered, including subordinate minor objects. Subordinate major objects of the object to be altered remain unchanged.
For reference, below is the structure of my XMLA script.
Thanks in advance,
Martin
<Alter ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>MySSASDB</DatabaseID>
</Object>
<ObjectDefinition>
<Database various xmlns-references...>
<ID>MySSASDB</ID>
<Name>MySSASDB</Name>
<Roles>
<Role1>
<role-elements> (ID, Name, Members etc)
</Role1>
<Role2>
<role-elements> (ID, Name, Members etc)
</Role2>
</Roles>
</Database>
</ObjectDefinition>
</Alter>
August 28, 2015 at 7:19 am
This should be simple enough to test , just like how you tested EXplanFull ,whats the problem here? Is it not working as expected?
I have always used Expand Full mainly to add partitions, the idea with expand full is minor obejcts are deleted if they are not part of the script.
For example a Partition that has aggregates in the SSAS Database but not in your script. If the script is run the partition is altered but the aggregations are lost.
I am not sure what the minor objects would be when it comes to roles but I ttesting both ways with Roles I didnt see any difference in behaviour.
August 28, 2015 at 8:59 am
Thanks for the reply,
I just realised the method I posted before does not work. In my case, using ObjectExpansion="ObjectProperties" only seems to be working when pointing out one single role within the Object-element and not if pointing out a database. (In other words it works when I have scripted out ALTER TO on a Role, but not on a database where I then change stuff in the role-elements).
So in other words, to "bulk" update all roles in one go I think it comes down to one option and that is scripting out ALTER TO the entire database, leave all content in the script, use the "ExpandFull" property, do the bulk Search&Replace for the content in the roles, and then execute the script. Please correct me if I am wrong 🙂
By the way, does anyone know what happens to aggregations and cache and such things, when running ALTER-script on a SSAS database? Are those left untouched?
Martin
August 29, 2015 at 2:32 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply