No maintenance OLAP security for thousands of users

Analysis Services, and OLAP cubes in general, have very powerful and functionally useful security. When security is working well, it adds no performance overhead to cube queries and allows reports and dashboards to be shared amongst large numbers of users with differring security profiles. In general, I find that BI designers use OLAP security reluctantly, when they should see it as an opportunity to support features with a simpler design.

There are so many things you can do with OLAP security. For example, letting some people see numbers without financials, some geographies, all customers but not addresses/phone numbers etc. I need to dedicate a blog just to security.

However, one overhead that can be a burden with OLAP security is that you have to assign users (or groups) to the roles. Not an arduous task, but it is a maintenance task. This burden can be avoided if you use Windows groups, since the maintenance is being performed anyway and there is nothing to do in Analysis Services.

An additional trick that I have found useful with two of my clients who have had requirements that individual users can only see "their" data. This could mean creating/removing roles every time there is a new/dropped user. The trick I have employed is to use the Username() function in Analysis Services, and essentially tell Analysis Services that the user can only see the member that is equivalent to their username. Or some function of it. For example, when RichardLees requests data from the cube (within a report, dashboard, application etc), Richard only sees data where RichardLees is the dimension member. The Username could identify a user, customer, client, store manager etc. Simply go to “Dimension Data” tab of Analysis Services, select the dimension with Usernames, select the username hierarchy, Advanced tab and enter in an MDX expression that will result in a fully qualified member name based on the Username. For example strtomember("[User].[Username]" + ".&[" + UserName()+ "]"). You can easily include some string manipulation in the MDX expression if your username is not exactly the same as the member name.

With this security in place, you might have 10,000 users, and they can all ask for exactly the same report/dashboard, but they will only get data for themselves. In this way, you write 1 report/dashboard, but it is really 10,000 different dashboards because users only see their report/dashboard.

I would put this on my demonstration site http://RichardLees.com.au/Sites/Demonstrations but since I don't get users to login, it wouldn't look very interesting.