560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to use security roles to restrict access to your SSAS Tabular model
Part three of a five-part series of blogs
Perspectives in Analysis Services let you show different parts of your tabular model to different people, but they don't have any security. To ensure that the right eyes see the right bits of your model, you'll need to create and manage security roles, as explained in this blog.
DAX contains a function called CustomData, the sole purpose of which seems to be to make a connection string dynamic. The example below shows how to create a role which can be configurable to show the data for any MAM region.
To create a dynamic row filter to solve the problem above, first create a role as follows:
If the custom data flag is not passed in, this will return False; otherwise, it will return True only if the region for the current row equals the one passed in via the connection string.
If you're wanting to copy this formula, here it is:
CUSTOMDATA () = "",
Region[RegionName] = CUSTOMDATA ()
If you now create a pivot table using this role, you'll see no data:
If you use this role when viewing a pivot table based upon your model, you'll see nothing!
This is because the custom data flag hasn't been set, and so the row filter formula for the region always returns False. To get round this, amend the connection string:
One way to edit the connection string used is to choose this ribbon option.
Choose to show the Definition tab in the dialog box which appears:
Click on this tab first.
Add a value for the CustomData flag:
You don't have to make this the last attribute, but it's easiest to read if you do.
When you select OK, your pivot table will show data for the London region:
For transactions occurring in the London region, the row filter formula now returns True.
I suspect that this is only useful if you have a client application in which it's easy to change the connection string (an example might be Reporting Services).
|Parts of this blog|
25 Aytoun Street