Measures question

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

--
16 Sep 2004 07: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
BEGIN
Declare @Qty decimal(20,10)
set @qty = Cast(Replace(Replace(Replace(@varqty, ',', '*'), '.',''), '*', '.') as decimal(20, 10))
return(@qty)
END

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.



PaulMcKibben
New Member
New Member

--
24 Sep 2004 11:24 AM
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
---