Dimension Security using SSAS Stored Procedure

Often time the business needs are such that the security restrictions of users change periodically due to modification of job roles, work location and/or other attributes.  A solution that can accommodate addition, update and removal of users seamlessly or dynamically in SSAS is critical to reducing latency and minimizing user security maintenance.  The idea of using SSAS stored procedure to enforce dimension security dynamically is rather straightforward: While all users belong to a single SSAS database role, individually, their data access is restricted based on the set of dimension members they have permission to as determined by the SSAS stored procedure dynamically.

A short time ago my team identified a requirement to refine our overall SSAS security scheme.  The data available in our cubes needed to be filtered to authorized users by their sales location(s).  The list of users and their authorized sales locations are maintained in a collection of M:N relational tables in one of our SQL Server databases.  Our ETL refreshes these tables regularly.  For simplicity, a database view is used to de-normalize the data in these tables into "user alias-to-sales location" pairs as shown in the mock-up here.

UserAlias	SalesLocation
usera	United States
usera	Canada
userb	United States
userc	Hong Kong
userd	United States
userd	Germany
userd	UK

To avoid the potential of SQL injection attacks, a SQL Server stored procedure is used to look up the data in the view.  The stored procedure (listed below) takes the alias of the current user as an argument and returns the corresponding sales locations.  The domain definition is stripped off for our purpose.

   

Our SSAS stored procedure (shown below) is implemented as a method from the UserSecurity C# class library - GetFilteredSet_SalesLocation().  The SSAS stored procedure employs the [usp_GetUserSalesLoc] SQL Server stored procedure to retrieve and return the unique name of the sales locations. 

The GetFilteredSet_SalesLocation() method takes three string-type arguments: user alias of the current user, name of the SQL Server where [usp_GetUserSalesLoc] is located (name of the database is hard-coded in the method - yes I know), and the dimension hierarchy level.  The dimension hierarchy level specification allows us to uniformly leverage the SSAS stored procedure across multiple SSAS databases and cubes with similar security filtering requirement but different dimensional schemas.  The method then enumerates through the data set returned by [usp_GetUserSalesLoc] and assembles the MDX set as an output string as such:

{[Dimension].[Hierarchy].[Level].&[MemberName], [Dimension].[Hierarchy].[Level].&[MemberName], [Dimension].[Hierarchy].[Level].&[MemberName], ...} 

Subsequently the SSAS stored procedure is compiled into a .NET assembly (SSASSecurity.dll) and deployed to the SSAS server.  I usually place the DLL in Drive:\Program Files\Microsoft SQL Server\MSSQL.#\OLAP\bin\ directory.  In this particular instance, I had the assembly registered within the server scope so that it can be used by any of the databases on the SSAS server.  It can certainly be registered within the database scope.

Note that the assembly must be registered with "External access" permission at the minimum in order for the GetFilteredSet_SalesLocation() method to function properly.  It also needs to run under an account that has permission to execute the [usp_GetUserSalesLoc] SQL Server stored procedure wherever it might be located. 

Finally we specified one SSAS database role with all user logins added to the membership collection.  The user members have read access to the cubes and dimensions.  For the dimension hierarchy of interest, we placed a call to the GetFilteredSet_SalesLocation() SSAS stored procedure in the MDX expression of the Allowed member set.  The STRTOSET MDX function converts the output string into a valid MDX set.  The USERNAME MDX function returns the login alias of the current user.   

The allowed member set can be defined for either database or cube dimension.  If a dimension is referenced by a number of cubes in the database, it might be desirable to define the allowed member set at database-level and propagate the filter to cube-level through inheritance.  By enabling the "Visual Total" option, we excluded the denied members from contributing to the aggregate values. 

This is it.  Users are now restricted to cube data for those sales locations they are authorized to see.  This security scheme works nicely with Excel when the workbook data connections are specified with integrated security.

There are few approaches to implement dynamic dimension security in SSAS - as you will find in number of writings both online and offline.  I found the SSAS stored procedure approach to be the most straightforward of them all and the least pervasive to implement.  Furthermore security restrictions are much more immediate because they are queried directly from the relational database each time.  However, we do need to ensure the SQL Server and the database where the [usp_GetUserSalesLoc] stored procedure resides is up and running at all time..

Justifying The Value of OLAP

So I was in this meeting at a client site yesterday, we talked about the progress of their DW upgrade and the dashboard/scorecard implementation that my consulting firm is tasked to do.  During our discussion, one of the IT folks, actually a contractor indicated that the current DW (SQL Server 2000) hasn't an OLAP layer, namely cubes.  All user reporting needs are satisfied through the relational DW database.  As part of his assessment, he is putting together a series of proof-of-concept cubes based upon the data sitting in the current DW.  Everything sounded good until we got further into our meeting and the following points were made by the other participants.

• Performance with reporting is not an issue. 
• All canned reports are created in Excel and are being pushed directly to the users.
• All users are "causal."  There are no "power" user requirements such as more complex analysis or ad-hoc query.
• No one in-house knows anything about cubes or multi-dimensional concept.

My inclination here is to ask, at least in the back of my mind, how much value the OLAP layer provides in this scenario.  I can see the POC as showcasing the potential value the OLAP layer may offer in the distance future. Other than that, we are adding a whole new layer of technology to the DW framework which requires additional skill sets and resources to support and maintain.  I maybe missing a bigger picture here but I am not seeing the ROI on this endeavor.

64K Limit

The Problem

I can’t believe I am still talking about SQL Server 2000 when SQL Server 2008 is coming around the corner. But here it is.

At my current project, my team and I operate a portfolio of DWBI solutions implemented on SQL Server 2000 and 2005 platforms. A couple days ago, the ETL processing of one of our older DWs (i.e. SQL Server 2000) failed out of the blue. Upon brief investigation, I determined that the failure was caused by a dimension table containing 64,400 plus member records. In Analysis Services 2000, a dimension member can’t have more than 64,000 children. This applies to the “All” member as well. Anything more, as in our case, causes the processing of the dimension to fail.

The Solution

To get around this limitation, we considered few options and their viabilities. Bear in mind that the dimension in question is a degenerated dimension derived from one of the facts in the DW.

1. Reduce the total number of members going into the dimension in the cube.
2. Reduce the total number of members going into the dimension in both the relational DW and the cube.
3. Create a dimension hierarchy to reduce the number of children per parent – with no changes to the total number of members.

In this particular case our customers and I settled on option 2. We purged all of the fact records older than a cut-off date thereby trimming down the number of members going into the dimension. (We effectively condensed the volume of history available in the DW.)

I must admit we took the quick and dirty approach. Option 3 suggested a more elegant and longer-term solution however it is also much more involved. Furthermore the nature of the data does not allow us to build a dimension hierarchy with meaningful or other logical groupings (Rolodex-style breakdown). As far as option 1 is concerned, since we preferred the data modifications to be consistent in both relational DW and OLAP tiers, the option is out.

The Lesson

I do feel that running into this limitation forces our customers and my development team to re-evaluate the business needs, growth requirements and usability of the DWBI solution. A few thousand children per parent already present a usability problem – that’s a lot of rows for users to drill down into a dimension. I simply can’t imagine having 64,000 items to select from in a drop-down list. Moreover some client tools may not be able to handle the volume gracefully.

SSAS Database Synchronization for Scalability & Availability

Database synchronization can be leveraged as a convenient solution to promote scalability and availability for the OLAP tier in a DWBI implementation.  It allows for a scale-out strategy by deploying copy of the SSAS database to multiple servers to load-balance user queries.  It increases availability by reducing the duration of time the SSAS database is offline or inaccessible due to processing.  In many scenarios, synchronization takes less time to perform than processing.

Traditionally, in Analysis Services 2000, synchronization implies backing up and restoring the SSAS database.  Nonetheless I had mixed success of getting msmdarch.exe to work properly with larger .cab files and across servers.  Analysis Services 2005 simplifies the task of synchronizing SSAS databases by making available the Synchronize XMLA command and the Synchronize Database Wizard which is a UI that ultimately creates and issues the Synchronize XMLA command.  Of course, in 2005, we can still back up and restore the .abf file.  But the synchronization capability is much more straight-forward.  

Several of our DWBI implementations take advantage of this synchronization capability Analysis Services 2005 has to offer.  Our solution performs processing of the cubes on a staging SSAS server and deploys (or sync's) the cubes to the presentation SSAS servers dedicated for user queries.  The diagram below illustrates the processing-flow of our solution.

Synchronization to the production SSAS servers is achieved by the means of the Synchronize XMLA command.  A DDL script in the form of XML specifies the command and all of its parameter elements (i.e. Source database, compression option) in an .xmla file.  (XMLA provides a declarative approach to specifying requirement and effectively eliminates the need of writing more complicated code - consequently enhancing the maintainability of the solution.) 

The Synchronize XMLA command is to be issued on the target SSAS server as opposed to the source SSAS server.  It can be manually executed in SSMS.  Or it can be run programmatically as in our solution using the Analysis Services Execute DDL task in a SSIS package. 

In order for the Synchronize command to work successfully, make sure that the Windows account under which the target SSAS server runs has administrative rights to the source database.  The target server executes the Synchronize command under its service account.