SSAS – varchar/nvarchar column size incorrectly mapped between database and cube

Today we received a cube from vendor which gave us the error:

“The size specified for a binding was too small, resulting in one or more column values being truncated.”

On researching more found this bug on Microsoft site

When large varchar/nvarchar data type columns are used in the data source views their size is not properly set in the cube dimension definitions causing the following exception when the dimension is processed:

“The size specified for a binding was too small, resulting in one or more column values being truncated.”

For instance:

– for nvarchar(4000) the DataSize property of the corresponding dimension is set at 1024;

– for nvarchar(max) the DataSize property of the corresponding dimension is not set at all.

Further more, specifying the maximum size for varchar/nvarchar(max) data type in the dimension to 2147483647 causes the following exception when the dimension is processed:

“Memory error: Allocation failure : Not enough storage is available to process this command.”

In fact any size larger than 163315555 causes the memory error.

The data type itself is mapped appropriately (i.e. to WChar as expected).

Warning: this problem occurs even when the cube and its dimensions are created for the first time, not only after the data source view has been changed and the changes have not been propagated manually to the dimension definitions.

Implemented the workaround mentioned:

Set the value of the DataSize property for the offending column in the erroneous dimension definition to an appropriate value that does not exceed 163315555 Bytes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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