Managing complex security in SSAS Tabular – Yeah Nah edition

A couple of times recently I have come up against requirements which have required some fairly complex logic to apply security. One involved some fairly gnarly relationships coming from multiple directions, the other involved grinding through Hierarchies from parent nodes down to permitted viewable children.

The problem with both cases is that though the logic can sometimes be written (albeit usually in an ugly as hell manner) – the functions needed to do so perform atrociously. For complex relationships you are obligated to take in context after context, changing filters and doing all sorts of DAX voodoo. As we know by now, avoiding relationships is good for performance. Hierarchies can be managed through the PATH function, but it’s a text operation that is far from speedy.

Let’s give a quick example of some complex security – consider the below data model:

Complex Model
Complex Model

Here the security controls who can see what has been spent on a Task in the FactTable object. How can see what depends on their Role and/or the Unit they are in. There is also a 1:many relationship between a person and the login they can use.

So for dynamic security you need to navigate from the User Id to the Person and assess what Unit they are in for the Unit based permissions. You also need to assess what Role they are in to get the Role based permissions.

I took one look at this and shuddered at the messy DAX I was going to have to write, plus how terribly it would perform.

Do it in the Cube? Yeah Nah.

So I thought “Yeah nah” and decided the cube was the wrong place to be doing this. Ultimately all I was trying to get towards was to pair a given login with a set of tasks that login would have permissions against. This is something that could easily be pushed back into the ETL layer. The logic to work it out would still be complex, but at the point of data consumption – the bit that really matters – there would be only minimal thinking by the cube engine.

So my solution enforces security through a role scanning a two column table which contains all valid pairings of login and permitted tasks to view. Very fast to execute when browsing data and a lot easier to code for. The hard work is done in loading that table, but the cube application of security is fast and easy to follow. The hierarchy equivalent is a pairing of User Id with all the nodes in the Hierarchy that are permitted to be seen.

As a final note, for those non-Aussie readers the expression “Yeah nah” is a colloquialism that implies that the speaker can’t be bothered with the option in front of them. For example: “Do you want a pie from the Servo, Dave?” “Yeah nah.”

Read More

Dynamic Parent Child Security using DAX

I was recently posed a challenge to implement the following requirements in a Tabular model:

  1. Secure a Parent Child dimension at a Parent level, allowing all children to be visible
  2. Allow multiple Parents to be securable to a given user
  3. Keep it as easy to administer as possible

The first 2 requirements are technical and internal to the tabular model, but the last implied inherently that use of Roles and AD Groups was out as this is not a very user friendly approach.

Note that in this exercise I will be using the DimEmployee Dimension from the AdventureWorks 2012 DW sample database.

Row Level  Security in Tabular Models

Tabular Models apply security is applied at the row level by the application of row filters that restrict what a given Role can see. You can make the effect of a Roles dynamic through the use of  lookup tables and the USERNAME() function. There is a detailed white paper on securing Tabular Models on MSDN: Securing the Tabular BI Semantic Model

In a non-parent child situation this is simple to manage as it is easy to establish a relationship between viewable members (i.e. rows) and users via a bridging table – the paper mentioned above has a clear example of this.

How Parent Child Dimensions complicate things

Parent Child Dimensions add complications because there isn’t a straightforward relationship to manage. You can secure the parent by filtering on the parent’s unique key – as below – but any child row, while aware of the relationship to the  immediate parent via the ParentEmployeeKey cannot be filtered on the same column.

DimEmployee in AdventureWorks
DimEmployee in AdventureWorks

A consequence of this is that if you want to secure using row filters, you would need a table that secures each parent and child explicitly for each user. This is obviously not easy to administer and if the hierarchy changes you need to rebuild your security all over again.

How can I make Security Dynamic?

DAX, of course! This is a variation on the scenario described in the white paper – except in this case complicated because the table to be secured contains no data that can be used to apply security directly.

How do I apply this to a Parent-Child Dimension?

Fortunately there are ways to make rows aware of the hierarchy that applies to them. Of particular relevance in our case are the PATH and PATHCONTAINS function.

The PATH function returns a text delimited list of hierarchy members from the current member to the topmost parent. The PATHCONTAINS function scans that list for a specific member and returns a boolean TRUE/FALSE result as to whether that specific member is there. So if we have the following functions in our model:



We get a set of results like this:

Parent Child Dimension with PATH and PATHCONTAINS DAX functions
Parent Child Dimension with PATH and PATHCONTAINS DAX functions

So the PATH for Employee Key 12 is “112 | 23 | 189 | 12″ – so key 12 is the leaf, and the three preceding members in the hierarchy. The PATHCONTAINS test for member 189 returns TRUE as it is present in the PATH.

So, now we have a means of evaluating in a single column what that rows place in the hierarchy is, and more importantly what its parents are.

The next challenge is to map this to the user data. Now, as I mentioned, there isn’t a meaningful relationship that can be established between the data and the security table, which I’ll show below:


This means any relationship needs to be generated on the fly. Also note it’s not a single higher parent per user – a user may need to be able to view different paths in the hierarchy. An example case of this may be a manager with a responsibility for managing employees performance across multiple units but without a direct reporting line in the hierarchy.

GENERATE a relationship dynamically

The data items can be linked using the GENERATE function in conjunction with some FILTERs to determine what applies. GENERATE creates a cartesian product of two tables. So by using GENERATE between the Dimension and the Security table, we get a view of all possible user / dimension level combinations:


Now this is only a starting point because we need to return a single value within our equation otherwise DAX will throw an error. Also, we don’t need everything from UserSecurity, just the items that apply to the current user. So first, lets FILTER our UserSecurity table:


Note I’ve hardcoded the User here to “U4″ but in a real life context you would use the USERNAME function to get that name. Now we’ve cut down on one side of the cartesian product, but now we need to cut down the other side to get the members we are allowed to see. So here we filter using PATHCONTAINS to pick up the items that are the permitted members or children of those members:

GENERATE(FILTER(UserSecurity,UserSecurity[User]=”U4″),FILTER(‘DimEmployee’,PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),UserSecurity[Highest Parent]))

So now in the GENERATED table we have all the members of the Dimension that the current user is permitted to see.It’s worth taking a quick diversion from DAX to show how this works in practice:


Because the PATH contains the keys for all members in the hierarchy from the current member to the topmost parent, any PATH that contains the highest allowable parent will pass the PATHCONTAINS test. So if looking for member 189, rows 1&2 fail because though they are part of the overall path they are above the highest permissible member. Rows 3&4 pass because they feature the highest permissible member in the path to the topmost parent. Row 5 fails because the highest permissible parent doesn’t feature in the PATH at all.

Note that because the result at this point is still a Table as opposed to a single value, it handles users with multiple permissions correctly. The next part is to make sure that in the specific row of the Dimension we are evaluating if that row is in the permitted list, which we can do by putting an outer FILTER on the GENERATE.

FILTER(GENERATE(FILTER(UserSecurity,UserSecurity[User]=”U4″),FILTER(‘DimEmployee’,PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),UserSecurity[Highest Parent]))),DimEmployee[EmployeeKey]=EARLIER(DimEmployee[EmployeeKey]))

We use the EARLIER function to get the current value of the EmployeeKey from the outer evaluation pass and give us the context specific to this row. Our final step is to convert this into a single value, which I do using a COUNTROWS function:

COUNTROWS(FILTER(GENERATE(FILTER(UserSecurity,UserSecurity[User]=”U4″),FILTER(‘DimEmployee’,PATHCONTAINS(PATH([EmployeeKey],[ParentEmployeeKey]),UserSecurity[Highest Parent]))),DimEmployee[EmployeeKey]=EARLIER(DimEmployee[EmployeeKey])))

Because of the way the data works in this example, COUNTROWS will return either 1 if Authorised or 0 if unauthorised.

The final step is then to use this Authorised calculation in the Measures to ensure we can only see permitted values for that user, which is no more complex than:


… and it’s done. Totals created by this measure will be sensitive to what the user is entitled to see and be fully aware of the Parent / Child relationship even though we only explicitly secure a parent.

Seeing this in action, we see that user U4 is authorised to see the two nodes from keys 290 (Alberts) & 294 (Abbas) and downwards:

Security Demo Pivot
Security Demo Pivot

As I mentioned previously this doesn’t secure the Hierarchy members from being visible, so the path up the tree is still visible – but there will be no values coming through that may be tied to those members.

Quick wrap

So this post has been pretty dense. If you want to get your hands on the result I have provided a sample PowerPivot workbook which has the completed example.

As a reminder, we set out to:

  1. Secure a Parent Child dimension at a Parent level, allowing all children to be visible
  2. Allow multiple Parents to be securable to a given user
  3. Keep it as easy to administer as possible

We did this by using the PATH function to make each row aware of where it was in the Parent / Child Hierarchy. Then using the GENERATE function and some FILTERs to dynamically mimic a relationship we worked out what security could apply. Finally we used the PATHCONTAINS function to work out what security applied.

So we hit point 1 on functionality, point 2 just through the nature of the solution – and by using a simple table rather than Roles / AD type solutions hit point 3.

Finally, a big hat tip to a post from Alberto Ferrari which helped me make sense of Parent Child in Tabular in the first place.

Read More

Windows 7 Security & Bitlocker Drive Encryption

Having got my hands on Windows 7 at last, one of the important things I wanted to do was secure my data using Bitlocker Drive Encryption. After losing 2 laptops to a burglar with large amounts of personal data on them, I am being a little more careful from here on, so that the physical loss of the laptop will be the worst possible outcome, and ID theft or misuse of my data (e.g. raiding my pitiful bank accounts!) won’t be a concern. Bitlocker is only available on Ultimate and Enterprise editions – this I think is an uncalled for feature restriction – to enhance its reputation for security, Microsoft would be better off opening this up to all editions of Windows 7. Users of other editions will have to use Folder Level encryption, which I’ll cover towards the end.

Bitlocker Drive Encryption encrypts the entire disk transparently. What “Transparent” really means to me and you is that if you copy a file from a Bitlocker Encrypted disk to an unencrypted disk, that file is readable by any machine. However should someone try to read the encrypted disk directly they wouldn’t see anything except encrypted garbage. So if your disks are Bitlocker Encrypted, unless someone has the Recovery Key, your drives and data are unreadable.

How do I enable Bitlocker?

So how does this work in practice? Well, a lot depends on whether your computer has a TPM 1.2 chip on the motherboard. If you do, you can simply open the  Bitlocker control panel (just type Bitlocker in the search bar) and enable Bitlocker. The TPM chip manages the storage of the Bitlocker keys – and Windows 7 will stop the process if you don’t have a key. You set a PIN and after that your computer won’t boot without a PIN, and if someone tries to remove the drives to read them – well, they can get the drives out but reading theier content is impossible.

If you don’t have a TPM chip, you can put the Bitlocker key on a USB Key. To do this requires a bit of a workaround as by default Bitlocker will only work if you have a TPM chip installed, and the wizard will block progress if you don’t. Fortunately the nice folks over at have posted a guide on How to Turn BitLocker On or Off without a TPM for Windows 7. It involves going into the Group Policy Editor (some sort of security thing) but is incredibly simple to do. Again, not 100% sure why Microsoft decided to make this a bit awkward to set up, so hopefully this will change in the future.

You can then apply Bitlocker and store the key on a USB drive. From now on your computer will only boot if you have the USB Key plugged in (you can – and should – remove it after the computer has booted). This is a minor inconvenience and you have to remember to keep the USB key seperate from the machine – otherwise if someone steals your computer and you’ve left the USB key in, your encryption efforts are wasted.

How do I prevent me locking myself out with Bitlocker?

This applies to those of us using USB Keys, but the advice is simple:

  • Keep multiple copies of the Bitlocker recovery keys somewhere accessible and safe – such as on a external HDD, Live Mesh, a webmail account…
  • Create multiple USB keys – from the Bitlocker control panel you can load the startup key on as many USB keys as you like. I have 3 copies so if a USB drive fails, or goes walkies, I don’t have to worry about not being able to use my computer. You can also store multiple computers bitlocker keys on one USB key.

I don’t have Ultimate / Enterprise – what are my options?

Well, option 1 is to upgrade using Windows Anytime. But assuming you don’t want to, the other option is Folder Level encryption. All you need to do is pick a folder, right click, select Properties and under the Advanced options choose to Encrypt the folder. If this is the first time you are encrypting something on your machine it will prompt you to back up the security certificate – as with the Bitlocker recovery keys I strongly recommend making multiple copies in multiple safe locations . Your folder will then encrypt and turn green so they are easy to spot. They aren’t angry, like the Hulk :)

Now this isn’t transparent – if you move a file from your encrypted folder to another location it remains encrypted and unreadable without the security certificate. But if someone stole your PC, unless you have autologin set, those files are unreadable, which for the purposes of this discussion is what we intended

So, am I safe and secure now?

Well, maybe, as long as you have done a few other basic things:

  1. Require a password on startup
  2. Have a screensaver that requires a password to unlock
  3. Backed up recovery keys
  4. Backed up your data
  5. Backed up your data
  6. Backed up your data

Good luck, and keep your data safe!

Read More