Technical: Microsoft – SQL Server – Analysis Services – Cube Browser – Error – “The server sent an unrecognizable response”

Technical: Microsoft – SQL Server – Analysis Services – Cube Browser – Error – “The server sent an unrecognizable response”

Background

In the middle of adding a new dimension to our cube and ran into an error message stating “The server sent an unrecognizable response”.

Pictorial

HexadecimalValue0x14IsAnInvalidCharacter

Possible Solutions

Goggled for help and most of the help led me down the path of reviewing the dimension’s columns attributes; specifically the KeyColumns sub-property that reads  “InvalidXMLXCharacters”.

The attribute has three possible values Preserve, Remove, and Replace.

Here is a sampling of corresponding web documents:

Attempted Solution

Change InvalidXMLCharacters from Preserve to Remove

To effect:

  • Access the “Dimension Structure”
  • Select each attribute and right click on the attribute
  • Navigate to the “Solution Explorer”
  • Transverse to the “NameColumn” property
  • Navigate to the Sub property titled “InvalidXMLCharacters”
  • And, I will suggest that you change the property’s value from Preserve to Remove

DimensionAttribute
So on our new dimension, we changed all the WChar (Wide Character) attributes as noted above and rebuilt our solution.  But, upon querying our cube same problem.

Review Data, looking for special characters

Using a nice query posted by Jitendra:

Solving MDX query error “The server sent an unrecognizable response”

http://biscoop.wordpress.com/2011/04/21/solving-mdx-query-error-%E2%80%9Cthe-server-sent-an-unrecognizable-response%E2%80%9D/

was able to confirm that the data set does not in fact have special characters; or more specifically the ones that are specified in Jitendra’s solution.

Our Solution

  • Our crude solution was to create a new database table that resembles the original dimension table.  But, in this case carefully populate with cleansed data; data that we were sure only contained English character set.
  • Accessed the Data Source view, and added the new Table
  • Created a new dimension against the new Table
  • Accessed the Cube and attached a new dimension

Summary

Now I see why they say it is best to have separate tables for your OLTP and OLAP data sets.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s