Search code examples
ssasolapcube

Dimension security in SSAS

I have an SSAS cube with sales data. One dimension is called Department and looks like this: Level 1:Department

Level 2:Cost center

Another dimension looks like this: Level 1: Client

Level 2: AccountNo

Level 3: Invoice

Some users should only be able to see a specific cost center. This I have solved with creating a Role and limited access to dimension data for the dimension Department. This works well so far.

Now my manager has said that the users with limited access should not be allowed to see the names of the clients that has not done business with the limited users' cost center.

So I need to limit access to the dimension Client. But this can't be done very easily, since there are thousands of customers and a customer can one day from another do business with a new cost center. A customer may also have several accounts which belongs to different cost centers.

I need some input on my options here. Would some kind of subcube be an option, or a new cube (sounds a bit over the top)? Another option would be to create another hierarchy in the Client dimension which uses cost center, account and invoice. This would have to eliminate the customer level since a customer can have several accounts belonging to different cost centers.

Any input welcome, I don't need exact solutions, but guidance...

Solution

  • The good news is that you can do this in Dimension Security.

    You must have set up a Role for these users. In the Dimension Data tab of the Role in VS, there's an Advanced tab. Here you can enter MDX that returns a set of dimension members the users can see.

    There are quite a few annoying gotchas with dimension security:

    1. Make sure you set security in the cube dimensions, not the database dimensions (they'll both be listed in the drop down at the top, so it's very easy to select the wrong instance).
    2. In your MDX expression, only use attribute hierarchies, not "user" hierarchies. Otherwise you get weird errors.

    Your "Allowed member set" expression for the Clients dimension should be something like this:

    EXISTS([Client].[An attribute hierarchy].Members,
{A set of cost centers the user is allowed to see},
"Name of a measure group that will have a non-NULL value if the client has 
 had dealings with the cost centre")

    As an example, the expression I came up with to check this on the cube I'm working on (restricting Customers to those who bought a particular brand) was:

    EXISTS([Customer].[Customer Key].Members,[Product].[Brand].&[MH],"OrderItems")