I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company.
The Company dimension contains a list of companies, of which there are several hundred in the cube. Our security model requires that users have visibility only to those Company values to which they have been assigned.
In our SQL Server environment, we control this access through a series of functions that are JOINed in VIEWs. The functions return a table listing all the Company values, which is used to filter results.
For instance:
CREATE VIEW dbo.FakeView
AS
SELECT d.*
FROM FunctionToGetAccess(SYSTEM_USER) f
INNER JOIN DataTable d
ON d.CompanyID = f.CompanyID
Is there a way for me to port this logic, or a similar logic, to dimension-level security in SSAS?
A couple of notes:
- The Cube will be rebuilt overnight
- Security does not have to be current hourly, but should be reprocessed during the cube processing overnight
- I would strongly prefer not to have to materialize the list of users and allowed clients into a table or dimension
Best Answer
You need to overcome your scruples and materialize the list of users and allowed clients into a dimension, then apply security to that dimension. If you have Enterprise Edition you can hide the dimension from the users.