Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.