Aggregate integrity problems

Has anyone else had any problems with cubes showing incorrect aggregates?
For instance, the sales for a Quarter is showing twice the amount of the 3
months showing. As well, there are values associated to products (lowest
level in dimension) that should not be there. When I re-process the cubes,
the issues are eventually resolved but I feel that I cannot trust any numbers
that the cubes show now.
Thanks in advance
Data Integrity starts at the souce and, once validated, is only maintained
through constraints.
The old adage applies here: Garbage In, Garbage Out.
I'd look at the integrity constraints of the primary sources. If those look
intact, I'd attempt to run manual queries to aggregate the data to see if any
anomolies show up. Finally, if all looks well, the next time some
abnormality shows up in the cube, instead of reprocessing, I'd look at the
source first to see if you could manually replicate the numbers.
I have not seen what you are describing except where there were data errors
in the sources. But if you feel that all has been checked out, I would most
certainly open up a PSS case.
Sincerely,
Anthony Thomas
"fastforward" wrote:

> Has anyone else had any problems with cubes showing incorrect aggregates?
> For instance, the sales for a Quarter is showing twice the amount of the 3
> months showing. As well, there are values associated to products (lowest
> level in dimension) that should not be there. When I re-process the cubes,
> the issues are eventually resolved but I feel that I cannot trust any numbers
> that the cubes show now.
> Thanks in advance
|||I agree, Garbage In, Garbage Out. I have experience more than I want to.
In this case however, the data is clean. It has been checked and double
checked. There are no problems with this set of data. Could this have
anything to do with how I have designed my aggregates or writeback issues?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Data Integrity starts at the souce and, once validated, is only maintained
> through constraints.
> The old adage applies here: Garbage In, Garbage Out.
> I'd look at the integrity constraints of the primary sources. If those look
> intact, I'd attempt to run manual queries to aggregate the data to see if any
> anomolies show up. Finally, if all looks well, the next time some
> abnormality shows up in the cube, instead of reprocessing, I'd look at the
> source first to see if you could manually replicate the numbers.
> I have not seen what you are describing except where there were data errors
> in the sources. But if you feel that all has been checked out, I would most
> certainly open up a PSS case.
> Sincerely,
>
> Anthony Thomas
>
> "fastforward" wrote:
|||Data integrity is always issue number one but if you've checked that out,
then logic comes next. I'd run the aggregate process manually on a few
deminsions with normal T-SQL aggregates. Then, most certainly, I would
consider a more simple design for your cubes to see if the problem clears up.
If so, I'd add complexity, one layer at a time.
If you continue to run into aggregations issues, I would most certainly
consider contacting MS PSS.
Sincerely,
Anthony Thomas
"fastforward" wrote:
[vbcol=seagreen]
> I agree, Garbage In, Garbage Out. I have experience more than I want to.
> In this case however, the data is clean. It has been checked and double
> checked. There are no problems with this set of data. Could this have
> anything to do with how I have designed my aggregates or writeback issues?
> "AnthonyThomas" wrote:

Aggregate integrity problems

Has anyone else had any problems with cubes showing incorrect aggregates?
For instance, the sales for a Quarter is showing twice the amount of the 3
months showing. As well, there are values associated to products (lowest
level in dimension) that should not be there. When I re-process the cubes,
the issues are eventually resolved but I feel that I cannot trust any numbers
that the cubes show now.
Thanks in advanceData Integrity starts at the souce and, once validated, is only maintained
through constraints.
The old adage applies here: Garbage In, Garbage Out.
I'd look at the integrity constraints of the primary sources. If those look
intact, I'd attempt to run manual queries to aggregate the data to see if any
anomolies show up. Finally, if all looks well, the next time some
abnormality shows up in the cube, instead of reprocessing, I'd look at the
source first to see if you could manually replicate the numbers.
I have not seen what you are describing except where there were data errors
in the sources. But if you feel that all has been checked out, I would most
certainly open up a PSS case.
Sincerely,
Anthony Thomas
"fastforward" wrote:
> Has anyone else had any problems with cubes showing incorrect aggregates?
> For instance, the sales for a Quarter is showing twice the amount of the 3
> months showing. As well, there are values associated to products (lowest
> level in dimension) that should not be there. When I re-process the cubes,
> the issues are eventually resolved but I feel that I cannot trust any numbers
> that the cubes show now.
> Thanks in advance|||I agree, Garbage In, Garbage Out. I have experience more than I want to.
In this case however, the data is clean. It has been checked and double
checked. There are no problems with this set of data. Could this have
anything to do with how I have designed my aggregates or writeback issues?
"AnthonyThomas" wrote:
> Data Integrity starts at the souce and, once validated, is only maintained
> through constraints.
> The old adage applies here: Garbage In, Garbage Out.
> I'd look at the integrity constraints of the primary sources. If those look
> intact, I'd attempt to run manual queries to aggregate the data to see if any
> anomolies show up. Finally, if all looks well, the next time some
> abnormality shows up in the cube, instead of reprocessing, I'd look at the
> source first to see if you could manually replicate the numbers.
> I have not seen what you are describing except where there were data errors
> in the sources. But if you feel that all has been checked out, I would most
> certainly open up a PSS case.
> Sincerely,
>
> Anthony Thomas
>
> "fastforward" wrote:
> > Has anyone else had any problems with cubes showing incorrect aggregates?
> > For instance, the sales for a Quarter is showing twice the amount of the 3
> > months showing. As well, there are values associated to products (lowest
> > level in dimension) that should not be there. When I re-process the cubes,
> > the issues are eventually resolved but I feel that I cannot trust any numbers
> > that the cubes show now.
> >
> > Thanks in advance|||Data integrity is always issue number one but if you've checked that out,
then logic comes next. I'd run the aggregate process manually on a few
deminsions with normal T-SQL aggregates. Then, most certainly, I would
consider a more simple design for your cubes to see if the problem clears up.
If so, I'd add complexity, one layer at a time.
If you continue to run into aggregations issues, I would most certainly
consider contacting MS PSS.
Sincerely,
Anthony Thomas
"fastforward" wrote:
> I agree, Garbage In, Garbage Out. I have experience more than I want to.
> In this case however, the data is clean. It has been checked and double
> checked. There are no problems with this set of data. Could this have
> anything to do with how I have designed my aggregates or writeback issues?
> "AnthonyThomas" wrote:
> > Data Integrity starts at the souce and, once validated, is only maintained
> > through constraints.
> >
> > The old adage applies here: Garbage In, Garbage Out.
> >
> > I'd look at the integrity constraints of the primary sources. If those look
> > intact, I'd attempt to run manual queries to aggregate the data to see if any
> > anomolies show up. Finally, if all looks well, the next time some
> > abnormality shows up in the cube, instead of reprocessing, I'd look at the
> > source first to see if you could manually replicate the numbers.
> >
> > I have not seen what you are describing except where there were data errors
> > in the sources. But if you feel that all has been checked out, I would most
> > certainly open up a PSS case.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > "fastforward" wrote:
> >
> > > Has anyone else had any problems with cubes showing incorrect aggregates?
> > > For instance, the sales for a Quarter is showing twice the amount of the 3
> > > months showing. As well, there are values associated to products (lowest
> > > level in dimension) that should not be there. When I re-process the cubes,
> > > the issues are eventually resolved but I feel that I cannot trust any numbers
> > > that the cubes show now.
> > >
> > > Thanks in advance

Aggregate integrity problems

Has anyone else had any problems with cubes showing incorrect aggregates?
For instance, the sales for a Quarter is showing twice the amount of the 3
months showing. As well, there are values associated to products (lowest
level in dimension) that should not be there. When I re-process the cubes,
the issues are eventually resolved but I feel that I cannot trust any number
s
that the cubes show now.
Thanks in advanceData Integrity starts at the souce and, once validated, is only maintained
through constraints.
The old adage applies here: Garbage In, Garbage Out.
I'd look at the integrity constraints of the primary sources. If those look
intact, I'd attempt to run manual queries to aggregate the data to see if an
y
anomolies show up. Finally, if all looks well, the next time some
abnormality shows up in the cube, instead of reprocessing, I'd look at the
source first to see if you could manually replicate the numbers.
I have not seen what you are describing except where there were data errors
in the sources. But if you feel that all has been checked out, I would most
certainly open up a PSS case.
Sincerely,
Anthony Thomas
"fastforward" wrote:

> Has anyone else had any problems with cubes showing incorrect aggregates?
> For instance, the sales for a Quarter is showing twice the amount of the 3
> months showing. As well, there are values associated to products (lowest
> level in dimension) that should not be there. When I re-process the cubes
,
> the issues are eventually resolved but I feel that I cannot trust any numb
ers
> that the cubes show now.
> Thanks in advance|||I agree, Garbage In, Garbage Out. I have experience more than I want to.
In this case however, the data is clean. It has been checked and double
checked. There are no problems with this set of data. Could this have
anything to do with how I have designed my aggregates or writeback issues?
"AnthonyThomas" wrote:
[vbcol=seagreen]
> Data Integrity starts at the souce and, once validated, is only maintained
> through constraints.
> The old adage applies here: Garbage In, Garbage Out.
> I'd look at the integrity constraints of the primary sources. If those lo
ok
> intact, I'd attempt to run manual queries to aggregate the data to see if
any
> anomolies show up. Finally, if all looks well, the next time some
> abnormality shows up in the cube, instead of reprocessing, I'd look at the
> source first to see if you could manually replicate the numbers.
> I have not seen what you are describing except where there were data error
s
> in the sources. But if you feel that all has been checked out, I would mo
st
> certainly open up a PSS case.
> Sincerely,
>
> Anthony Thomas
>
> "fastforward" wrote:
>|||Data integrity is always issue number one but if you've checked that out,
then logic comes next. I'd run the aggregate process manually on a few
deminsions with normal T-SQL aggregates. Then, most certainly, I would
consider a more simple design for your cubes to see if the problem clears up
.
If so, I'd add complexity, one layer at a time.
If you continue to run into aggregations issues, I would most certainly
consider contacting MS PSS.
Sincerely,
Anthony Thomas
"fastforward" wrote:
[vbcol=seagreen]
> I agree, Garbage In, Garbage Out. I have experience more than I want to.
> In this case however, the data is clean. It has been checked and double
> checked. There are no problems with this set of data. Could this have
> anything to do with how I have designed my aggregates or writeback issues?
> "AnthonyThomas" wrote:
>

aggregate function

The following statement fail to generate my expected result:
select A.part_id, sum( (B.total + sum(C.amount)) * D.rate)
from A, B, C , D
where B.part_id = A.part_id and C.line_id = B.line_id and convert(char(6),
B.date, 112) = D.code
It generates the error of "Cannot perform an aggregate function on an
expression containing an aggregate or a subquery.".
Could anyone please give me a hand?
Thanks in advance.
SC
----
DDL:
create Table A
( part_id char(1) primary key,
description varchar(1),
)
create Table B
(
part_id char(1),
date datetime,
line_id int,
total numeric(10,2),
primary key (part_id, date)
)
create Table C
(
line_id int,
seq int,
amount numeric(10,2)
primary key (line_id, seq )
)
create Table D
(
code char(6) primary key,
rate numeric(10,2)
)
DML:
insert into A values ( 'A', 'A' )
insert into A values ( 'B', 'B' )
insert into A values ( 'C', 'C' )
insert into B values ( 'A', '2006/01/01', 1, 10)
insert into B values ( 'A', '2006/02/01', 2, 5)
insert into B values ( 'B', '2006/01/01',3, 12)
insert into B values ( 'B', '2006/01/03',4, 10)
insert into B values ( 'B', '2006/02/01',5, 2)
insert into C values ( 1, 1, 3)
insert into C values ( 1, 2, 4)
insert into C values ( 2, 1, 5)
insert into C values ( 3, 1, -5)
insert into C values ( 3, 2, 2)
insert into D values ('200601', 1.1)
insert into D values ('200602', 1.5)
Expect result:
A 33.7
B 23.9I didn't spend time to completely work this out, but it should get you movin
g in the right direction. (The A result is what you desired, but the B resul
t is not...)
Sum Table C as a derived table (named 'C2') and THEN JOIN on it.
SELECT
A.Part_ID
, sum(( B.Total + C2.Amount ) * D.Rate )
FROM A
JOIN B
ON A.Part_ID = B.Part_ID
JOIN ( SELECT
Line_ID
, sum( Amount ) AS 'Amount'
FROM C
GROUP BY Line_ID
) C2
ON C2.Line_ID = B.Line_ID
JOIN D
ON convert( char(6), B.[Date], 112) = D.Code
GROUP BY A.Part_ID
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Squirrel" <xsquirrelx@.hotmail.com> wrote in message news:OwO5qWnlGHA.4212@.TK2MSFTNGP03.phx
.gbl...
> The following statement fail to generate my expected result:
> select A.part_id, sum( (B.total + sum(C.amount)) * D.rate)
> from A, B, C , D
> where B.part_id = A.part_id and C.line_id = B.line_id and convert(char(6),
> B.date, 112) = D.code
>
> It generates the error of "Cannot perform an aggregate function on an
> expression containing an aggregate or a subquery.".
>
> Could anyone please give me a hand?
>
> Thanks in advance.
>
> SC
> ----
> DDL:
> create Table A
> ( part_id char(1) primary key,
> description varchar(1),
> )
> create Table B
> (
> part_id char(1),
> date datetime,
> line_id int,
> total numeric(10,2),
> primary key (part_id, date)
> )
> create Table C
> (
> line_id int,
> seq int,
> amount numeric(10,2)
> primary key (line_id, seq )
> )
> create Table D
> (
> code char(6) primary key,
> rate numeric(10,2)
> )
>
> DML:
> insert into A values ( 'A', 'A' )
> insert into A values ( 'B', 'B' )
> insert into A values ( 'C', 'C' )
> insert into B values ( 'A', '2006/01/01', 1, 10)
> insert into B values ( 'A', '2006/02/01', 2, 5)
> insert into B values ( 'B', '2006/01/01',3, 12)
> insert into B values ( 'B', '2006/01/03',4, 10)
> insert into B values ( 'B', '2006/02/01',5, 2)
> insert into C values ( 1, 1, 3)
> insert into C values ( 1, 2, 4)
> insert into C values ( 2, 1, 5)
> insert into C values ( 3, 1, -5)
> insert into C values ( 3, 2, 2)
> insert into D values ('200601', 1.1)
> insert into D values ('200602', 1.5)
>
> Expect result:
> A 33.7
> B 23.9
>
>|||Hello, Squirrel
The following query returns the expected results:
SELECT Y.part_id, SUM(Y.AnotherSum*D.rate) as TheSum
FROM (
SELECT X.part_id, X.code, SUM(X.TotalPlusAmount) as AnotherSum
FROM (
SELECT B.part_id, CONVERT(char(6),B.date,112) AS code,
B.total+ISNULL((
SELECT SUM(C.amount)
FROM C WHERE B.line_id=C.line_id
),0) as TotalPlusAmount
FROM B
) X GROUP BY X.part_id, X.code
) Y INNER JOIN D ON Y.code = D.code
GROUP BY Y.part_id
Razvan|||Thanks, Razvan.
Frankly, your SQL statement is complicated to me. would you kindly explain
it to me?
Thanks again.
SC
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1151130774.295568.163480@.m73g2000cwd.googlegroups.com...
> Hello, Squirrel
> The following query returns the expected results:
> SELECT Y.part_id, SUM(Y.AnotherSum*D.rate) as TheSum
> FROM (
> SELECT X.part_id, X.code, SUM(X.TotalPlusAmount) as AnotherSum
> FROM (
> SELECT B.part_id, CONVERT(char(6),B.date,112) AS code,
> B.total+ISNULL((
> SELECT SUM(C.amount)
> FROM C WHERE B.line_id=C.line_id
> ),0) as TotalPlusAmount
> FROM B
> ) X GROUP BY X.part_id, X.code
> ) Y INNER JOIN D ON Y.code = D.code
> GROUP BY Y.part_id
> Razvan
>|||Squirrel wrote:
> Frankly, your SQL statement is complicated to me. would you kindly explain
> it to me?
Read it from the inner-most query, like this:
First, we compute B.Total+SUM(C.Amount) for each row in B (using a
correlated subquery to get the sum of C.Amount, wrapped in an ISNULL,
just in case there are no rows in table C for a certain line_id).
Then we compute AnotherSum, as the sum of the TotalPlusAmount (the
value computed above), for each part_id and X.code; we defined earlier
that X.code is the month/year of B.date.
Then we join the above result to table D, on the column code, to get
the rate corresponding to each month/year. We compute TheSum as the sum
of AnotherSum (the value calculated above), multiplicated by the
corresponding rate, for each part_id.
Razvan|||Hi There,
You may like to try this one out exactly what razvan suggested. The
join of four tables seems reductant.
1) First taking B as base table find the sum(amount from C table ofr
lineids in B)
2) Join the derived table with D on code
3) Apply your formula (b.total+ sum(c.amt) )*rate
Select Der1.Part_ID , Sum(Tot) From (
Select Der.Part_id,Sum(Der.Total+Isnull(X,0))*D.Rate Tot From
(
Select B.part_id , b.Total ,
(
Select sum(C.amount) from C where C.line_id=B.line_id
) X ,
convert(char(6),date,112) Code from B
) Der
Inner Join D On D.Code=Der.Code
group by Der.Part_Id,D.rate
) Der1 Group by Part_id
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Squirrel wrote:
> Thanks, Razvan.
> Frankly, your SQL statement is complicated to me. would you kindly explain
> it to me?
> Thanks again.
> SC
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1151130774.295568.163480@.m73g2000cwd.googlegroups.com...