Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Raising your own Errors in Dynamic Security

with 6 comments

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:

image

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:

image

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.

Written by Chris Webb

December 15, 2010 at 7:20 pm

Posted in MDX

6 Responses

Subscribe to comments with RSS.

  1. Nice article Chris. This method is an excellent addition to the way Dynamic Security is implemented. I can think of a few cubes I’ll be revisiting :)

    Craig Bryden

    December 16, 2010 at 2:10 am

  2. Hi Chris,
    Just wondering if it is possible to refer to a Named Set within a role definition in the AllowedSet MDX box of Dimension Data security?
    Sorry this question isn’t related to the post, but thought you might be an excellent resource for answering my question.
    Thanks,
    -mark

    Mark Attridge

    August 30, 2011 at 7:21 pm

    • Ahh, yes, I’ve come across this. My advice is that it’s complicated, and so it’s best not to refer to named sets inside your roles. I believe you can use named sets in role definitions so long as you apply the security to the cube dimension (ie the dimension in the cube) rather than on the database dimension (which appears at the top of the list in the dimensions dropdown in the role editor).

      Chris Webb

      August 30, 2011 at 8:50 pm

      • We do in fact wish to apply security to the cube dimension, not database dimension. So if in theory this will work at the cube dimension level, then is there any special syntax to refer to the Named Set?

        So you know, when I typed {[MyNamedSet]} in the AllowedSet, saved the role and then applied the role in the cube browser, I am given the following error: “The ‘City’ attribute in the ‘Region’ dimension has a generated dimension security expression that is not valid.”

        Mark Attridge

        August 30, 2011 at 9:05 pm

      • Hmm, that error could be a lot of things. Have you tried using the set in a query, does it work? I sometimes also see this error when I’ve accidentally used a set expression that contains members from a different hierarchy to the one I’m trying to apply security to – can you check that?

        Chris Webb

        August 30, 2011 at 9:45 pm

  3. Yup, that was the problem. Thanks Chris!

    Mark Attridge

    August 30, 2011 at 9:54 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,190 other followers

%d bloggers like this: