Analysis Services 2005 - Dynamic Default Member

Whilst migrating a database from AS2000 to AS2005 I came across in interesting issue when trying to replicate the same behaviour as the "legacy" version. Firstly, a little background...

For the sake of expediency I used the migration wizard to convert most of the AS2000 database over to AS2005. I say most because although the validation checker stated that everything was a-okay, when it came to actually do the migration the wizard whinged about a dimension that was joined to different cubes at different levels within the dimension. In order to progress this, I deleted the dimension and migrated the rest.

So, what I was left with was a pretty complete system that, although not overly complex, it wasn't trivial either. The database contained a number of cubes and virtual cubes etc. and they contained a financial line dimension making use of unary operators and custom MDX for specific members. When designing the AS2000 database originally, I specifically avoided calculated cells so there were none of those but it did contain quite a number of calculated members. I must say that I was actually quite impressed with the conversion process once it worked. Certainly the dimensions were migrated quite well, though it made a bit of a mess of migrating the "alternate hierarchy" dimensions. I'm also not a big fan of using linked measure groups etc. so I'll probably back those out into a series of cubes using multiple measure groups etc. but I digress.

Whilst going through all the dimensions, to compare the migrated version to the original version, I noticed that the [Currency] dimension had not been migrated to be the same as the original. The dynamic default member definition was missing and needed to be added back. Now, based on AS2000 functionality, the [Currency] dimension had the default member defined using the "Filter" function to filter the members of the dimension based on a "Default Flag" member property such that when the member property was "1" it identified the default member.

In AS2005 the preferred method is to use the "Exists" function and it performs a lot better than filtering. Indeed, there is also a known issue in AS2005/8 where use of the "Properties" function when specifying a default member causes heap corruption during processing so it should be avoided. Now the interesting bit, The currency dimension does not have an "all" level so I set the "IsAggregatable" property to false, changed the "Default Flag" attribute's "AttributeHierarchyEnabled" property to true, to change it from a member property to a true attribute and set about deploying and processing the revised dimension. All of that resulted in the following error:
Errors in the metadata manager. The 'Currency' attribute with IsAggregatable=false must not have any relationship to 'Default Flag' attribute that has AttributeHierarchyEnabled=true

So, rather interestingly, you cannot have attribute 'x' related to attribute 'y' if attribute 'y' has its "IsAggregatable" property set to false. Converting attribute 'x' to a member property serves to allow the dimension to deploy but prevents the use of the attribute in the "Exists" function. One would have to revert back to the use of the "Filter" function which we really need to avoid. Now I'm not sure why this limitation exists, it just does. So how to work around it?

Well what I did, was to create a copy of the key attribute and have it related to the key. I changed the attribute relationship to "Rigid", as this should never change, and the cardinality to "one to one" even though this latter property isn't used in AS2005. Then, on the key attribute, I set the "IsAggregatable" property to true, the "AttributeHierarchyVisible" property to false and left the "Default Flag" attribute relationship on the key. The key also had to be renamed so I added an underscore to the end of it.

On the copied attribute (i.e. the copy of the key) I renamed it to the name of the key attribute but without the underscore, set the "IsAggregatable" property to false and defined the default member with MDX using the "Exists" function and the "Default Flag" attribute as follows:
Exists([Currency].[Currency].[Currency].Members, [Currency].[Default Flag].&[1]).Item(0)

This appears to do the trick at the expense of duplicating the attribute, providing a dynamically set default member on an attribute that is not aggregatable.

I hope this helps others out there who may face the same issue and hey, if you know why you can't do this straight on the key, please enlighten me!