How to optimize the dimension security performance using partitioning in SSAS Multidimensional

Introduction

In the articles How to partition an SSAS Cube in Analysis Services Multidimensional and Benefits of Partitioning an SSAS Multidimensional Cube, the concept of measure group partitioning is introduced and the advantages are clearly illustrated. One of the biggest advantages of partitioning is partition elimination, where only the partitions necessary to satisfy the query are read instead of all the data.

In this article, we’ll investigate how we can couple partition elimination to dimension security. With dimension security, we restrict the members a certain user can see for a specific dimension. For example, we could create a role where members of the role only can view data of a specific region. If this region is contained in one single partition, only this partition needs to be read.

Set-up Dimension Security

In this article, we’ll build further upon the set-up used in the previous articles about partitioning. To recap, we have 7 partitions on top of the sales regions in the WideWorldImporters database:

Let us now create a new role. Members of this role will only be able to view sales for the Far West region.

In the Membership tab, we need to add a domain user. We will use this user to test the role security.

We can leave the Data Sources tab as is. In the Cubes tab, we need to give the members of the role read permission on the cube, otherwise they won’t be able to browse the data at all.

Cell Data is used to configure cell security. Since it has quite a performance impact, dimension security is used in most scenarios. In the Dimensions tab itself, there is not much to configure. You can either give Read permission, or Read/Write permission (for dimension write-back scenarios).

Remark there isn’t an option to restrict access to an entire dimension. You can only restrict access to specific members of a dimension, which you can configure in the Dimension Data tab. The first step is to select the dimension (a regular dimension or a cube dimension) on which we want to apply security.

Next we need to select the desired attribute:

There are two options for securing members of a dimension attribute:

Select all members. By default, the role has access to all members. You need to deselect members for which you want to deny access. New members will be allowed.
Deselect all members. The opposite of the previous option. Not a single member is allowed, unless explicitly selected. New members are denied by default.

In this example, we will explicitly deny all members and give only access to the Far West region:

The role is now configured and can be deployed to the server.

Impact of Dimension Security on Partitioning

Let’s create a quick report using Power BI with the credentials of UserA.

We can see the user can only see the “Far West” sales territory. However, the grand total still shows the sales count for all territories. What is happening? Well, this is behavior is the default because the cube shows the result for the All member. The cube reads all the data and then filters out the territories on the axis. However, the totals still show the result for all the territories. To avoid this, you can select the Enable Visual Totals checkbox in the Advanced tab. In this tab, you can also edit the MDX statements for the allowed or denied member set.

The downsides of not enabling visual totals are clear:

You see the totals for all values. This might impose a data breach. For example, suppose there are only two territories. You could deduct the values of the other territory easily by just subtracting your values from the totals. In most cases, you want to avoid such scenarios and display totals for the allowed members only.
Also, because you need to have data for the totals, the cube needs to either rely on aggregations, but possibly also needs to read all partitions.

Remark: the table in Power BI Desktop shows a correct total. However, this depends on the client tool we are using. If we would create the same table in Excel, we can see that the grand total doesn’t match the actual total of the rows.

When we look at Profiler, we can also see that all partitions are read:

When Visual Totals are enabled, we get the result we expect in Excel:

And in Power BI as well:

Now the totals are calculated only for the Far West territory, even if it is not present on one of the axis. When we look at profiler, we can see only one partition has been read (after clearing the cache):

There is one big cave-at though: it is possible all partitions are read when the partitioning attribute (Sales Territory in this example) is included in the axis. For example, let’s add the attribute to the table in Power BI Desktop:

When we look at Profiler, we can see all partitions are read:

Let’s add a slicer on Sales Territory (which doesn’t make sense in the security context, but it’s for demonstration purposes):

Now only one partition is read:

This behavior is not well documented and there is not a clear explanation for now. It seems if you include the partition attribute on the axis, all partitions are read. If you explicitly filter on it, partition elimination does happen. Also, if you don’t reference the partition attribute at all – as we did earlier – then partitions are eliminated as well.

At a customer, I have a similar set-up where security is dynamically assigned depending on the user that logs in. Security is assigned to the employee name. However, when creating a report with the employee name included, only the relevant partition is read, as expected. So, it’s not clear what causes this behavior.

Conclusion

With the set-up described in this article, we can eliminate reading several partitions when the attribute we’re partitioning on is also the same attribute used in dimension security. However, in some cases, it’s possible all partitions are read if the partitioning attribute is included on the axis. Make sure to test your solution to see if it is applicable to your case.

The previous articles in this series:

Koen Verbeeck

Koen Verbeeck is a Business Intelligence professional working at element61. He helps clients to get insight in their data and to improve their business intelligence solutions.

Koen has over 7 years of experience in developing data warehouses, cubes, and reports using the Microsoft BI stack. Somehow he has developed a particular love for Integration Services along the way.

He has a blog at http://www.sqlkover.com and he is a frequent speaker at local SQL Server events. You can find him on Twitter as @Ko_Ver.

View all posts by Koen Verbeeck

Introduction

Set-up Dimension Security

Impact of Dimension Security on Partitioning

Conclusion

Reference Links

Related posts: