Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 36

The book’s diagrams on pages 733 and 734 illustrate what dimension and cell security do:

• Dimension security removes details and removes amounts from aggregations
• Cell security removes details, but does NOT remove amounts from aggregations

As I commented in the last chapter review, the Analysis Services access security may not behave as many people believe, and therefore it may make sense to make another cube which has only the appropriate information for larger groups.  Even with one cube, someone will have to set and maintain settings as the database grows.  Also, a single cube model will have additional performance impacts to remove some information from final display.

VisualTotals (page 740) is a setting which allows for displaying either the aggregation of all members, or only the aggregation of the members which do not have dimension security.  Again, a “total” means a calculated total, and as on a spreadsheet, it is important to know what the “total” aggregation represents.    The difference is important when Analysis Services becomes the source for reports through Reporting Services or SharePoint.  VisualTotals are set by dimension and by attribute.

Testing dimension security (page 744) can happen through Business Intelligence Development Studio, or through SQL Server Management Studio.  As I stated earlier, having dimension or cell security requires to have someone to not only make the settings (as this chapter defines) but also continue to make changes as 1) the Analysis Services database structure changes, 2) organizational policy changes, 3) people move to new organizational roles (perhaps requiring more or less security), 4) people join the team, and 5) people leave the team.  While Analysis Services provides excellent granularity on settings, I recommend that organizations take a holistic view of what is involved in security beyond just the technical people who can make these settings.  Organizations have a tradeoff to make between resources invested to maintain security, and the level of assurance provided.

Dynamic dimension security (page 746) happens through MDX, and assisted through the UserName MDX function (allowing the script to dynamically respond to the specific user’s credentials).  I remember writing similar permissions for applications in the past, but in my case I was programming against the Windows 32-bit API to obtain the user name, and use that information inside the application.  Because MDX expressions can be used, I can imagine other ways that this script can be dynamic, including allowing MDX to dynamically respond to system state.  The intention of the authors was a more basic and helpful message:  security can be dynamic based on who a user is.

Dimension security architecture (page 748) describes the Analysis Services efficiency in evaluating security, even when users have many roles, or databases have many and large dimensions.  The book describes the bitwise architecture of a fast memory swapping strategy to implement this architecture.  I believe that this solution is fast — but I repeat my earlier recommendation to consider multiple Analysis Services databases because you may be able to remove entire classes of roles from having security defined (they would simply have access to the entire database).

The flowchart on page 749 shows information again which justifies the book purchase:  dimension security is evaluated before MDX scripts, which are evaluated before cell security.  The order of operations is consistent with what I said earlier:  that dimension security will remove items from totals, but that cell security does not affect totals.

Finally, page 750 makes some careful statements on how Analysis Services combines security information from multiple roles.  I will not repeat their statement, but give a general rule:   multiple roles can potentially lead to variable query results.

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6