Analysis Services Dimension Security – via AMO

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:

 

“What should I do?” – Brett Favre

The full agenda for SQLBits X is now published (link), and it looks like it’s going to take some tough choices to find out which sessions I’m going to attend. I know my coworker Rasmus Reinholdt (LinkedIn) is going to attend the “Putting tabular models into production” session by Cathy Dumas (blog) where I will see the “Deep dive into Analysis Services” session by Akshai Mirchandani (LinkedIn), but where to go Friday and Saturday?

Query Tuning by Adam Machanic (blog|twitter), Big Data by Thomas Kejser (blog) and CDC by Allan Mitchell (blog|twitter) sessions fight for the kickoff, then of to the hands down winning session “SSIS in SQL Server 2012, Part 1: Developer Enhancements” by Matt Masson (blog|twitter) and if its half as good as it sounds to be, I’ll take the 2nd session as well. After lunch some nasty cuts have to made once again; either T-SQL candy by Mladen Prajdić (blog|twitter) or SSAS Load testing by Bob Duffy (blog). I’m leaning towards the latter.
The last session on Friday will be the hardest to pick; SSIS Catalog by Jamie Thomson (blog|twitter), SSAS Security by Chris Webb (blog|twitter) or SSIS Unit Testing by John Welch (blog|twitter)

Saturday morning starts off with either Hierarchies by Markus Ehrenmüller-Jensen (blog) or SSIS Dev Part 2. Then of to either Alex Whittles (blog|twitter) Loading data in DW, T-SQL Perfomance Tips by Milos Radivojevic (blog) or Query Tuning by Adam Machanic. After that Im probably of to Performance investigations by Akshai Mirchandani, followed by windowing functions by Fabiano Neves Amorim (blog|twitter) and finally Deep dive into DW in SQL Server 2012 by Dejan Sarka (blog)

I know the venue is not until late March, but still, so many options, so many good sessions…

And if you we’re wondering about the title, have a look at this: http://www.youtube.com/watch?v=POyFvDgV2cU