Post reply

Securing calculated measures in Analysis Svcs 2005

  • April 18, 2008 at 9:16 am

    #124875

    I've got a role for which I need to hide certain measures (as well as hide calculated measures based on the hidden measures). I have tried both of the following:

    "Select all members" and then de-select the measures to hide;

    "Deselect all members" and then select the measures to show

    Both give me an error when I test cube security: "Error occurred retrieving child nodes: MdxScript(Daily Cash Sales) (93,45) The dimension '[Gross Cost LY]' was not found in the cube when the string [Gross Cost LY], was parsed."

    [Gross Cost LY] is one of the measures that needs to be hidden, and it is also included as part of a calculation. When I remove the calculated measures, the error goes away, and [Gross Cost LY] is properly hidden.

    Is there somewhere else to go to hide the calculated measures?

    Thanks,

    Marianne

  • April 23, 2008 at 7:54 pm

    #806353

    Any thoughts???

    Thanks,

    Marianne

  • April 24, 2008 at 2:46 pm

    #806905

    Hey Marianne,

    Hope i'm not overly simplifying the problem, but could you just set the visibility (hidden) for the source measures to not visible (hidden == true)?

    This won't make them inaccessible to someone who i) knew they were there and ii) had the means (e.g. an MDX querying tool where they can write their own MDX) but for an 'avaerage' user, they'll never know that they're there. making the source measures hidden doesn't preclude their use in subsequent calculated measures in the cube. But this *will* make the measures in question hidden for *all* users.

    Perhaps an even a better approach is to try using perspectives. Again, this lets you hide measures and calc measures, but again isn't a security measure (they still exist, just not visible to users accessing via that perspective). The advantage here is that you can have a perspective that *does* show some or all, and one or more perspectives that show some subset of the members.

    HTH,

    Steve.

  • April 24, 2008 at 3:18 pm

    #806916

    Steve,

    Thanks for the reply. Unfortunately, I can't hide the measures from *all* users, so I don't think altering the visibility would work. I thought about using perspectives, but the measures in question have to be totally restricted from certain users (can't all users see all the perspectives? - or can that be restricted?)

    For example, say there are three measures - [GrossBookings], [GrossCost], and [NetRev] (which is calculated as GrossBookings - GrossCost). Role Main should see only GrossBookings, and Role Super should see all three measures.

    I can hide [GrossCost] from Role Main without a problem, but once I add [NetRev] to the cube, I get the error I mentioned. I am assuming it is because the calculated measure is not explicitly hidden, and it can't have a valid value without the underlying [GrossCost].

    So, how do I restrict access to [NetRev]?

    Thanks,

    Marianne

  • May 5, 2008 at 3:02 pm

    #810939

    Well, if anyone runs into the same issue, here is how I resolved it:

    - In the DSV, I created a named calculation [NetRev], and set the value = 0.

    - In the User Main Role, I removed access to [NetRev]

    - In the calculations tab on the cube designer, I created a named set ErrorNetRev as this:

    iif(iserror(strtomember("Measures.[GrossCost]")), {},{Measures.[NetRev]})

    - Then I assigned the value like this:

    SCOPE(ErrorNetRev);

    this = [GrossBookings] - [GrossCost];

    END SCOPE;

    Hopefully that makes sense.

    -Marianne

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply