I have a cube in SSAS 2012 with about 30 dimensions, one of which is
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
VIEWs. The functions return a table listing all the
Company values, which is used to filter results.
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