Neudesic Blogs

Passion for Innovation

BISM Tabular Security

I have been focused on the security options within SSAS Tabular models lately and I have found a few ways to approach it. There is straight forward AD roles tied to a specific link to the data, but this is not very dynamic in nature and all of these roles must be hard coded to a specific row level item such as region or division. In the dynamic variety two are fairly well documented and the third is my own hybrid of the two solutions, which I will go in to detail to explain. Before I get into these I will detail out some of the general concepts.

First and foremost there is roles based security within the tabular models, but don't confuse that with dynamic security using AD roles. There is a bit of a disconnect amongst these two things when trying to create dynamic security roles. When using straight hard coded roles such as and AD group tied to a specific division this works like a charm. Most folks do not want to maintain this type of structure when you grow past just a handful of rows to maintain though. For the most basic types of security go through the lesson 12 in the Tabular Modeling segment of the Adventure Works Tutorial.

 

This leads us to a few more dynamic ways of doing this and the main issue you run into here is that to do so you must tie the row-level data back to a user by using the USERNAME() function within DAX.  This forces your role level permissions to be managed at the user level.  You can still tie specific roles to have membership to the role you are defining by allowing AD Roles in the membership, but when you start tying the data back dynamically it is forced down to the user.

 

First of the dynamic options is the method that MS shows you to use in the MSDN supplemental lesson Implement Dynamic Security by Using Row Filters.  In this lesson it details how you would tie a user in your AD to a single record type in the dimension you are working with.  The first thing I did was figure out a way to tie this to more then one table and also more then one type of record in each table.  So to go into more detail you can tie a user to not only one territory, but many by adding more records into the same table with additional 'Employee Security'[Sales Territory Id]'s, which doesn't even require a different DAX statement as they showed in there. That statement is…

 

='Sales Territory'[Sales Territory Id]=

LOOKUPVALUE('Employee Security'[Sales Territory Id],

   'Employee Security'[Login Id],

   USERNAME(),

   'Employee Security'[Sales Territory Id],

   'Sales Territory'[Sales Territory Id])

 

Essentially this DAX statement is returning a Boolean value of True or False.  So the first item shown is the 'Sales Territory'[Sales Territory Id] that is basically calling our which column in the 'Sales Territory' table this is going to be checking against on a per record basis.  Then there is an equal sign to kick get to the latter part of the logical test, which in this case is looking up what the 'Employee Security'[Sales Territory Id] is that is defined in the 'Employee Security' table.  The last 4 values actually consist of two paired values that do the lookup checks on the 'Employee Security' table and check the value against the values you would like to match it to.  Starting with the first one it looks up the 'Employee Security'[Login Id] to match it with the logged in user within AD using the USERNAME() function.  The second checks to make sure the row within the 'Employee Security' table matches the records within the 'Sales Territory' table.  When using this it is not tied to a single record within the 'Employee Security' table and will allow you to have a single employee listed more then one time with more then one [Sales Territory Id].  Thus allowing different territories and a duplicated user within different records in that table to be referenced with this same statement, cause often times I can see the need for more then one territory to be allowed for various users that can be at the regional management level where they may manage multiple territories.

 

I also thought that this can become even more complicated when you also tie it to some other table as well, especially to do data management on this table.  So by adding another column to the 'Employee Security' table you can also add in security on the 'Product Category' table as well.  So I added another column with the name [Product Category Id] that will tie back to the column 'Product Category'[Product Category Id].  This will be used in a second DAX statement within the same role, although this will sit the Row Filter for 'Product Category' table.

 

='Product Category'[Product Category Id]=

LOOKUPVALUE('Employee Security'[Product Category Id],

   'Employee Security'[Login Id],

   USERNAME(),

   'Employee Security'[Product Category Id],

   'Product Category'[Product Category Id])

 

When I looked at all of this and also thought about managing the data within the 'Employee Security' I started to think there must be a better way to handle this.  So I used a technique that will allow you to make the table more generic and then use key name and key pairs tied to users' [Login Id] field.  Instead of the table that was recommended within the lesson on MSDN above, you can user the following structure instead.

  

CREATE TABLE [dbo].[DimSecurity](

[LoginID] [nchar](50) NULL,

[KeyName] [nchar](20) NULL,

[Key] [tinyint] NULL

) ON [PRIMARY]

 

GO

 

Fill in the table with values that tie to the security you created above.  This would mean you fill in the first column with the 'Employee Security'[Login Id] and the third would use either 'Employee Security'[Sales Territory Id] or  'Employee Security'[Product Category Id]. The new column in the middle is the trick here that allows you to use this generic table approach and this would require a text field based on the name of the key you are tying it back to.  In the cases here it would be "Sales Territory Id" and "Product Category Id" respectively.

 

LoginId

KeyName

Key

domain\user1

Sales Territory Id

1

domain\user1

Sales Territory Id

2

domain\user1

Product Category Id

1

domain\user1

Product Category Id

2

domain\user1

Product Category Id

3

domain\user1

Product Category Id

4

domain\user2

Sales Territory Id

1

domain\user2

Sales Territory Id

2

domain\user2

Sales Territory Id

3

domain\user2

Sales Territory Id

4

domain\user2

Sales Territory Id

5

domain\user2

Product Category Id

1

 

 

You would use the trio of values to tie back the different Row Filters by creating the following statements within the new Role.  First let's start off with the Sales Territory Row Filter by adding the following DAX statement to that filter first.  The difference here is that you now also use the 'DimSecurity'[KeyName] paired with "Sales Territory Id" in the LOOKUPVALUE function to pair the proper valued pairs from the 'DimSecurity' table first. 

 

='Sales Territory'[Sales Territory Id]=

LOOKUPVALUE('DimSecurity'[Key],

            'DimSecurity'[LoginID],

            USERNAME(),

            'DimSecurity'[KeyName],

            "Sales Territory Id",

            'DimSecurity'[Key],

            'Sales Territory'[Sales Territory Id])

 

You will also need to do the same on the Product Category Row Filter as well to get the same results as we got in the previous example.

 

='Product Category'[Product Category Id]=

LOOKUPVALUE('DimSecurity'[Key],

            'DimSecurity'[LoginID],

            USERNAME(),

            'DimSecurity'[KeyName],

            "Product Category Id",

            'DimSecurity'[Key],

            'Product Category'[Product Category Id])

 

Now that you have both in you can go and test it out using the "Analyze in Excel" option.  I think the generic table is a bit more flexible then the option provided in the MSDN post mentioned, mostly due to the ability to manage the data in the security table much easier then if you had both [Sales Territory Id] and [Product Category Id] columns in that same dataset all tied to one user, because in this model you would still have to also add multiple records for each user to tie out every combination of the allowed rows in that table.

 

So this also leads me to a post by Teo Lachev that details out how you can use a factless fact bridge table to do something very similar to the approach I have come up with.  Not wanting to steal anything away from Teo go ahead and read that post here.

 

I don't feel any option here is perfect and will fix all your needs, but one of these options listed should get you to where you need to be. 

Posted: May 10 2012, 09:09 by Tom.Marek | Comments (0) RSS comment feed

Tags: , , , , ,
Categories: Business Intelligence

Tags

Categories

Archive

Blogroll

Neudesic Social Media

Follow Neudesic on Twitter Follow Neudesic on Facebook Neudesic Neudesic