SSAS Dynamic security for Dimension Content

Say we have a dimension called as – Territory (e.g. America, Australia, China). We want to set up security in cube so Users from America can see only their own data and Users from China can see only China’s data.

In brief overview we need to build two dimension tables, one for our users and territories, then build a bridge fact table with user and territory; create a role, add members(users) and then modify the dimension data for your territory dimension with the following code:

exists([Dim Territory].[Territory].[Territory].members, (STRTOMEMBER(“[Fact Bridge User Territory Mapping].[User Name].[“+ Right(UserName(), Len(UserName()) – Instr(UserName(), “\”))+ “]”)), ‘Fact Bridge User Territory Mapping’)

Note: in this example user name is stored with out domain name. Since domain name is not included so that’s why we have this – Instr(UserName(), “\”)).

Now as regards Totals, you need to understand your requirement.

You will not believe but in one scenario our requirement need to show in Grand Total, the total in entirety, including members you don’t have access too.  In this case you don’t need to do any thing.

But in usual scenario, you will need to show the Grand Totals for only the members you have access to.  This can be done by checking “Enable Visual Totals


MSDN Reference

Other 1



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s