We have a rather large ssas based tabular cube which our organization uses and I managed/administer. Users can connect to it via an odbc connection in an excel sheet to pull fields and create reports by using a pivot table. Management wants to streamline the fields though so that certain users can only see certain fields. I know I can use perspectives to do this however all this does is allow the users to select a perspective to view and subsequently it'll show them whatever fields/tables I have set that perspective to show. What I'm trying to do is limit each users access so that each user has limited access to what perspective they can view. For example, a new employee would have basic access, someone who's been with the company a long time would have be considered a power user and would have a perspective with more access. Is there any way using either perspectives or roles in ssas to limit their access when they connect via an odbc connection? Maybe through active directory?
1 Answers
Have you considered row level security? You could create roles corresponding to each level of access needed and implement filters on rows/tables using DAX. For example, create a New Employee Role with Read permission. In the Role Manager window (Model > Roles...) add filters on which ever tables you want to restrict for that role, a basic example would be =FALSE() as the DAX Filter to restrict the entire table from that role. Of course you can also add further granularity at the column and row levels. Just keep in mind that any tables which are restricted will have the security filter propagated in the one-to-many relationship direction to tables in which it shares a relationship with, i.e. a fact table will be filtered by a restricted dimension as it's on the many side of the one-to-many relationship.
