How to optimize the dimension security performance using partitioning in SSAS Multidimensional
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.