Inconsistent negative numerical values in SQL2K database table

From: Earl Newcomer (EarlNewcomer_at_discussions.microsoft.com)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 15:39:01 -0800

I have a case where the sum of a column containing positive, zero and
negative values is correct in Query Analyzer but the values in the column do
not contain a '-' (minus sign). The rows sort as expected but they do not
have a minus sign. I can update a positive row value by multiplying it by -1
and that value shows with a minus sign. The newly updated row sorts with the
other negative values but it is the only row with a minus sign.

This "condition" came to light when a cube built on the fact table (where
this is occurring) was not summing correctly. The cube sees all values as
positive as if an absolute function was being used. When the one positive
row was updated to a negative value as described above, the cube recognized
the negative value and the measure changed accordingly. What is it in the
SQL Server table or in the cube interpretation of the table values that is
causing this? Is there a workaround?

Another twist. The table, pmix_fact1, with which we see this behavior was
copied from another SQL Server 2000 server. The cubes built on that table
sum correctly. I suspect the process of copying the table may be introducing
this behavior. Has anyone experienced anything like this?

I have seen this once before when using an ODBC driver to copy table rows
from an Oracle 7.3.4 database to SQL Server 2000 where the values were all
positive after the copy. I changed to the latest Oracle driver (9.0.1) that
would still work with Oracle 7.3.4 and this resolved that problem. I have
not seen a case where a SQL Server to SQL Server copy would confuse numeric
values.

Sample data from SQL Server 2000 SP3 Query Analyzer
Extended_amount (sorted descending) No minus sign at the bottom.
8.99
7.99
4.08
3.19
2.99
2.98
2.59
2.29
2.19
2.19
1.99
1.79
1.59
1.49
.69
.00
.00
.00
.00
.00
.00
.00
.00
.00
.00
.00
.00
2.29
2.98
2.98
3.19
4.08
4.08
------
27.43

The cube reports: 66.63

Update the 8.99 to 8.99 * -1 and the Query Analyzer results are:
Extended_amount
7.99
4.08
3.19
2.99
2.98
2.59
2.29
2.19
2.19
1.99
1.79
1.59
1.49
.69
.00
.00
.00
.00
.00
.00
.00
.00
.00
.00
.00
.00
2.29
2.98
2.98
3.19
4.08
4.08
-8.99
------
9.45

The cube reports: 48.65

Suggestions are appreciated.

Earl Newcomer

Relevant Pages

  • OLAP not recognizing negative SQL Server table values
    ... not contain a '-' (minus sign). ... This "condition" came to light when a cube built on the fact table (where ... SQL Server table or in the cube interpretation of the table values that is ... Sample data from SQL Server 2000 SP3 Query Analyzer ...
    (microsoft.public.sqlserver.olap)
  • Re: Inconsistent negative numerical values in SQL2K database table
    ... >and that value shows with a minus sign. ... The cube sees all values as ... >copied from another SQL Server 2000 server. ... >The cube reports: 66.63 ...
    (microsoft.public.sqlserver.server)
  • Re: long processing time
    ... The AS Operations Guide was just recently posted and got dropped from their ... >> 1) Have you ran the Optimize Schema wizard in the Cube Editor? ... >> inner join between the fact table and *all* of the dimension tables. ... >>> through SQL server. ...
    (microsoft.public.sqlserver.olap)
  • Re: long processing time
    ... currently it is processing 2 partitions in parallel. ... >1) Have you ran the Optimize Schema wizard in the Cube ... >the RDBMS (SQL Server or Oracle), ... >> The cube which took 9 days to process has time dimension ...
    (microsoft.public.sqlserver.olap)
  • Re: how to hold PCBs at right angles to each other?
    ... They need to be at 90 degrees plus or minus a tenth of a ... Initial production numbers will be fairly low so ... How about a cube of G10? ... you can have a 3D circuit board. ...
    (sci.electronics.design)