Warning 1 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.
How do I fix this? I have tried going into the DSV and making sure things were right. I have gone into the dimensions and can't find anything to change.
Any ideas?
Thanks,
Chris
Just to add that I had this error even after I had refreshed the DSV and it had reported no changes.
Manually changing the field length does solve the problem.
Richard
I have the same issue here.... I have changed some fields size and know i just cannot create the dimension...
I have already updated the DSV... NO updates to do... changed the fields properties... searched in the XML code and all point for the right field size... I just don't know what todo...
Anyone has override this issue?
Best Regards,
To update the DSV, just right click on the background and it is one of the menu items. Once you create an object based on metadata in the DSV, that metadata is carried along with the object. You have two options, either edit the object directly, or you can re-create the object (such as a measure group, dimension, etc.).
I talk about this issue in the Project REAL AS Technical Drilldown white paper. See http://www.microsoft.com/sql/bi/ProjectREAL for more info.
Another trick that I use is to script out the database into an XMLA script and then use a normal text editor to find the entries and edit directly. Then run the XMLA script back in using SSMS.
_-_-_ Dave
After i have updated the DSV, the fields sizes and recreated the dimension, nothing worked...
What seems to be the problem was that the KEY was based on two fields and in one dimension level was like (COD_ONE, COD_TWO) and in the other the order was not the same like (COD_TWO, COD_ONE). I putted the same order and then it worked...
But strangely i have another error on the same parent-child dimension. It says that a loop occurs in a specific item... but i have searched that item and there is no loop. ;(
Regards,
I too have found this error message:
Errors in the OLAP storage engine: An error occurred while processing the 'Partition1' partition of the 'Measure Group 1' measure group for the 'My Cube' cube from the Test Database database.
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.
After much debugging I narrowed this down to a single (role-playing) dimension. I had changed the dimension key from type (VARCHAR(1)) to type (VARCHAR(3)) in the named query. To fix the problem, I went into the dimension, clicked on the key and changed the datasize field of the key column to be 3 so the resulting type is WCHAR(3). Now, just to be sure I update the DSV and reprocess. Same error.
So, after reading up on this error here, I found Dave Wickert's idea of dumping the database to XMLA. So, I have now dumped the database to XMLA and found this key still seems to be of DataSize 1:
<Dimension xsi:type="RegularMeasureGroupDimension">
<CubeDimensionID>My Status Flag</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Lu Status Flag</AttributeID>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>1</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>f_table</TableID>
<ColumnID>my_status_flag</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<Type>Granularity</Type>
</Attribute>
<Attribute>
<AttributeID>Status Flag Desc</AttributeID>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>20</DataSize>
<Source xsi:type="InheritedBinding" />
</KeyColumn>
</KeyColumns>
</Attribute>
</Attributes>
</Dimension>
For now, I am going to try to manually fix this in the XMLA and reimport it, but can someone confirm a bug here? Is there anyplace else I can go in the GUI to change this datasize? It seems to be associate with the measure group and not the dimension per se. The dimension actually processes fine. When I do DSV refresh, it says "No changes have been found." Also, this is a role-playing dimension, so I have to make these changes times 12 for each dimension that this key change applies to.
Keehan
Actually, reimporting doesn't get the change back into BIDS (or VS), it get the cube on the instance changed. How do I get this change back into BIDS so I can continue developing?
Keehan
I solved this by taking all my dimensions that were connected to my role-playing dimension out of the project, processing (for good measure) and then re-importing them. Once this was done, all my keys for the fact table were set to the correct length.
HTH,
Keehan
I've got similar problem after simple update of backend database.
Solution was updating DataSize field in KeysColumns attribute of properties of that dimension attibute. For some reason Analysis Services set its value based on the maximum width of existed entries, not the field width. This is dangerous in general and it looks like I must review all DataSize for all varchar fields.
May be it's just a special case.
Thanks, Andrei.
You have to edit the attribute and increase the datasize to match the maximum length returned by the query in the DSV.
There's a better solution with putting zero to the datasize of the attribute. If a dimension attribute has a zero length size it seems that Analysis services ignores the datasize returned by the query in the DSV, so you can change the relationnal database design without changing the cube (i tried it and it seems to work but i never see any documentation relating this issue).
After much searching i finally realized that this problem was caused by the datasize of the foreign key in the fact table was larger than the size of the Primary key in the dimension table.
I'm not sure about the zero length size, i would be curious if anyone has knowledge of this and what the down side could be.
I had this same problem with an attribute mapped to an NVARCHAR(MAX) column. Looking at the XML for the dimension, I discovered that there was no DataSize specified in the XML for that attribute! I manually added one and the problem is solved.
The weird thing is, this loaded fine on the development server. It wasn't until moving it to the production server that the error started occurring (of course).
Actually, reimporting doesn't get the change back into BIDS (or VS), it get the cube on the instance changed. How do I get this change back into BIDS so I can continue developing?
In BIDS go. File -> New Project and choose this "Import Analysis Services 9.0 Database" option. This will create a new BIDS solution from a specified database instance.
