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.

6 comments:

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

    ReplyDelete
  2. Brilliant man..

    ReplyDelete
  3. 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. 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. 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. This blog was so interesting. It was also solve my issues. Thanks for this.

    ReplyDelete