In this post I will demonstrate how to add row level security in your tabular model. This can be a useful feature if you want to restrict access to different dimension members for different users.
First of all we need to create a new windows user on your PC / server. Lets call this user “TestUser2”
Then I will create a new table with user names and which customers they should be able of viewing.
use [AdventureWorksDW2012] create table [UserAccess] ( FullDomainUserName varchar(200), DomainUser varchar (100), CustomerKey int ) insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11511) insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11512)
Open the Tabular model project in Visual Studio Data Tools.
Lets add this table to the model and then create a relationship to Customer and column CustomerKey. Click “Hide from client tools” on the UserAccess table.
After you have done this your model will look like this
Add a new role (Model -> Roles) called TestRole. Give this read permissions.
Then add this DAX to the DAX filter on the Customer table
='Customer'[CustomerKey]=LOOKUPVALUE('UserAccess'[CustomerKey], 'UserAccess'[FullDomainUserName], USERNAME(), 'UserAccess'[CustomerKey], 'Customer'[CustomerKey])
Add your new Windows users to members
Deploy your model.
Open a Excel pivot. When Connection to the SSAS server impersonate the TestUser2 user. After doing that your pivot will look like this.