Feeds:
Posts
Comments

Archive for August, 2018

I have got a business requirement to only allow users in the same department to see the data that belong to their department. To do that in the SSAS tabular model 2016, you would need to create a table in SQL database then bring this table into the model. The table will contain the user’s first name, last name, login id, department name and/or employee id. The login id consists of the domain name and username. Once done, you would need to create a new role in the model. Go to the Model menu then select Roles to bring up the Role Manager. In the Members tab within the Role Manager, add users into the role. In the Row Filters tab, set the DAX filter for the fact table(s).

An example of the DAX filter is shown below that restricts users to see the data in the Issue Reports fact table based on which health service they belong to. The Users table contains a list of the users with their health service.

=‘Issue Reports’[Health Service]=LOOKUPVALUE(‘Users’[HealthService],

‘Users’[LoginId], USERNAME(),

‘Users’[HealthService],

‘Issue Reports’[Health Service])

To test:

Click on the “Analyze in Excel” from the SSDT then choose the role you have just created or get the user to test it.

To test on SSMS, browse the Cube and then click on the Change User icon.

test role in ssms

Reference:

https://docs.microsoft.com/en-us/sql/analysis-services/supplemental-lesson-implement-dynamic-security-by-using-row-filters?view=sql-server-2017

 

UPDATE:

Is that possible to use the AD groups to perform the row level security in the tabular model?

 Currently my DAX filter is below:

=’Issue Reports'[Health Service]=LOOKUPVALUE(‘Users'[HealthService],

‘Users'[LoginId], USERNAME(),

‘Users'[HealthService],

‘Issue Reports'[Health Service])

It does a lookup based on the LoginId in the Users table and the user’s username returned from the USERNAME(). If it matches, then returns with the health service the user belongs to and then returns with the issue reports for that health service.

 I googled if there is a IS_MEMBER function in DAX or something similar and I haven’t found any solutions.

The workaround is to create two roles and then do a dax filter on the objects.

We created two roles in the model i.e. one for East Metro and another for South Metro and then do the dax filter on the facts and/or dimensions.

We only have 2 dimensions that have health service so we will use bi-directional row level security on one of the dimensions and use dax filter on the other dimension.

Advertisements

Read Full Post »

The solution is to set the ConsumeContainerWhitespace property of the report to true.

Read Full Post »

When the subreport is run on its own, it does not generate any blank page, however, when it is placed on another report, it generates a blank page. To solve this, make sure the subreport has the same size as the original report (page size) for the subreport.

 

Read Full Post »

%d bloggers like this: