Measures question

Last Post 24 Sep 2004 12:24 PM by PaulMcKibben. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

16 Sep 2004 08:32 AM
I have two cubes that show incorrect values for a measure at all levels. For example, one customer shows 22 as a shipped quantity value in the AS cube, but I execute a SQL query for the same customer with a sum function and it shows a value of 10.

Let me give you the background please.

The measures are calculated with the aggregrate function of SUM. All fields that I am using as measures are Decimal(20,10) in SQL. To load the values into the SQL table I have a UDF that converts a varchar field to a decimal. The quantity conversion UDF code is below:

CREATE FUNCTION EuroQtyFormatting (@varqty varchar(10))
RETURNS decimal(20,10) AS
Declare @Qty decimal(20,10)
set @qty = Cast(Replace(Replace(Replace(@varqty, ',', '*'), '.',''), '*', '.') as decimal(20, 10))

The quantity field that I am converting is a varchar(20) field. I realize that that might be an issue, but when I query the table the varchar quantity field value appears to be the same as the decimal quantity field.

Any assistance would be greatly appreciated.

New Member
New Member

24 Sep 2004 12:24 PM
I am replying to my own post in case someone has the same issue.

The problem was because the dimension table had multiple rows for items in the table. For example: Customer #1 had multiple rows in the dimension table.

The cube was built with a star schema and needed a one to many relationship between the dimension table and the fact table.

Acceptable Use Policy