locked
negative value in int dataypes RRS feed

  • Question

  • I am creating a cube over Teradata platform with data almost close to 100 million records. my biggest concern over here is the signed data types which overflow integer values result storing a negative value on grand total or some at year level data.  why this happens is because

     

    In signed datatypes, the result is a little different and results in some seemingly weird behaviour:

    Positive limit: 32767 or 0111 1111 1111 1111
    Too big: 32768 or 1000 0000 0000 0000
    What's stored: -32768

    Why's that? It's because of "2's compliment," which is how negative numbers are represented in binary. To make a long story short, the first half of the range (0 thru 0111 1111 1111 1111) is used for positive numbers in order of least to greatest. the second half of the range is then used for negative numbers in order of least to greatest. so the negative range for a signed 2-byte short is -32768 thru -1, in that order.

     

    i am not sure how to control this behavior in  SSAS.

     

    Please advise on a a suggestion that could be taken care of.

     

    Thank you

     

     

     

     

    Tuesday, August 12, 2008 7:26 PM

Answers

  • I don't think you can change this in the DSV, but you should be able to do it in the cube by following the instructions that Deepak posted.

    Tuesday, August 12, 2008 9:25 PM
  • That error may be because you may not have changed the Source Data Type in SSAS. You got to make following changes:

    1. Select the measure property and change the DataType to BigInt (Perhaps, you tried doing this and got error... but you have to make one more change mentioned below in step 2)

    2. Expand the Source Property , there you will have to change the DataType again to BigInt

    Now deploy the Cube!

    I've tried it, It's working. Hope it works for you as well.
    • Proposed as answer by Shashi J Friday, July 3, 2009 1:38 PM
    • Marked as answer by ns100 Sunday, November 15, 2009 4:10 PM
    Friday, July 3, 2009 1:30 PM

All replies

  • You could try changing the integer data type to "bigint", which is stored in 64 bits, as compared to 32 bits for int. See this blog entry for a detailed discussion:

     

    SSAS: Beware of measure data types

    ...

    Basically the database and the DSV were increased to a bigint data type, but the issue was still occurring. What I believe is happening here is that the measure in the cube was set with an int data type. To fix this, edit the cube, go into the cube structure tab, click on the measure in question and change its data type from int to bigint. The re-process the cube.

    ...

     

    Tuesday, August 12, 2008 8:06 PM
  • Deepak

     

    The problem is my source data does not suppport big int , Can i just change this at cube level , i have been trying to do this but it throw me error the data type should be the same as data source.

     

    please advise

     

    Thank you

     

     

    Tuesday, August 12, 2008 8:27 PM
  • I don't think you can change this in the DSV, but you should be able to do it in the cube by following the instructions that Deepak posted.

    Tuesday, August 12, 2008 9:25 PM
  • That error may be because you may not have changed the Source Data Type in SSAS. You got to make following changes:

    1. Select the measure property and change the DataType to BigInt (Perhaps, you tried doing this and got error... but you have to make one more change mentioned below in step 2)

    2. Expand the Source Property , there you will have to change the DataType again to BigInt

    Now deploy the Cube!

    I've tried it, It's working. Hope it works for you as well.
    • Proposed as answer by Shashi J Friday, July 3, 2009 1:38 PM
    • Marked as answer by ns100 Sunday, November 15, 2009 4:10 PM
    Friday, July 3, 2009 1:30 PM