THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Dynamic Dimension Security in Analysis Services 2000

Subject of security in Analysis Services is close to my heart (besides my regular responsibilities, I am also responsible for the overall security in Analysis Services). According to the OLAP Report, Analysis Services has most advanced security features among all OLAP servers, security can be controlled on cube, dimension and cell levels, contingently and noncontingently, with or without visual totals etc. But what is even more interesting - all security definitions are done through MDX. This opens a whole big area of possibilities. For example, security definitions can be data driven, i.e. come from the member properties or even they can come from the external data sources by embedding calls to UDFs inside MDX security definitions. Another interesting scenario is to have single role behave differently for different users, by using the Username MDX function inside the security definitions. This is what we call "dynamic security". The clear advantage of this method is that instead of creating multiple roles, the single one can be maintained, which automatically adjusts as users are added, removed or their access rules change. When we released OLAP Services 7.0 SP1, I have written a whitepaper which explained how to get both data driven and dynamic security for the cell security definitions. Later, Russ Whitney wrote two articles for SQL Server Magazine(1,2), where he explored similar ideas but applied to dimension security. Russ's articles are often cited and referred to whenever there is a need to implement data driven or dynamic dimension security. However, while those articles are technically accurate on how to configure dynamic security, they have somewhat misleading and up to incorrect statements when it comes to the number of roles that can be handled by Analysis Services. Dave Wickert has done deep and thorough investigation of the subject, and gave several presentations about it. He now offered to share his presentation slides, speaker notes, sample project, demo notes, setup instructions etc on I am very grateful to Dave for that, and I am sure everybody who needs to implement dimension security is going to benefit greatly from his work.

Additional resources:

Published Thursday, December 16, 2004 5:05 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement