BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Security in SSAS Tabular - creating and managing roles
- Creating a permissions table
- Using CustomData to make row filters dynamic (this blog)
- Controlling what a user sees using their logon name
- Assigning members to roles
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 26 February 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using CustomData to make row filters dynamic
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.
Creating the security role
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:
IF (
CUSTOMDATA () = "",
FALSE (),
Region[RegionName] = CUSTOMDATA ()
)
Setting the custom data flag in the connection string
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).
- Security in SSAS Tabular - creating and managing roles
- Creating a permissions table
- Using CustomData to make row filters dynamic (this blog)
- Controlling what a user sees using their logon name
- Assigning members to roles