A Workaround for Column Security in the SQL Server Analysis Services BISM Tabular Model

An often needed feature in the SSAS BISM Tabular Model is the ability to restrict users from viewing certain columns.  Perspectives enable a better user experience but are not meant to be used as a security feature, as users can still access the underlying cube.  Tabular models enable flexible row filtering, but do not provide any functionality for column or perspective security.

I have created a workaround for the lack of included column security.  This workaround enables selected users to see all of the columns in a cube and others to see the same cube without the sensitive data columns.  The solution involves configuring and scheduling an SSIS package (available here) and works without any changes made to the existing cube.

The SSIS package works by:

  1. Creating a replicated version of an existing, already processed, tabular model cube
  2. Deleting the specified columns from the second cube
  3. Adding access for windows users / groups to the second cube

This solution has the benefits of:

  1. Adding minimal additional development effort
  2. Preserving only one version of cube source code
  3. Retaining the current level of performance for all measures and dimensions
  4. Preserving existing processing times

The SSIS package uses a combination of XMLA and Tabular ADO to perform these functions.  To use the SSIS package, download the attached SSIS project file.  You can then update the SSIS connection manager, project and package parameters to match your specific setup.

2 Comments

  • Darren Gosbell July 9, 2014 4:20 pm

    This technique would be easy to retrofit to an existing database. But it will require time to copy the database and it will double your memory usage. Both of these could be an issue for a large model. It also requires different users to connect to different cubes. However my alternative solution – http://darren.gosbell.com/archive/2014/04/22/implementing-column-security-with-ssas-tabular-and-dax.aspx – does come at the expense of introducing a M2M relationship and creating extra DAX expressions which will have a performance impact. It would be nice to see something like this implemented in the product so that workarounds like these were not needed.

  • Aaron July 9, 2014 4:37 pm

    Hi Darren,

    Completely agree with your assessment. Both methods have their strengths / weaknesses. Copying the database doubles memory usage and requires extra time (in my tests 20 minutes or so for a cube with 10-20M fact table rows). The M2M method would work better when there are many report connections (SSRS, PerformancePoint, Power View…) into the cube, and you don’t want to have to duplicate reports. However, if there are many DAX measures in the cube, the M2M method requires a lot of retrofitting, and the M2M logic will have an impact on query performance.

    I also agree with you that Microsoft should implement this in the product, and I think a great solution for Microsoft would be to allow roles to be assigned to perspectives.

    Aaron

Your email address will not be published. Required fields are marked *

Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons