Friday, September 7, 2012

Dynamic Dimension Security MDX Error

Getting errors:

Check MDX script syntax failed because of the following error:
----------------------------------
An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[Dim Employee]' was not found in the cube when the string, [Dim Employee].[LoginID].[CORP\Name], was parsed.

Solution:
When you set up your role, use the following MDX for the "Allowed member set" for the "User Name" attribute of the "Dim User" Dimension (not the Cube Dimension, the regular Dimension):
STRTOSET("[Dim User].[User Name].["+Username+"]")

You do NOT need to enable Visual Totals!

The other thing you need to set in your role is the "Allowed member set" for the "Dim Reseller" CUBE Dimension. This is important. If you put the security on the regular "Dim Reseller" Dimension, it doesn't seem to work. You have to secure the CUBE Dimension for "Dim Reseller" (I guess this is because only the Cube Dimension knows about the relationships defined for the cube)? Anyway, set the allowed member set to:
EXISTS(
[Dim Reseller].[Dim Reseller].Members,
STRTOSET("[Dim User].[User Name].["+Username+"]"),
'Fact Reseller User'
)


This time you DO need to enable Visual Totals (at least if you want to restrict the FactResellerSales data).

Referenced from Comments from:
http://hccmsbi.blogspot.ca/2007/08/implementing-user-specific-security-in.html