Tuesday, March 27, 2012

Analysis Services error: The size specified for a binding was too small

Analysis Services error: Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated.

This error occurs when the size(number of characters) of an attribute exceeds the DataSize defined in the attribute properties of the dimension. By default, Analysis Services sets the size of an attribute based on the size defined in the relational database.

For example, let’s say you have a field called CustomerID which is defined as VARCHAR(4) in the database. If you use this field as an attribute in a dimension, Analysis Services will set the DataSize property to 4. You can see this property by expanding the NameColumn and KeyColumns property of the attribute.

Now, if you replace the dimension in Analysis Services with a named query and edit the  CustomerID field with something like CASE WHEN CustomerID IS NULL THEN ‘Not Defined’ ELSE CustomerID END AS CustomerID, the cube fail to process because Analysis Services has already set the data size for CustomerID to 4 but we just violated this by using the value ‘Not Defined’ which is of length 12.

To resolve this issue, all we need to do is increase the value of the property called DataSize for the attribute under both NameColumn and KeyColumns.

Hope this helps.

7 comments:

  1. AnonymousMay 23, 2013 at 3:12 PM

    Solved my strange new problem which is now only a solution. Thanks.

    ReplyDelete
  2. AnonymousJune 18, 2013 at 9:57 AM

    Brilliant man..

    ReplyDelete
  3. flameaterJune 26, 2013 at 8:52 AM

    Hi Faisal, This is interesting. I have always had to drop the attribute entirely and rebuild it again because changing the DataSize properties never worked. I have just encountered the same error and this did not work for me. I am using Visual Studio 2008. What Visual Studio/Data Tool version are you using.

    ReplyDelete
  4. FaisalJune 26, 2013 at 11:00 AM

    Hi Flameater,

    I've encountered this issue with SSAS 2005, 2008 and 2012 and was able to resolve by changing the data size property. Did you make the change to both NameColumn and KeyColumns?

    ReplyDelete
  5. Tilmann StarkeJuly 12, 2013 at 6:01 AM

    Hi together,
    I had the same problem.
    After fiddling around some time, I found I had to:
    - change the NameColumn and KeyColumn sizes
    - process the dimension separately
    - process the whole cube

    That's it for me. Thanks.
    Kind regards,
    Tilmann Starke

    ReplyDelete
  6. Faisal Zahoor AhmadOctober 23, 2017 at 7:50 AM

    This blog was so interesting. It was also solve my issues. Thanks for this.

    ReplyDelete
  7. UnknownFebruary 11, 2020 at 2:59 PM

    For me it was issue that translation of attribute remained same size as before. I had to also change this manually under translation tab and update worked. So if you have translations you have to change datasize there also.

    ReplyDelete

Subscribe to: Post Comments (Atom)