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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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:
IF (
CUSTOMDATA () = "",
FALSE (),
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.