Data Warehousing and Business Intelligence

18 June 2010

Creating Many Roles in SSAS Cube

Filed under: Analysis Services — Vincent Rainardi @ 4:54 pm
Tags:

If one day you had to create 100 roles in SSAS cube, all of which are with dimensional security*, then might be worth automating it. Here I would like to write down the possible options.

*as in, access to the dimension data, not access to the dimension itself. And I think the methods I’m about to write here are also applicable for cell security (as in, access to cell data). Remember that “access to cell data does not automatically give access to the dimension data” – see R2 doc: Analysis Services – Multidimensional Data – Security and Protection – Secure Operation – Granting User Access.

The methods that I’m thinking we can use to automate the process of creating the roles with dimensional security are:

a)  AMO
The idea is to add those role in the cube using AMO. Then define the dimensional security.

b)  XMLA
The idea is to compose XMLA scripts that create the roles, and the dimensional security, apply/execute this XMLA against the cube to create those roles, then rebuild the BIDS solution from the server.

Using AMO

In AMO we have a Role class. The constructor of this class is (C#) “public Role(string)” with string being the name of the role. We also have the option of using Role(string, string) constructor, with the second string being the ID of the role.

We also have Role Collection class. In this class we have an Add method, which is useful for creating a new role at the end of the collection. The creation is by the ID of the role, or by name and ID. If we want to create the role somewhere in the middle of the collection (rather than at the end), we can use the Insert method. Again we can use the ID of the role, or name and ID.

The RoleCollection class also has a Contains method, which indicates whether the collection contains a certain role or not, by ID (as string). This method is very useful for checking whether the role we are about the create already exists or not. As usual, like in the Cube class, the more useful method is Find and FindByName.

After we create the role, we need to give it access to the cube. For this we can use CubePermission class. The constructor has 3 parameters: the role ID, the cube name and the cube ID.

We also have CubePermissionCollection class, which has an Add method. This method can take 2 parameters (role ID and the cube permission name) or 3 parameters, with the 3rd param being the ID of the cube permission.

Then we need to create the dimensional security. We can use DimensionPermission class for this. The constructor has 3 parameters: the role ID, the dimension permission name, and the dimension permission ID.

We also have DimensionPermissionCollection class. This class has an Add method, which takes 2 parameters: the role ID and the dimension permission name. The Add method can also take a third parameter: dimension permission ID. As usual this class also has an Insert method.

Using XMLA

First we create the role, then give it cube access, then give it dimension permission. Something like this:
<Role>
  <ID>Role1</ID>
  <Name>Role1</Name>
  <Members>
    <Member>
      <Name>DOMAIN\ADGroup1</Name>
    </Member>
  </Members>
</Role>

<CubePermission>
  <ID>CubePermission 73</ID>
  <Name>CubePermission 73</Name>
  <RoleID>Role1</RoleID>
  <Read>Allowed</Read>
  <ReadSourceData>None</ReadSourceData>
</CubePermission>

<DimensionPermission>
  <ID>DimensionPermission 23</ID>
  <Name>DimensionPermission 23</Name>
  <RoleID>Role1</RoleID>
  <Read>Allowed</Read>
  <AttributePermissions>
    <AttributePermission>
      <AttributeID>Attr1</AttributeID>
    </AttributePermission>
    <AttributePermission>
      <AttributeID>Attr2</AttributeID>
      <AllowedSet>{[Dim1].[Attr2].&amp;[Member1]}</AllowedSet>
      <VisualTotals>1</VisualTotals>
    </AttributePermission>
  </AttributePermissions>
</DimensionPermission>

We then execute the XMLA against a cube, and then create the BIDS project from the cube: in BIDS, File menu, New Project, choose project type = Import SSAS database.

Creating Roles from other Cube

If the task is to mimic the roles on cube1 based on the roles on cube2, then we either a) use AMO to get the details of the roles and dimensional security on the existing roles on cube2, then create the roles in cube1 using AMO, or b) script cube2 on the server into XMLA, programmatically extract the role and dimensional security parts from the XMLA then execute that extract on cube1.

I prefer method a) (AMO) than method b) (XMLA) because we have more control and it’s more flexible.

1 Comment »


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Blog at WordPress.com.

%d bloggers like this: