Raising your own Errors in Dynamic Security

Reposted from Chris Webb's blog with the author's permission.

Last week I was working with a customer that is going to implement dynamic security using the CustomData function: they have their own web-based reporting app and want to use the CustomData connection string property to pass information about the user from the app back to a role in SSAS, which then dynamically generates an allowed set for dimension security based on that information. You can do something similar in the Adventure Works cube by doing the following:

• Create a new role, called “Role”, and give it access to the Adventure Works cube
• Go to the Dimension Data tab and select the Country attribute on the Customer cube dimension and select the ‘Deselect all members’ radio button
• Go to the Advanced tab and enter the following MDX in the Allowed Member Set textbox:
  {STRTOMEMBER("[Customer].[Country].&[" + CUSTOMDATA() + "]", CONSTRAINED)}
• Deploy the solution
• Go to SQL Management Studio and click the MDX button to open a new MDX query window
• In the connection dialog that appears, click the Options button, go to the Additional Connection Parameters tab, and then enter:
  Roles=Role;Customdata=Australia
• Ignore what you see in the metadata pane (it’s a different connection), watch out for this bug, and note that when you run the following query, only the country Australia is returned:

  SELECT {} ON 0,
  [Customer].[Country].[Country].MEMBERS ON 1
  FROM [Adventure Works]

Once this was working, the next question the customer had was what happens if you forget to pass in the CustomData connection string property, or you pass an invalid value in (in this case, a country that doesn’t exist on the hierarchy). Well, the good thing is that after you connect you can’t run any queries, but you do get an unhelpful error message like this:

The ‘Country’ attribute in the ‘Customer’ dimension has a generated dimension security expression that is not valid.
DimensionPermission (1, 2) The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated.

Not good for debugging, and a bit confusing for your users if they see it. Also, in some implementations of dynamic security (for example if you’re not using the Constrained flag or using a Filter) instead of getting an error you’ll be able to query the cube but just see no data in it, which is even more confusing.

So what we want to be able to do is display our own error messages in these situations as well as forcing a lock-out, and we can do this by using the MDX Error() function (I always knew I’d find a use for it someday!). In our example if we want to trap situations where the developer has forgotten to pass in the Customdata connection string property we can use the following MDX in our role:

IIF(
CUSTOMDATA()=NULL,
ERROR("You forgot to pass in the Customdata connection string property!"),
{STRTOMEMBER("[Customer].[Country].&[" + CUSTOMDATA() + "]")})

Now, when you connect you’ll see a slightly more helpful error message. Here’s what Excel shows when you open a connection using the role but don’t supply anything in Customdata:

Of course you’d probably want to handle more complex scenarios than this, so here’s a more complex example that handles missing and invalid Customdata values:

IIF(
CUSTOMDATA()=NULL,
ERROR("You forgot to pass in the Customdata connection string property!"),
IIF(
COUNT(
INTERSECT({STRTOMEMBER("[Customer].[Country].&[" + CUSTOMDATA() + "]")}
  ,[Customer].[Country].[Country].MEMBERS)
)=0,
ERROR("The country " + CUSTOMDATA() + " doesn’t exist on the Country hierarchy of the Customer dimension"),
{STRTOMEMBER("[Customer].[Country].&[" + CUSTOMDATA() + "]",CONSTRAINED)}))

And here’s what you get in Excel with this if you pass the value “Norway” using Customdata:

This may all seem like window dressing, but it can save you time later on when you’re trying to debug security problems over the phone with users or other developers.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .