Filtering data by user

Question

Filtering data by user

lucagent

Valued Member

Points: 65
More actions
September 22, 2008 at 3:59 am

#191209

Hi all,
I'm quite new to datawarehousing and Analysis Services so please forgive me if my question is banal.
I must allow users to access only a subset of data depending on the department they belong to.
In the source DB (the DB that feeds my DW) this is achieved through a series of tables matching people to departments, is this the "correct" way to obtain the same security policy in SSAS or does a better way exist?
Thx in advance
Luca

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You have a couple of options. One is to add a dimension and hierarchy that has the NT User name of each of your users associated with the data they can access. This can be in a many-to-many relationship. You can then filter by UserName using this dimension to limit the data users can see. Doing this will prevent data caching, so you do take a performance hit.

The second option would be to create roles in your cube for each set of data that is available and assign the users to the proper roles. This could either be done manually or by writing a bit of code (I have done it in a script task in an SSIS package) to generate the roles as a series of XMLA scripts and apply them automatically to the cube.

lucagent Valued Member Points: 65 More actions · Answer 2

I had already thought to the first solution and I think it would fit for my case, it's nice to have a cconfirmation of my hypothesis.

Many thanks Michael,

Luca

lucagent Valued Member Points: 65 More actions · Answer 3

I have a further question about data visibilty:

I have to show to each user only a subset of data depending both on the department and the kind of employees, that is user A should see all data referred to dept 1, user B only those data of dept 1 which refer to employees but not to managers, user c should see only data referrerd to managers of dept 1 and 2 and so on.

The specific question is:

is it better to have only one "security dimension" which carries both criteria (dept # and employee type) which requires many rows per username in case of users allowed to see all data (cartesian product : employee types X depts) or ti have two distinct dimensions storing security data about "depts" and "emp types"?

Thanks for any help

Luca