Negative values being summed incorrectly

I'm wondering if anyone has seen this before.

I have a cube in which the data is not being rolled up correctly when the values include negative numbers. The fact table and dimension tables are on an Oracle Server (9i). The fact table looks like:

Column Name

Data Type

Nullable

EDP_NO

NUMBER(10,0)

Yes

MONTH_YYYYMM

VARCHAR2(6 Bytes)

Yes

COGS

NUMBER

Yes

INV_VALUE

NUMBER

Yes

QTY_SOLD

NUMBER(10,0)

Yes

ITEM_NO

VARCHAR2(20 Bytes)

Yes

The dimension table looks like:

olumn Name

Data Type

Nullable

EDP_NO

NUMBER(10,0)

No

VENDOR_NAME

VARCHAR2(41 Bytes)

Yes

AP_VENDOR_NAME

VARCHAR2(41 Bytes)

Yes

DCS

VARCHAR2(4 Bytes)

Yes

DEPT_NAME

VARCHAR2(49 Bytes)

Yes

CLASS_NAME

VARCHAR2(49 Bytes)

Yes

SUB_CLASS_NAME

VARCHAR2(49 Bytes)

Yes

CATEGORY_NAME

VARCHAR2(49 Bytes)

Yes

BUYER

VARCHAR2(160 Bytes)

Yes

AP_VENDOR_NO_UI

VARCHAR2(10 Bytes)

Yes

VENDOR_NO_UI

VARCHAR2(10 Bytes)

Yes

ITEM_NAME

VARCHAR2(20 Bytes)

Yes

PLANNER

VARCHAR2(8 Bytes)

Yes

When I create the cube and browse

the data down to the most granular level (EDP_NO),and when the QTY_SOLD measure is less than

zero,SSAS reports it as being -1

larger.For example,if the value in the table is -3,then the cube browser reports it as -4.Summarized values are also off by a similar

amount.That is,if in the table the data is: -4, -3, -1,then the cube will report the total as

11.

I’m wondering if you’ve ever run

into a similar situation.I’ve verified

that the aggregation being used is SUM.

What’s weird about this is that I’m

trying to recreate a cube that already exists in SQL Server 2000.If I migrate the cube to 2005,it works correctly.It’s only when I create the cube manually

that I have this problem.

I appreciate any assistance you can offer.

Thanks,

Mike Hayes

Hi Mike,

Maybe there is incorrect integer truncation, when converting from the Oracle data type: NUMBER(10,0). Since a migrated cube works correctly, could you compare the DataType for the QTY_SOLD Measure in the 2 cubes? The possible DataType values are:

http://msdn2.microsoft.com/es-es/library/ms129408.aspx

>>

SQL Server 2005 Books Online

DataType Element (ASSL)

Defines the data type of the associated element.

...

The values for DataType are defined in the System.Data.OleDb.OleDbType enumeration. However, only the enumeration values in the following table are valid in the DataType element.

Value

Description

BigInt

A 64-bit signed integer. This data type maps to the Int64 data type in Microsoft .NET Framework and the DBTYPE_I8 data type in OLE DB.

Bool

A Boolean value. This data type maps to the Boolean data type in the .NET Framework and the DBTYPE_BOOL data type in OLE DB.

Currency

A currency value ranging from -2⁶³ (or -922,337,203,685,477.5808) to 2⁶³-1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. This data type maps to the Decimal data type in the .NET Framework and the DBTYPE_CY data type in OLE DB.

Date

Date data, stored as a double-precision floating point number. The whole portion is the number of days since December 30, 1899, while the fractional portion is a fraction of a day. This data type maps to the DateTime data type in the .NET Framework and the DBTYPE_DATE data type in OLE DB.

Double

A double-precision floating point number within the range of -1.79E +308 through 1.79E +308. This data type maps to the Double data type in the .NET Framework and the DBTYPE_R8 data type in OLE DB.

Integer

A 32-bit signed integer. This data type maps to the Int32 data type in the .NET Framework and the DBTYPE_I4 data type in OLE DB.

Single

A single-precision floating point number within the range of -3.40E +38 through 3.40E +38. This data type maps to the Single data type in .NET Framework and the DBTYPE_R4 data type in OLE DB.

SmallInt

A 16-bit signed integer. This data type maps to the Int16 data type in the .NET Framework and the DBTYPE_I2 data type in OLE DB.

TinyInt

An 8-bit signed integer. This data type maps to the SByte data type in the .NET Framework and the DBTYPE_I1 data type in OLE DB.

UnsignedBigInt

A 64-bit unsigned integer. This data type maps to the UInt64 data type in .NET Framework and the DBTYPE_UI8 data type in OLE DB.

UnsignedInt

A 32-bit unsigned integer. This data type maps to the UInt32 data type in the .NET Framework and the DBTYPE_UI4 data type in OLE DB.

UnsignedSmallInt

A 16-bit unsigned integer. This data type maps to the UInt16 data type in the .NET Framework and the DBTYPE_UI2 data type in OLE DB.

WChar

A null-terminated stream of Unicode characters. This data type maps to the String data type in the .NET Framework and the DBTYPE_WSTR data type in OLE DB.

Inherited

The data type of the DataItem contained in the Source element of the Measure element.

Note:

Applicable only to Measure elements.

>>

|||Deepak,

Thanks for the information. I think you're on the right track. The data type of the column in the migrated cube is Double, and the data type of the created cube is Int64. They should both handle the math correctly. I'm in the process of testing this. I'm trying a different data source (using oledb drivers instead of the .net drivers). If that doesn't work, I'll explicitly change the data type using a query instead of a direct link to the table.

I appreciate the info. I'll let you know what happens.

Mike