Wednesday 24 March 2010

SSAS - Scoping on restricted members

Due to a miscommunication in the requirements gathering process I recently had to apply a temporary fix to overwrite values in my cube like so:


SCOPE([MEASURES].[Revenue KPI]);
  SCOPE([Product].[Product].&[11700]);
    THIS = [Measures].[P and L];
  END SCOPE;
END SCOPE;

In this particular cube we have security roles set up that restrict on the [Product].[Product] hierarchy. Users that are members of roles that restrict them from seeing [Product].[Product].&[11700] were complaining about error messages like the one displayed below.








The problem is that we're trying to scope on a member that that user is not allowed to query. So how do we get around that? I pinged a message to Chris Webb and he replied that he had already blogged about this.


The solution is shown here and basically works around the issue by creating a named set that is empty if the user is not allowed to view the member:





CREATE HIDDEN SET [BFSelect] AS
  iif(iserror(strtomember("[Product].[Product].&[11700]")),
  {},
  {[Product].[Product].&[11700]}
  );

SCOPE([MEASURES].[Revenue KPI]);
  SCOPE([BFSelect]);
    THIS = [Measures].[P and L];
  END SCOPE;
END SCOPE;

Problem solved, thanks to Chris.

No comments:

Post a Comment