SSAS advanced dimension level security using SSRS reports, showing parent of a level you have no access

By | October 23, 2009

I was working with a client where i had some difficult security questions, that i had to solve without having to resort to advanced MDX query’s. The client has to be able to create reports without MDX knowledge. Take the following example from the AdventureWorks cube:

I want to give users a report that show all sales by state province of the country they are in, e.g. you are a sales person belonging to postal code 91801 and you want a report showing all the state-province’s of the United States since that is the country you belong to.
As seen in the dimension below:

all level

But by default users are forbidden to see the sales of another state province when their postal code isn’t in it. When you place security on a country by using a role, autoexists take care of the security for the rest of the levels resulting in only the parents of the selected postal codes will be shown to the user with the role defined.

salesterr

This will result in not being able to show the parent levels your postal code does not belong to, as seen below while browsing the report using the role:

withsec

When you create a report based on this dimension you get the following report (don’t mind the formatting)

rap1

The key to solving this problem is using two dimensions with the same levels. One with the security enabled by using a role and one dimension (you can just copy the original dimension) without security, you could remove the postal code level in this dimension to make sure the security is intact.

When you create your report you use levels from both dimensions, and this is where autoexists is your friend. Since you want to show only city’s from a sales persons own country you use the the country level from the secured dimension (only his own country is returned by the secured dimension) and the state province level from the security-less-dimension, autoexists takes care of showing only those state provinces of the country the sales person belongs to.

Resulting in the following desired report:

rapok

All in all reasonably simple when you understand autoexists.

  • Couldn’t you have just checked the Visual Totals checkbox on the advanced tab? I don’t think you would have to use a separate dimension for security in this scenario, but I could be wrong.

  • Kasper de Jonge

    @Greg Galloway Hi Greg, thx for replying.
    Visual totals is not an option in this scenario, what visual totals does is make sure when you sum a parent level of the secured level it shows the sum and of only the levels you have access to. As you can read here: http://www.sqlserveranalysisservices.com/OLAPPapers/DynamicVisualTotals.htm

    I wish it was that simple 🙂

  • I think you’ve got it backwards (unless I’m having a senile moment). Visual totals means the totals are made up of only what you can see. So checking that box (not the default) means the totals obey the security you setup. Try it.

  • Kasper de Jonge

    @Greg Galloway
    hi Greg, you’re absolutely right. I did try it but didn’t even look at the numbers. But it still doesn’t do what i want in my scenario, i want to report over dimension levels i don’t have access to thanks to autoexists, visual totals doesn’t change this.

  • How about providing an advanced, MDX based calculation where you deny access to descendants of states which are not ancestor of certain postal codes, but are in the same country?

    Additionally, you can deny access to other countries on the grain of your choice, using one more condition.

  • Kasper de Jonge

    @Tomislav Piasevoli
    Hello Tomislav, Thank you for your reply.

    I’m sure you could attain the same result with the use of MDX. But is see a few major problems with this:
    – You have to remove the role with the security to let user access all the levels leaving the cube all open
    – Using roles with different postal code per user let the report automatically show the right data, no selection necessary
    – I have to create multiple, different, reports using this security, so i have to create MDX for every report. My client is no MDX expert and they want to do it themselves

    And i understand that creating two almost identical dimensions isn’t very pretty but it’s still the most report builder friendly solution.

  • I agreee this might be the easy workaround in SSRS based solution. Alternatively, you can split the dimension into 2 and apply security on one of them.

    However, both way don’t work nicely if you allow end user to use Excel 2007 to browse the cube. The “duplicate dimension way” cannot secure the data at all. The “split into 2 dimension way” works, but you have to see 2 dimension tables in field panel and you cannot build hierarchy on top of it.

    MDX solution is best option thought it is the most complex one.

  • Kasper de Jonge

    @Haojie Qiao
    Hi Haojie, yes when you want to enable user to analyse the cube themselves this is not a viable option. But MDX won’t serve you in Excel 2007 either. But i agree MDX is the neatest solution, but also the most difficult.

  • Cube

    Cube :
    HI,
    this article 100% apt for my requirement, but when i am using that cube data in my ssrs environment i didn’t get filtered, it gives me complete data so can u please guide me on this

  • Kasper de Jonge

    @Cube
    Hi, I think you mean the same as on this SSRS forum:
    http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/bc3e411c-6d9f-47c1-8991-0b6ae66407e7 let me know if you mean something else ?