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.
Next, create the below lookupvalue formula and assign it to the table as a DAX filter.
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.
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.
In order to test this, you can use the ‘Analyze in Excel’ feature in Visual Studio (SQL Server Data Tools):
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.
