Showing posts with label Tabular. Show all posts
Showing posts with label Tabular. Show all posts

Sunday, October 7, 2012

Defining Row-Level Security in SSAS Tabular database

I work in HR IT where security is always the primary consideration when it comes to data. Recently I was watching a replay of the TechEd’12 presentation by Julie Strauss on “Developing and Managing a BI Semantic Model in Microsoft SQL Server 2012 Analysis Services”. I want to highlight the row level security bit that Julie demonstrated for my own future reference.

Create a table with the user-id and the dimensional values you want to secure. In the below example, she is securing SalesTerritoryCountry dimension values. Basically, its just a separate table used for looking up.

image

image

Next, create the below lookupvalue formula and assign it to the table as a DAX filter.

image

The above formula translates to: Go to the Security table>SalesTerritoryCountry column, lookup the username with the username used to logon with and return SalesTerritoryCountry. That is how row-level security works. You only show the rows that the end users are authorized to see.

image

In addition, you can also secure the Security table itself from being exposed to the end user. This is simply done by hiding it from client tools.

image

In order to test this, you can use the ‘Analyze in Excel’ feature in Visual Studio (SQL Server Data Tools):

image

Use ‘Other Windows User’ to test the security by emulating the other user (s)

Obviously, there is more to security than just row-level security. If you have successfully implemented  other strategies for securing your data in SSAS, leave me a note below.