THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Improvements to dimension security in Analysis Services 2005

What changed with respect to dimension security in Analysis Services 2005 ? A lot ! I will try to touch on most important changes.

Attribute based instead of hierarchy based.

This is perhaps the most important change. The concept of UDM goes across entire Analysis Services, and dimension security is not an exception. Just like calculations in MDX are attribute based - so is dimension security. In terms of Analysis Services 2000 it means that when master dimension is secured - all of its virtual dimensions would get secured appropriately and vice versa. For example, if we secure specific member on attribute Year - automatically all months of that year, all weeks of that year, all quarters of that year etc - get also secured. This is similar to Analysis Services 2000 - when you secure the parent - all of its children get secured, but it only worked in single hierarchy. In Analysis Services 2005 - all hierarchies: Year-Quarter-Month, Year-Week, Year-Month etc will get secured accordingly.

This magic is achieved by applying auto-exist between Allowed/Denied sets on dimension attributes. This also allows to simplify some common expression for allowed set which used to filter by member property. For example, in my book "Fast Track to MDX", we show the following example of dimension security:

Filter( [Store].[Store Name].MEMBERS, Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

In Analysis Services 2005, instead, we would simply add [Store Sqft] as additional attribute to the Store dimension, and define dimension security on it:

[Store].[Store Sqft].[Store Sqft].MEMBERS(0) : [Store].[Store Sqft].[Store Sqft].&[2000]

This will affect individual stores, and if needed other attributes such as City, State etc.

Scalability of dimension security.

The way dimension security was implemented in Analysis Services 2000 - was by creating special replica dimension (sometimes incorrectly called "shadow" dimension. Shadow dimensions actually have nothing to do with dimension security - they are simply snapshots done during DSO transaction and dimension writeback in order to preserve ACID properties of transaction). Replica dimensions weren't the entire portion of the dimension, they only contained unsecured members (plus their siblings - as empty placeholders if siblings were secured), and the replica dimensions were created only if necessary on the fly. Still, when dimension to be secured was big (millions of members), and number of different roles was big as well (hundreds), and unsecured part was relatively big - it could've caused scalability issues, because in Analysis Services 2000 all dimensions were always loaded into memory, and this includes replicas as well. In Analysis Services 2005 the implementation is different. Dimension security is implemented as virtual bitmap on the secured attributes. This means, that in the worst case, the overhead will be - one bit per attribute member per role. In reality the picture is even better, because those bitmaps are not always implemented as pure bitmaps. For example, if there are big contiguous spaces of attribute members which are secure - they will be compressed instead of using bit per member, some important particular cases are optimized as well - like when only single member on the attribute is allowed or denied etc. But, even if those bitmaps should become very big - they can be swapped out to disk - subject of standard Analysis Services memory manager algorithm, which knows how to swap caches, metadata, dimensions etc - including security bitmaps.

Analytical power & MDX.

There were certain limitations on which MDX functions could be used in expressions for dimension security in Analysis Services 2000. For example, up until SP3 - UDFs weren't allowed, other MDX functions are not allowed even in SP4 - like LookupCube. None of those limitations exists in Analysis Services 2005. Using .NET stored procedures is completely seamless, there is no need to do special tricks to force registration of UDF to be included inside dimension security - all stored procedures which exist on server, database and cube are available. What is even more important, since UDFs in AS2K could only return scalar data types, the user had to produce strings and then invoke StrToSet function in order to convert them into real sets. This approach had performance implication, as well as functionality implication, since the user had to ensure that the string length doesn't exceed the allowed maximum. In AS2005, there is server ADOMD.NET object model available, which can return real MDX sets from stored procedure. Example of such stored procedure can be found here.

Additionally, dimension security MDX expressions, are evaluated after executing MDX script of the cube - which allows them to refer to the calculated values in the cube, whereas only data at pass 0 was available in AS2K.

Published Sunday, April 10, 2005 12:30 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement