Part I – The Measure Group Setup
The idea of dynamic dimension security is that with a single security role, each user gets a different list of members, based on an MDX expression that returns the permitted set. For example, User A may be allowed read access to different products than User B.
The MDX Username function returns the current user’s login information. If there is a many-to-many relationship between the User and the permitted products, you need a security map table, which is actually a “fact”, or bridging, table that contains one foreign key for the user and another for the product.
The SQL Server 2005 Adventure Works sample cube does not include a specific security map table, but it is possible to use the Internet Sales measure group as a surrogate: If a user (aka customer) purchased a product over the internet ([Internet Sales Amount]), then that user has the right to see the total sales for that product ([Sales Amount]). The structure is the same as if we used a regular security map table.
With a real security map table, you would create a measure group, and use a simple count of the rows as the measure, naming it something cool like [Security OK]. In this example, [Internet Sales Amount] is the surrogate for the [Security OK] measure.
Part II – The Username comparison
The format returned by the Username function is DOMAIN\User. In an ideal world, your dimension table would have an attribute in the exact same format. In the real world, you often have something a little different. For example, the name in the dimension may not include the domain. The closest thing to a user name in the Adventure Works Customer dimension is the Email Address attribute. Its format is User@adventureworks.com. This is not a bad approximation for a real-world user name attribute.
In the MDX security expression you compare the result of the Username function with the value of the [Email Address] attribute. Here’s an expression that will convert the Username to the right format for the Adventure Works [Email Address]:
Mid(Username,InStr(1,TestName,"\")+1) + "@adventure-works.com"
This expression will work even if users can be on multiple domains. If you know that all users will be on the same domain, you can make the expression evaluate much faster by replacing the (slow) InStr function with a hard-coded length. Assuming a constant domain name of DOMAIN, this formula is the simplified version of the previous one:
Mid(Username,7+1) + "@adventure-works.com"
For testing purposes (in case your current domain user name doesn’t happen to be in the Adventure Works Customer dimension), you can create a calculated member as a surrogate for the Username function:
Member Measures.TestName as "DOMAIN\anne14"
Then, during early testing, you simply replace the Username function with the TestName calculated member. This makes it easy to try out different “users”. Then, during final testing, switch it back to use the Username function.
Part III: The MDX Set Function
The general concept of the set function is to find all the products where the intersection of the product, the current user, and the [Security OK] measure is not empty. (I’m going to show three methods for doing the set. If you just want the “best” answer, skip to the third one.)
** Double-Filter Method (2000 and 2005)
Here’s the way I usually did this in 2000. You use an “inner filter”, to find the User whose email alias matches the user logon (there should be only one), and use Item(0).Item(0) to turn it into a member. Then you combine that member with the test measure, and check for whether that tuple is empty in an outer filter against the list of products. This technique does work in 2005, but it’s a bit slow. On my test box it was about 1 minute.
Filter( [Product].[Model Name].[Model Name].Members, not IsEmpty(
( Filter ( [Customer].[Email Address].[Email Address].Members
, [Customer].[Email Address].CurrentMember.Name = TestNameX
).Item(0).Item(0) , [Measures].[Internet Sales Amount] ) ) )
Note: To test the set, you can run a simple query:
With
Member Measures.TestName as …
Member Measures.TestNameX as …
Set SecurityTest as …
Select [Measures].[ Sales Amount] on 0,
SecurityTest on 1
From [Adventure Works];
** NonEmpty Method (2005 only)
One way to simplify this is to use the NonEmpty function that is new in 2005. This basically puts the outer Filter and the IsEmpty test into a single function, but the logic is basically the same: Filter the list of products by testing it against the (single member) set of email members that match the current user. Note that you don’t need the .Item(0).Item(0) for this one, because NonEmpty is comparing two sets, so the result of the Filter function is fine as is. This is much faster—on my box, it’s about 3 seconds. Quite an improvement over 60 seconds.
NonEmpty ( [Product].[Model Name].[Model Name].Members
, ( Filter ( [Customer].[Email Address].[Email Address].Members
, [Customer].[Email Address].CurrentMember.Name = TestNameX
) , [Measures].[Internet Sales Amount] ) )
** NonEmptyCrossJoin Method (2000 and 2005)
Another way to do it—and this theoretically works in 2000 as well as 2005, but I don’t have a 2000 instance handy to test it on—is to use the NonEmptyCrossJoin function. Mosha says he hates the NECJ function (which is OK for him to say because he wrote it), and to use NonEmpty instead. But the NonEmpty function only takes two sets, and the NECJ function can take an indeterminate number. I have always had a great fondness for NECJ. The real goal is to find all the members of the Product dimension that actually exist in the Security measure group, when intersected with the specified member. This approach took < 1 second on my box, and I still like NECJ. Maybe there’s a better way in 2005 that can avoid NECJ, but I like this one. Plus, as an added bonus, it also exploits StrToMember which is another “no no” that I love to use.
NonEmptyCrossjoin(
[Product].[Model Name].[Model Name].Members
, StrToMember("[Customer].[Email Address].[Email Address].[" + TestNameX + "]")
, [Measures].[Internet Sales Amount] , 1 )
-- Reed Jacobson