Cube summing/aggregating values incorrectly???
-
I have a very simple cube with only a few dimensions. The cube consistenly sums up the measures wrong. The values are always less than what they should be. These are 'float' data type fields. When you sum the values in the fact table in a regular SQL query using the line of business dimension i'm using in the cube, the values are correct. The cube values are always incorrect.
anybody ever seen this, or have any suggestions? I'm lost....
-
Can you give us the schema of the table and the schema of your cube design? Is the summed field nullable?
is this 2000 or 2005?
-
Sorry it took me so long to reply. It is SQL 2000 and the field is nullable. Here's the DDL.
CREATE TABLE [dbo].[DateDim](
[DateKey] [int] NOT NULL,
[ActualDate] [datetime] NOT NULL,
[Year] [int] NOT NULL,
[Month] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[DualStatusDim](
[DualKey] [int] NOT NULL,
[DualEligibleFlag] [varchar](1) ,
[DualEnrolledFlag] [varchar](1) NULL
)
CREATE TABLE [dbo].[LOBDim](
[LOBKey] [int] NOT NULL,
[LOB] [varchar](2) NULL,
[LOBDescription] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MemFact](
[DateKey] [int],
[LOBKey] [int],
[DualKey] [int],
[membercount] [float] NULL,
[remitamount] [float] NULL,
[RemitPartAB] [float] NULL,
[RemitPartD] [float] NULL
) ON [PRIMARY]
The weird thing is...The One Line of Business adds up correctly, but the other doesn't consistently. If you manually sum them in the table, the amounts are correct. Any ideas? I'm a little lost on this one. Any questions, just ask. Thanks!
-
When you do your select, do you include all the dimensions or are you just summing on the fact table? I am wondering if something is introducing duplicates, causing agregation to be incorrect.
I assume an equivilent query to what the cube processing is doing would be
SELECT biz.LOB
, SUM(mem.membercount) AS membercount
, SUM(mem.remitamount) AS remitamount
,SUM(mem.RemitPartAB) AS remitpartab
, SUM(mem.RemitPartD) AS RemitPartD
FROM dbo.MemFact mem
INNER JOIN dbo.DualStatusDim dual ON mem.DualKey = dual.DualKey
INNER JOIN dbo.DateDim dt ON mem.DateKey = dt.DateKey
INNER JOIN dbo.LOBDim biz ON mem.LOBKey = biz.LOBKey
GROUP BY dbo.LOBDim.LOB
-
hhmmm...actually I just ran
SELECT biz.LOB
, SUM(mem.membercount) AS membercount
, SUM(mem.remitamount) AS remitamount
,SUM(mem.RemitPartAB) AS remitpartab
, SUM(mem.RemitPartD) AS RemitPartD
FROM dbo.MemFact mem
INNER JOIN dbo.LOBDim biz ON mem.LOBKey = biz.LOBKey
GROUP BY dbo.LOBDim.LOB
and just that much made the one line of business come up short across the board....i'll look into it.
what do you think Cliff?
-
cliff, i'm sorry, that was incorrect. I was using the wrong test table. The INNER JOIN I listed above did produce the correct results for each line of business...man this is weird...
-
cliff, think I got it. Found some NULLS in the date column in the fact table. Looks like the culprit. I'll use the COELESCE Function to handle them. Always good to talk it through though with somebody. thanks!
-Pat
-
Cool. Figures it was a NULL. That bites me all the time.
-
Please help...
I have a cube that is giving incorrect values via excel. With a sum and group-by query it gives me the right answers. The relations in the cube editor is right. I do not have null values. What else can it be?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply