Wednesday, 9 February 2011

'Invalid characters' in Analysis Services cube

We ran into the following error whilst trying to query our development cube today;

The server sent an unrecognizable response.
' ', hexadecimal value 0x19, is an invalid character.

Rather peculiar I think you'd agree! Turns out the data in question was a Unicode field with a ASCII control character in it. 0x19 is ASCII character 25 (19 in hexadecimal) - which stands for EM or End of Medium apparently.

Analysis Services quite happily processed and stored the data - however any attempt to query it was defeated despite the fact that the associated dimension attribute was also Unicode (ie a WChar). This, I realised, was due to the fact that clients communicate with Analysis Services using the XML for Analysis (XMLA) protocol. As the name suggests its based on XML which cannot encode some unicode characters - including ASCII 25!

How on earth it got into our source system was a mystery - but we had to do something about it.

Fortunately there's a very useful option in Analysis Services for dealing with this sort of issue; the InvalidXMLCharacters property. You can find it by right-clicking on an attribute and selecting Properties:

Then expand the 'NameColumn' property and under that you'll find the InvalidXMLCharacters property:

Essentially it tells Analysis Services what to do with characters that aren't supported by XML

It has three settings:

PreserveKeep invalid characters. The default
ReplaceReplace with a '?'
RemoveRemove the character from the query results

We opted for 'Remove' which resolved our problem. Its worth considering setting this on all unicode and non-unicode text attributes as potentially any off these could include invalid XML characters. It really depends on how well you know - and trust - your data source...