none
SSAS DSV and underlying Dimension Table Datatype length mismatch

    Question

  • SSAS Error: Value was either too large or too small for Dimension column attribute.

    I have a Dimension attribute data length increased from VARCHAR(10) to
    VARCHAR(20) and modified it in the SSAS Cube to reflect the same by changing the Key column and name column length.

    But I have 2 questions or issues related to it.

    1) In the same dimension a different attribute that has same datatype and length in the Cube as well as underlying table is giving the error "Value was either too large or too small for Dimension column attribute."

    2) How do I deploy the cube? What is the best practice that has to be followed. When I try to deploy only the changes from BIDS I get the error "Database with the same name already exixts". The way I deploy it is rename the existing Database to Old and deploy the cube with changes. For all the modifications I usually import the Analysis Services Database and fix it here.

    I am using SQL Server 2008 R2 and BIDS 2008 R2 environment.

    Thanks.............

    Friday, June 15, 2012 5:04 PM

Answers

  • if the underlying length in the database has changed, first we do a refresh in the DSV. This will sync the mismatch between database and DSV. Once we do this, we need to make sure the lengths are updated both at cube dimension level (Cube-DimenionName-Attribute) and at database dimension level (Dimension Folder-DimensioName-Attribute).

    Refresh in DSV does not make the changes in already defined objects


    vinu

    • Marked as answer by ione721 Wednesday, June 20, 2012 3:10 PM
    Tuesday, June 19, 2012 1:38 AM

All replies

  • Looks like the data length has changes post initial definition. Please check if the data length of the dimension at the DSV, Dimension Level and at the Cube Dimension are all same. If not please change.

    vinu

    Friday, June 15, 2012 5:50 PM
  • Thanks Vinuthan for your reply. I will check the above mentioned areas. Could you also let me know the deployment best practice.

    Regards.......

    Friday, June 15, 2012 6:39 PM
  • Hi Vinuthan, Data length of the dimension in DSV, Dimension Level and Cube Dimension are all the same, but still, I get this 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. Usually what is the practice that has to be followed when something changes for the underlying dimension or attribute.

    Thanks..............
    Monday, June 18, 2012 5:32 AM
  • if the underlying length in the database has changed, first we do a refresh in the DSV. This will sync the mismatch between database and DSV. Once we do this, we need to make sure the lengths are updated both at cube dimension level (Cube-DimenionName-Attribute) and at database dimension level (Dimension Folder-DimensioName-Attribute).

    Refresh in DSV does not make the changes in already defined objects


    vinu

    • Marked as answer by ione721 Wednesday, June 20, 2012 3:10 PM
    Tuesday, June 19, 2012 1:38 AM
  • Thanks Vinuthan that helped.
    Wednesday, June 20, 2012 3:10 PM