Dynamic security in SQL Denali #PowerPivot CTP3

One common requirement is that users only see data relevant to them. This normally corresponds to the role he or she has within the company. In this blogpost I will show you how this can be done in SQL denali PowerPivot CTP3.

It is important to know that security is one of the features which can not be set up within the Excel PowerPivot addin but only within Visual Studio 2010 (BIDS). Microsoft feels like this is a typical feature that BI developers would create, not end users.

I am using the AdventureWorks data warehouse for example data.

This is the “before” situation:

What I want is for me to see only those orders relevant to me. To do this I have to extend the current Employee table with an extra attirbute called “DomainAccountName” in which, yes you guessed right, I will store my unique domain account name. Using the DAX function USRNAME() I will show you how you can leverage this.

Our data model with the extra attribute “DomainAccountName” in table DimEmployee:

I have updated the following employee to use my domain account name; “David Campbell“. Clicking on the “role manager” icon in Visual Studio 2010 I open the “Role manager” window in which I define a “Role”:

Using the following syntax I make sure that only fact table records are returned to which my account name is bound.

=if(RELATED(DimEmployee[DomainAccountName]) = USERNAME(), True, False)

Here is the proof:

Just to point out I did not use any filters or slicers I included the “PivotTable Field List” in the screenshot 🙂

So there you go, basic dynamic security done within moments. The real rocket science lies in you data model design where you choose how security impacts data. I will try to come back on this subject to explain more complex “dynamic security” scenario’s.


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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