Putting private data to public good

I was very pleased to read about IBM's collaboration with US cancer institutions. Using the history of people's cancer treatments and their progress to help determine the most effective treatment for a newly diagnosed patient. This is a great example of putting private data to public good. I have been looking for similar opportunities in the health sector for many years. There is so much scope to use historical diagnoses, demographic data, treatments and outcomes to forecast the risk and efficacy of various treatment options. However, I have found a pretty solid wall declaring such data couldn't be used for "privacy" reasons. This is a real shame, for if I were diagnosed with an illness where the health specialist was deciding between various drug therapies and surgical options, I would like that specialist to make use of all the information available. Of course, very experienced health specialists will have a vast range of experience and read reams of published studies. But I am sure that a data mining model over national (or even international) databases would help estimate risks and efficacies of the various treatment options.

I find the general concern over "data privacy" intriguing. Whether it's your Google search terms, driving records, telephone calls, everyone must accept that their personal data is held somewhere. Why are we so paranoid about our privacy? We have lived in communities for thousands of years, where we are known about in the community. We could all withdraw with curtains drawn and minimal social interaction but we would then miss out on so many community benefits.









I hope that in the future, we can all open our blinds and see more "private" data put to public good.

SSAS Dynamic Security

Imagine the scenario where there are hundreds or thousands of cube users, most of which are only permitted to see "their" data. And there is a dimension with an attribute containing their Windows userid. It would be very tedious to set up a security role for every individual. Not to mention the maintenance in adding and deleting roles. Although this could be somewhat automated with AMO scripting.

A technique I like to employ is to set up just one SSAS role, which essentially contains everyone that might have some access to the cube (integrated security only). So, in the Dimension Data security tab, I restrict the "Allowed member set" to just those members where the member name is equal to the username(). Username() is an mdx function that returns the Windows userid.

The Allowed member set might look something like

{StrToMember("Branch.BranchManager.&[" + UserName() + "]")}

The default member could be

StrToMember("Branch.BranchManager.&[" + UserName() + "]")

You can add other roles, for example, one with read access to the entire cube. So the users that can only see their Branch, would be in the first role (they would only be able to see their own branch) and head office users in the second role would be able to see the entire cube.

This is a very elegant solution. As you can see, it requires no maintenance, as new/old users will automatically be able to see their own data. If the branch manager changes, the old user will have no read access and the new user will have read access to that branch.

There are many ways to extend this security. I have combined it with application tables that list users' access. This can be a many-to-one or many-to many relationship. No problem, just create a dimension (possibly many-to-many) , and grant read access to the dimension in the same way. Also, it might be that you only want part of the username, or that you want/don't want the domain. Again, that is no problem, you can use VBA functions in the MDX expression to get the substring that you want. You could also use this technique to manage a black list (Denied member set) instead of a white list (Allowed member set).

A classic use of this technique would be to support sales persons to see information relating to their customers. This technique might be combined with other restrictions. For example the dynamic security role might let browsers see a subset of their data, such as quantities, but not dollar values. And only for their customers.

Note, there is an overhead in Dimension Data security, but in my experience it is almost undetectable. Of more concern, if you needed to use it, would be the many-to-many dimension. Be wary of performance if your m2m dimension and/or m2m fact table is large.

Also note, you probably want to check the "Enable Visual Totals" check box, so that users can only see the total for members they are allowed to see.

The use of this dynamic security technique can be used in conjunction with conventional roles in the same cube.