0

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?

MattyKluch
  • 11
  • 1
  • 10

1 Answers1

0

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.

userfl89
  • 4,610
  • 1
  • 9
  • 17
  • Interesting. This might be what I was looking for. I'm not familiar with writing DAX filters so do you have any more info on how to write or apply them? Thanks – MattyKluch Aug 23 '18 at 16:59
  • The first link covers row level security and second one is the Microsoft reference for roles, which has a good section on row filters. The first article uses a lookup table and you can use do this or implement it in another way. For DAX I'd recommend http://sqlbi.com, both the articles on there and their books are great resources. https://learn.microsoft.com/en-us/sql/analysis-services/supplemental-lesson-implement-dynamic-security-by-using-row-filters?view=sql-server-2017 https://learn.microsoft.com/en-us/sql/analysis-services/tabular-models/roles-ssas-tabular?view=sql-server-2017 – userfl89 Aug 23 '18 at 18:24