Powershell Roles Ssas Visual Total Amo Code To Derive
Powershell Roles Ssas Visual Total Amo How To Define Security

This is just a very simplified example but I used this approach on some of my enterprise deployments and it works just fine also for very complex scenarios.

Especially for big enterprise solutions the security-concept can become very complex.

Powershell Roles Ssas Visual Total Amo How To Define Security

Analysis Services Multidimensional in general offers two option on how to define security: Cell Data Permissions and Dimension Data Permissions.

Because of the huge impact on performance Cell Data Permissions are barely used.

Dimension Data Permissions are usually the way to go and can cover probably 98 of the security requirements.

Though, also Dimension Data Permissions can get quite complex, especially for large-scale cubes with 100 or even 1000 users.

Just think of an Analysis Services that holds sales data associated to KeyAccounts further assume that we have 100 KeyAccounts.

Each of this KeyAccounts is managed by one user, so we would end up creating 100 roles one for each KeyAccount and its manager.

In terms of maintenance this can get quite complex and in the long run unmanageable.

Dynamic Security is evaluated at runtime for each user and allows you to consolidate your roles.

So for our example from above instead of having 100 different roles we would end up having one dynamic role serving all 100 users.

Basically, when a user connects it queries internal data (within the cube using MDX) or external data (using Analysis Services Stored Procedure (ASSP)Assemblies) to get the security settings for the current user.

Powershell Roles Ssas Visual Total Amo Code To Derive

Especially in combination with ASSP this can be quite powerful as you can use any datasource that you can query using.Net-code to derive your security from.

But what about the Visual Totals setting You can only check or uncheck the checkbox and you are not able to use any dynamic expressions.

But there might be some rare scenarios where you also need to make the Visual Totals dynamically based on Username() or CustomData() and this is what this post is about.

Fortunately there are also other ways besides the UI to work with the cube and modify its structure.

I am talking about XMLA (XML for Analysis Services) here, which is the protocol that is used for all communications between a client and Analysis Services.

Instead of writing XMLA on your own there is also a programmatically way to do this: AMO (Analysis Management Object).

AMO is a.Net library that allows you to integrate calls to Analysis Services within any.Net code.

After modifying any object in the code AMO generates the according XMLA for you and sends it to the server which is a much more convenient way than writing plane XMLA on your own.

We will put our dynamic security on the Category attribute of our Product dimension.

Please note here that AMO only work with the Analysis Service directly (online) but not at design time in Visual Studio.

Once everything is deployed this is the AMO code to use to modify the predefined role.

The AttributePermission object actually represents the UI that we have seen above.

It has properties for all the fields and textboxes on the UI.

The most important of course are: AllowedSet DeniedSet Defaultmember and VisualTotals.