Microsoft SQL Server 2008 Analysis Services Unleashed Review Chapter 37

This chapter talks about using the MDX editor in Business Intelligence Development Studio to set cell security.  Then, the book describes testing the result using the “Test Cube Security” link.  I like the idea of testing the code since writing MDX needs to be precise.  Note too that generic MDX code which might be correct today may not be correct tomorrow (especially if the MDX code includes relative as opposed to absolute references).  The book’s example on page 753 has only absolute references.  Thus, do not be lulled into a false sense of security by only checking the MDX script one time, or by the story that someone else checked that MDX script in the past.

Contingent cell security (page 756) discusses a setting to prohibit users from also seeing calculated members derived from elements which have cell security. The logic is that users may be able to indirectly calculate a restricted value if they know the relationship between that restricted value and other values.  Note though that the book’s example is rather trivial since if someone actually knows profit and cost, they can manually calculate (or at least estimate) sales, even if contingent cell security prevents Analysis Services from making that calculation.  My recommendation, as always, is to look at security as a holistic topic, and not just an Analysis Services settings discussion.  For example, what is the point of setting cell security on store sales if that same user can widely obtain a report with that information on it?

Dynamic cell security (page 758) also leverages the MDX UserName function to allow for customized MDX expressions.  As I commented in the chapter 36 review, “dynamic” can mean more than just user name, and people can leverage the full power of MDX to respond to all types of state variables and settings.

This chapter is quite brief, and could have been bundled with the previous chapter, especially since chapter 36 started out with a discussion on both topics.

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