It has been quite a while since my last actual blog post. I do hope that this will help people to use a simple security model, which is easy to implement and maintain. The idea is further down the line to have an application sitting above the security table, so that the right people can add and remove access without our intervention.

What we are going to explain below is to use Dynamic SSAS Role Security.

But we are also going to achieve the following:

  • You will also be able to Control that the users have access to the cube.
    • This will enable you to only define one role, and put in the AD Group once with everyone in this AD group.
  • You will be able to Control which user has access to which Dimensional Data
    • In this example User A will only be able to see information from the Countries USA and Australia

Example:

  • We had a requirement where we needed to simplify our SSAS Role Security.
    • It was sitting at already almost 60 SSAS Roles and it was only going to grow.
    • Along with this, there was performance related issues with so many roles.
    • Not to mention the admin headache of adding and removing users in multiple AD Groups, as well as having to find out which AD group in a particular role the user must be added to.
  • For our working example below, I am going to create a really simple scenario.
  • We have got Tennis Rankings.
    • We want DOMAIN\GilbQue to only be able to see data for the Countries from the USA and Australia.
    • We also want DOMAIN\GilbQue to be able to view this data in our cube called “Tennis Rankings“.
    • Then we will change DOMAIN\GilbQue who will also need to see the data for the same countries.
      • But DOMAIN\GilbQue must see data for another Cube called “Swimming Rankings“
      • NOTE: We are not going to create another cube called “Swimming Rankings“. It is to show how the security will work when a user tries to access a cube to which they have potentially access to the Country Dimension table, but not to the actual cube.
  • With the above example it will allow us to have one Security Table in which we can control which users have access to which Cubes.
    • As well as if they can access the cube, to which Country information they can view.

Loading Data and Relationships

The first step is to load the data, and create the relationships

  1. The following tables below are our Dimension Tables.
    1. Country
    2. Cube Name
  2. The following table below is our Fact Table
    1. Tennis Rankings
  3. Then finally this table contains all our Security Information table.
    1. Security Table.
      2. NOTE: In the above picture you would need to change it to your Domain Login details.
  4. Then once we have created the relationships it will look like the following:
    2. We created the following
      relationships
      2. In the first
        column we created a relationship from our Fact Table “Tennis Rankings” and the column called “Country” to the same column name “Country” in our Dimension Called “Country”
      3. In the second column we created a relationship from our Fact Table “Tennis Rankings” and the column called “CubeName” to the same column name “CubeName” in our Dimension Called “Cube Name“
      4. In the third column we created a relationship from our Security Table “Security Table” and the column called “Country” to the same column name “Country” in our Dimension Called “Country“
        1. NOTE: The reason for this is so that next when we create our Role and assign the dynamic Security that there is the required relationship.
      5. In the fourth column we created a relationship from our Security Table “Security Table” and the column called “CubeName” to the same column name “CubeName” in our Dimension Called “Cube Name“
        1. NOTE: The reason for this is so that next when we create our Role and assign the dynamic Security that there is the required relationship.
  5. NOTE: When creating the relationships you MUST ensure that your dimension tables have all the related information in your Dimension Table.
    1. The reason for this is if your dimension table does not have a related value, then the Dynamic Role Security will not be able to Filter it out.
    2. For Example, in the above in our Country table we had no value for Serbia & Montenegro
    3. When I first tested my security below I was getting a blank row with some values.
    4. Once I then added Serbia & Montenegro I could only see my required or related information.

Creating the Role and creating the Dynamic Security

In the steps below we will now create our Role and configure the Dynamic Security

  1. In your Tabular Model, click on the Roles
  2. When the Role Manager Window opens, click On New
  3. We then configured it with the following as shown below:
  4. Now in the steps below is where we configure the Dynamic Security as explained below:
    1. Due to the reason that we want to ensure that both the Country and Cube Name are used to grant people the correct access to the cube, we are going to configure the security to both tables below.
    2. First is Country, where we will be adding the following DAX function:

      =Country[Country]=LOOKUPVALUE(‘Security Table'[Country],

      ‘Security Table'[DomainLogin], USERNAME(),

      ‘Security Table'[Country], Country[Country])

      1. To explain what we are doing above is the following:
        1. We are specifying that we are using the column name called “Country” from our “Country” Table in RED
        2. Next we are using the lookup function to then lookup in our “Security Table” the related “Country” Column. In GREEN
        3. Then it validates if in our “Security Table” the column called “DomainLogin” has a corresponding username, which is passed when we log in. in PURPLE
        4. And finally it then verified that the “Country” data in both tables matches. In ORANGE
    3. Then for our Security Table we want to set the Permissions to False.
      1. =FALSE()
    4. Then we go and once again do the same as step 4b above, but this time it is for our Cube Name table.

      =’Cube Name'[CubeName]=LOOKUPVALUE(‘Security Table'[CubeName],

      ‘Security Table'[DomainLogin], USERNAME(),

      ‘Security Table'[CubeName], ‘Cube Name'[CubeName])

      1. To explain what we are doing above is the following:
        1. We are specifying that we are using the column name called “CubeName” from our “Cube Name” Table in RED
        2. Next we are using the lookup function to then lookup in our “Security Table” the related “CubeName” Column. In GREEN
        3. Then it validates if in our “Security Table” the column called “DomainLogin” has a corresponding username, which is passed when we log in. in PURPLE
        4. And finally it then verified that the “Cube Name” data in both tables matches. In ORANGE
  5. So once it is completed it will look like the following:
  6. The final step is to click on the Members and ensure that you add an AD Group in which all your members will be a part of it.
    1. Click Add
    2. I then clicked on Advanced and then on Object Types I changed it to Groups Only
    3. I then selected Domain Users as shown below
  7. Then clicked Ok and then Ok again.

Testing the Dynamic SSAS Tabular Security

In the steps below we are going to test and see if this dynamic security now works.

  1. From within our model we click on the Analyze with Excel Button
  2. Then on the Analyze in Excel screen we select the Role we just configured
  3. Then click
    Ok.
  4. Now based on our Security Table configuration that we did above, I should be able to see data only from USA and Australia
    1. Here is the Security Table as it currently is
    2. Now when I opened
      Excel and dragged in Total Points and Country I saw the following:
    3. And the Pivot Table has the following:
      2. Which is what we wanted to see.
    4. Now if I had to go and change the above table and change the CubeName
      values to “Swimming Rankings“, what we would expect to see is no values from our cube.
      1. Due to the fact that the user does not have access to see the cubes. Which is related and contained in our Fact Table and Security Table.
      2. So our Security Table now looks like the following:
    5. Now when we browse the cube as we did in step 2 above we see the following in Excel
    6. And the Pivot Table has the following:
    7. Which is showing us what we wanted to see.
  5. The only thing to Note, is that if you give the user access to the cube name on either one or both of the rows in the security table the user will see all their related information.
    1. EG: You only gave the user access for USA, when they view the cube they would see both USA and Australia
    2. If you only wanted the user to see USA, then remove the line with Australia