Raising your own Errors in Dynamic Security

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:
  • 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:

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:

ERROR("You forgot to pass in the Customdata connection string property!"),
INTERSECT({STRTOMEMBER("[Customer].[Country].&[" + CUSTOMDATA() + "]")}
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.

11 responses

  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 🙂

  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.

    • 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).

      • 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.”

      • 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?

    • The MDX will work however you are connecting, but I don’t know whether you would see the error if you were connecting from a stored procedure.

      • Hi Chris,
        I am struggling with passing customdata while connecting with cube in t-sql procedure. The way I know to connect cube in t-sql sp is via linked server. Can you please point me to an example or link? It would be very helpful.


      • Thanks Chris. I really appreciate your time on this.

        As a matter of fact, I realized hat when I am using SP for MDX execution, I don not need customdata and embed the filter directly in the SP based on parameter passed to SP (parameter same as customdata).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: