you have a requirement to capture the user accounts that log into your cube and
based on their username they will only be permitted to see specific data. This can be accomplished by creating dynamic
or table based security in Analysis Services which is much less difficult to
setup than it sounds like. In this
article I will walk you through the setup needed on the relational database
side as well as Analysis Services side to get this working in your
will start by identifying what attributes you want to base your security off
of. For example, maybe I want User1 to
only be able to see the sales for the states Florida, Georgia and Louisiana,
while User2 can see California, Oregon and Washington. Typically, if I were to create this security
I would create a role for each grouping of states and add the appropriate
users. An easier way to maintain
security like this example would be to store all this information in relational
tables that way a DBA that may not be as familiar with Analysis Services can
easily handle adding new users or modifying existing ones. This table structure would look something
FactSales is storing all the
company sales, while DimState has all the possible distinct states that sales
can occur in. This is a very small scale
example, but here’s in detail what DimUser and UserStateBridge tables store:
This table simply has a key
which is an identity column in this case and an account column which is the
user account that you wish to grant access.
So example records would look like this:
This is a table that has a
many-to-many relationship between the DimUser table and DimState. This many-to-many relationship allows for one
user to see many states and one state to accept many users. Here’s a sample of what this table may look
I’m not going to show you what the
State dimension would look like because this is just a typical dimension you
see day to day. The StateID here is the
identity column from the state table.
Once this table structure is created
your next step is to bring it into Analysis Services. Here are the steps to follow inside BIDS to
leverage these tables (I’ll assume you’ve already created a basic cube first):
Add these new tables (DimUser and
UserStateBridge) to you Data Source View.
Create a new dimension based off the DimUser
table. Hide all the columns by changing
AttributeHierarchyVisible to False. You
don’t want users to be able to view this dimension. Process this dimension.
Create a new Measure Group in the cube based off
the UserStateBridge. Delete all measure
it creates except for the automatically generated Count measure. Select the single measure left to be hidden
by selecting it and changing the Visible property to False. This will hide the entire Measure Group from
your users but you can still use it when writing MDX.
Ensure these two have a Regular relationship
setup in the Dimension Usage table of the cube browser. Often this relationship is created
In the Solution Explorer, right-click on Roles
and select New Role. Give Read access in
the General, Data Sources and Cubes tabs.
On the Membership tab add any groups or user accounts this Role will
On the Dimension Data tab select the State
dimension from the dropdown list. Then
select the StateID(you would likely have this column hidden in the dimension)
in the Attribute Hierarchy dropdown list.
Inside the Dimension Data tab select the
Advanced tab. In the Allowed member set
area enter this MDX:
[Dimension To Secure Name].[Dimension Key].Members,
StrToMember ("[User Dimension].[User Key].[" + UserName ()
[Measures].[Bridge Table Count Measure]
StrToMember ("[User].[User ID].[" + UserName () +
[Measures].[User State Bridge Count]
UserName() function will grab the user account that attempts to access the
cube. StrToMember converts a literal
string to a dimension member.
Last in the bottom left of your current screen
check the box called Enable Visual Totals.
Reprocess the entire SSAS database now. When you navigate to the cube browser now you
can test and make sure that your security is working properly by emulating one
of the users you’ve added to the role.
After verifying you security in the
browser if you still find problems with the results then your next step is to
check the user and bridge table. Again
this is a very basic example of creating dynamic security so if you did
something more complicated like based a role off of two bridge tables then you
may run into conflicts between the two.
For example, say you not only want to base your security off of state
but also on the store the items was sold at.
In that case you would create two bridge tables. One for state, which we’ve already done, and
a second for store built the same way the first one was. So image your fact table had some results
using multiple bridge tables in a single role you need to make sure that if
User1 has rights to StateID 3 in the UserStateBridge table then he also needs
rights to StoreID 4546 and 4578 in the store bridge table to be able to see
each record when browsing. This means
the bridge tables can actually over rule each other if they do not have
permission for both keys. This is
especially important if you’re using inferred members in you data warehouse
using some kind of indicator like -1. If
there is a store in an unknown store and you assign unknowns to a -1 key value
when you will want to make sure your users have access to that -1 store.
new accounts is very simple now. Add
them to the DimUser table and then reprocess.
If you need additional states associated with users simply add them to
the bridge table then reprocess the cube.
After reading this article if you
still need more information on this topic I highly recommend the book Expert
Cube Development with Microsoft SQL Server 2008 Analysis Services. This book shows many different types of
dynamic security including security based off of a stored procedure. Also feel free to email me with any questions
about this or any other topic at firstname.lastname@example.org. You can also post on our forums at www.bidn.com/forums.
If there are other higher levels, like Continent and Country, above State, will the user automatically be secured in those as well? Or do you have to add security attributes to those levels? Obviously, assuming that the State-Country-Continent relationships have to exist in the source tables already. Thanks.
If you pick the lowest level you should be good. Anything above will be included.
Great article! We had implemented something similiar in our cube. This method works great. For others that may try it, just wanted to mention the added benefits one gets in front-end tools like SSRS with this model. If you were to create a parameter list, for instance, populated with states, it would only be populated with the dimension members that are accessible to the current user. This makes for reports that can be used by a variety of users without any special coding in the reports. Visual Totals are cool in ensuring that the grand totals make sense for the current user while at the same time not divulging the whole pie (the "All" value for all states). Thanks again for sharing.
Me again. I'm trying to implement something like this, but am not getting it to work as expected. Can you try to add a Country level above the State to see if the Country is automatically secured based on the lower State?
My logic isn't exactly the same as yours, as I'm using IIF, Count, Exists, and Except expressions in the MDX to select specific members. Even if I have the lowest dimension level Allowed Member Set defined, the parent levels are visible and are only secured when I add an appropriate Allowed Member Set in the parent level, too.
Ideally, I want to secure the lowest level and have Analysis Services secure the levels above it automatically without having to define the Allowed Member Sets in those levels, too.
Let me know if that doesn't make sense. Thanks.
Is it showing all of the higher level or just sections of it that you have not locked down on the lower level. Sounds like this may be a case for multiple bridge tables if you need to secure both levels. I do give some warnings about multiple bridge tables in this article though.
I got it working! :) As I mentioned earlier, I have custom MDX in the Allowed Member Set, which contains the EXCEPT function. That function was returning the [All] member as part of the result set, which made it look like the security wasn't working. As soon as I included .DefaultMember as a parameter in the EXCEPT function (to exclude the [All] member), everything started working as expected. Thanks!
Another use of having the security in relational tables, one that I often use, is that when creating parameters in SSRS, you can use the relational tables and join to the dimension tables, filtering by the current user.
The advantage of this over
A) not doing it - is that it doesn't give the user options he can't use
B) doing it in MDX - is that the query is simpler (a join rather than an EXISTS on the measure) and most likely more performant
Nice article indeed! We are doing something similar for a client right now. And like Mark Stacey stated, it does make it easier to filter the results by the current user in SSRS.
Thanks for writing and sharing this article. We have implemented our dynamic security model exactly the same and it has been working for almost two years now.
It works perfectly in combination with Reporting Services 2005/2008. In Excel we are experiencing some vague problems. The most mysterious problem is in the next scenario:
User X connects to SSAS via Excel, with rights to see department A and B. At the beginning (=after SSAS restart or full cache refresh) everything works fine. After some slicing and dicing in a pivottable a black art magician comes into play and user X loses his rights to the cube and the strangest of all is that sometimes user X loses all his rights and sometimes it loses only a small part of his rights. Only a SSAS reset or a full cache refresh in SSMS will solve this problem. I already traced all the MDX queries of the users, but could not find something wrong. There was an issue with the SID, but correcting the SID did not help.
Have you ever bumped into a similiar problem?
Group (AD-group) security
Another limitation on this dynamic security thing, is that it only works on authorising Users and not directly on authorising groups.
I made an custom part in TSQL that connects to the AD and resolves the groupnames, but I do not think that this is the best solution.
Has someone made something similiar? I think the most 'beautiful' solution is to solve this in custom code, that can ben referenced in the MDX-filter part (other function o.s.)
but I really do not know how to implement this.
Thanks for your reply in advance.
We have recently implemented this solution, and the security works, however we are experiencing serious performance issues, where if you log in as an administrator so no security is applied, the cube runs fine without any performance hit. Whereas if I sign in as a user who is part of the user dimension, it can take up to 10minutes to run a report or browse the cube in management studio. However once this first load has happened then subsequent queries run in no time.
It seems that when first running a query it evaluates the whole cube for security and creates a cached cube just for that user. Now if we only had a couple of users we could cache this for each user at the start of each day, but we have 200+ users who will access the reports.
Has anyone else run into performance issues?