Analysis Services Dimension Security - via AMO

Reposted from Jens Vestergaard's blog with the authors permission.

As part of a portal our team is currently developing, I needed a way to push permissions set in the portal onto the Analysis Services instances present in the system. A big part of the solution is what we call meta-data driven BI, which means the portal acts as a toolbox of common data warehouse best practices driven by meta-data. Hence cubes are represented by the name of the server, database and cube, as three columns in a table called Cubes. All this meta-data is marshalled by up to several types of user profiles. The data steward has his domain, the BI consultant another, the end-user a third and so on. The portal tries to embraces every step of the process, from raw text files to flashy end-user reports.
We’re not quite there yet, but well on our way. In short, we’ve got ourselves a data staging area (DSA), an enterprise data warehouse (EDW), traditional portal administration functions, data marts (DM) on the back of that and now finally we can push permissions onto Analysis Services. It’s not the first time I’ve crossed blades with Analysis services Management Objects (AMO), and that saved my butt more than once. The trick is that from the portal administrators perspective, a user is denied any access by default, in Analysis Services it’s the other way around. If nothings defined, your free to browse your eyelids off.

Maybe it’s appropriate to adress the dynamic external assembly solution that exists. See an example by Chris Webb (blog|twitter) and read the main reason why we did not choose this path.

As an extension to the way permissions work in Analysis Services, the design and implementation should be able to handle what is refered to as Down-Permissions, Up-Permissions and UpDown-Permissions.

Down-Permissions are best described as a member in the allowed set, with Visual Totals checked. See the detailed description of Visual Totals here.

Example (from a modified AdventureWorks database/cube):

Up-Permissions is a type of permission the is constructed in such a way, that the role has access to the member in question and all that members ancestors, and ancestors only not their siblings. Visual Totals is not checked. So data is aggregated on the ancestors, so at top level the role will see the grand total.

Example:

UpDown-Permissions is a combination of the two former types. This means deined access to parent siblings, but full path to root, and Visual Totals not checked.

Example:

All this is set through the portal, by clicking a mouse and selecting the permission you want to grant. Something even some super-users could be trusted. No need for knowing the nitty-gritty details of MDX or anything like that. See actual screendump, sorry for the language in the screen shot, but I think you get the point all the while:

Jens has been working in the IT Industry since mid 90s. He started focusing in on the SQL Server Stack around 2003, mainly Analysis Services and the RDBMS. His blog is at http://www.t-sql.dk and he currently works for http://www.rehfeld.dk as a senior BI consultant.