locked
How to get rid of hex data in a dimension RRS feed

  • Question

  • I did a large data load and somehow I ended up with what SSAS inturprets as an esc character (0x1B) in one of my dimensions.  I tracked it down in my SQL database that serves as the data source for the cube.  I had 6 or 7 text fields with an esc appended to the text.  I removed the offending characters in the SQL table and reprocessed my dimension and my cube.  Even though the offending characters are no longer in my data source tables I can't seem to rid them from my cube.  I've reporcess the dimension and the cube several times.  I deleted the offending dimension and recreated it.

    I can't seem to get rid of the bad data.

    Any suggestions on how I can clean my cube?

    Thanks.


    OOOPPPSSSSS.....I found another % in one of the fields.  This one SQL Server didn't find when I searched for '←' so I'm not sure if SQL missed it or if it's a different hex character that SQL is representing as an %...........


    UPDATE - I removed the offending characters and I can't find any more in the DSV.  I can't seem to get SQL to search for them as the search I tried (select * from dimCustomer where Name like '%←%' ESCAPE '←') only found ←'s at the end of a string not in the middle.  I reporcessed my dim and cube and when browsing the dim I still get an error that it found an ←.
    Friday, December 11, 2009 3:30 PM

Answers

  • Couple of tricks. First is that it's helpful in SQL to cast to varbinary in order to find special characters. You might try that.

    Second, there's a setting called InvalidXmlCharacters that you can set to Remove in order to have SSAS get rid of those characters rather than throwing an error. To get to that property, find the offending dimension attribute in the dimension designer in BIDS. Right click on that attribute and choose properties. Then find the KeyColumns property and click the plus sign. Click the plus sign next to each column. Then change the InvalidXmlCharacters property. Do the same on NameColumn and ValueColumn if those refer to the same problem column.

    Let us know if that doesn't help.
    http://artisconsulting.com/Blog/GregGalloway
    • Proposed as answer by GregGallowayMVP Friday, December 11, 2009 10:43 PM
    • Marked as answer by JohnDMP Monday, December 14, 2009 1:22 PM
    Friday, December 11, 2009 10:43 PM

All replies

  • Couple of tricks. First is that it's helpful in SQL to cast to varbinary in order to find special characters. You might try that.

    Second, there's a setting called InvalidXmlCharacters that you can set to Remove in order to have SSAS get rid of those characters rather than throwing an error. To get to that property, find the offending dimension attribute in the dimension designer in BIDS. Right click on that attribute and choose properties. Then find the KeyColumns property and click the plus sign. Click the plus sign next to each column. Then change the InvalidXmlCharacters property. Do the same on NameColumn and ValueColumn if those refer to the same problem column.

    Let us know if that doesn't help.
    http://artisconsulting.com/Blog/GregGalloway
    • Proposed as answer by GregGallowayMVP Friday, December 11, 2009 10:43 PM
    • Marked as answer by JohnDMP Monday, December 14, 2009 1:22 PM
    Friday, December 11, 2009 10:43 PM
  • Wow.  InvalidXmlCharacters; that was easy.

    Thanks!
    Monday, December 14, 2009 1:22 PM