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:
- Creating a replicated version of an existing, already processed, tabular model cube
- Deleting the specified columns from the second cube
- Adding access for windows users / groups to the second cube
This solution has the benefits of:
- Adding minimal additional development effort
- Preserving only one version of cube source code
- Retaining the current level of performance for all measures and dimensions
- 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.