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!

Installing Subversion from source on CentOS 5.2

Okay so this not strictly BI related but hey, most of us BI types have a slight geek inclination don't we, or is that just me?

I guess the main reason for blogging about this is that I really didn't expect this to be that hard. I mean, I've been installing Subversion from source since about Fedora Core 3 without issues (other than when the Subversion/Berkley DB integration underwent some changes that shafted access to my repositories) so I kinda expected the same here. Um, wrong! At least this time the issue related to a fresh install rather than suddenly finding that I can no longer access my repositories. Needless to say I now use FSFS rather than Berkley DB as the back-end but still. A friend of mine also works on FSFS (see: http://www.farside.org.uk/200704/tree_structured_fsfs) so he'd been going on at me for a while to switch. Still took that heartache to make me use FSFS though, groan...

Anyway, so a while back (meant to blog about this a long time ago but based on how long it has been since my last blog, I can see that it really was a loooong time ago) I set about compiling Subversion from source code. I guess there are many reasons for installing Subversion from source code but that's immaterial here. Needless to say that I started and soon came to realise after running ./configure that there were some missing dependencies when trying to enable SSL. After a little bit of searching and running rpm -qa etc. to find if certain libraries were installed I found that my install of CentOS was missing the openssl-devel library. So, a quick install using yum and I was away, or was I?

Okay, so maybe not. After installing the missing library another error occurred during the ./configure script run. This particular issue, however, is documented in the Subversion documentation and requires you to amend the parameters passed to the configure command in order to have the information passed through to the "sub-configure" scripts. Rather bizarrely you need to specify the --with-libs parameter, specifying "/usr/kerberos" as the value. (i.e. --with-libs=/usr/kerberos)

I guess the part that really got me for a while was the fact that when you run the configure script, passing in the above mentioned switch, a message is generated on the first line stating: "configure: WARNING: unrecognized options: --with-libs" however this message can be safely ignored. That little tidbit of information is not specified in the documentaion. So, if you leave the script to run it should actually run to completion, barring any other issues naturally.

Long and short of it is that in order to compile Subversion with SSL enabled on CentOS 5.2 (well with the my install base anyway) I had to do the following:

# Unpack the source
tar -zxvf subversion-<version>.tar.gz
cd subversion-<version>
# Check that openssl-devel has been installed.
yum list openssl-devel
# If not, install using the following:
yum install openssl-devel
# Configure and install subversion with FSFS,
# ignoring message "configure: WARNING:
# unrecognized options: --with-libs".
./configure --without-berkeley-db --with-ssl --with-libs=/usr/kerberos

And that's all there is to it!