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:
>

Advice on table design which will allow me to enforce integrity

Hi,
I have two tables Table A and B, below with some dummy data...

Table A (contains specific unique settings that can be requested)

Id, SettingName
1, weight
2, length

Table B (contains the setting values, here 3 values relate to weight
and 1 to length)

Id, Brand, SettingValue
1, A, 100
1, B, 200
1, null, 300
2, null, 5.3

(There is also a list of Brands available in another table). No primary
keys / referential integrity has been setup yet.

Basically depending upon the Brand requested a different setting value
will be present. If a particular brand is not present (signified by a
null in the Brand column in table B), then a default value will be
used.
Therefore if I request the weight and pass through a Brand of A, I will
get 100
If I request the weight but do not pass through a brand (i.e. null) I
will get 300.

My question is, what kind of integrity can I apply to avoid the user
specifying duplicate Ids and Brands in table B. I cannot apply a
composite key on these two fields as a null is present. Table B will
probably contain about 50 rows and probably 10 of them will be brand
specific. The reason its done like this is in the calling client code I
want to call some function e.g.
getsetting(weight) ... result = 300
Or if it is brand specific
getsetting(weight,A) .... result = 100

Any advice on integrity or table restructuring would be greatly
appreciated. Its sql 2000 sp3.
Thanks
bradAs you have already realized, TableB isn't even in First Normal Form.
Attribute Value models like this are seldom viable and you haven't
stated any excuse not to use the standard, normalized approach:

CREATE TABLE TableB (id INTEGER NOT NULL, brand CHAR(1) DEFAULT 'Z' NOT
NULL REFERENCES Brands (brand), weight INTEGER NOT NULL, length INTEGER
NOT NULL, PRIMARY KEY (id,brand))

Either add the default ("Z" here) brand to the brands table, or if you
prefer not to do that, put the brand-specific info into a separate
table:

CREATE TABLE TableB (id INTEGER NOT NULL, default_weight INTEGER NOT
NULL, default_length INTEGER NOT NULL, PRIMARY KEY (id))

CREATE TABLE TableBBrands (id INTEGER NOT NULL REFERENCES TableB (id),
brand CHAR(1) NOT NULL REFERENCES Brands (brand), weight INTEGER NOT
NULL, length INTEGER NOT NULL, PRIMARY KEY (id,brand))

Personally, I would lean toward the former design. This meets all the
requirements that you have specified as far as I can see.

--
David Portas
SQL Server MVP
--|||i would go with two tables B1(id ,brand , value) with PK (id,brand) and
B2(id,value) PK (id)
but i think that more appropriate solution is to have separate table
for each "setting type" e.g.
WEIGHTS, LENGTHS, VOLUMES where you can have appropriate data type for
each setting

HTH, Strider|||> i think that more appropriate solution is to have separate table
> for each "setting type" e.g.
> WEIGHTS, LENGTHS, VOLUMES

Do you mean separate COLUMNS? Why would you create separate tables for
each attribute?

--
David Portas
SQL Server MVP
--|||I should have also said that Table A will grow in size over time (i.e.
60 different settings), so you can have many settings, i.e. a user
could add additional settings in here, therefore making these as
columns would not be ideal. If possible I would also prefer to not have
to have a dummy brand.

David Portas wrote:
> > i think that more appropriate solution is to have separate table
> > for each "setting type" e.g.
> > WEIGHTS, LENGTHS, VOLUMES
> Do you mean separate COLUMNS? Why would you create separate tables
for
> each attribute?
> --
> David Portas
> SQL Server MVP
> --|||>Why would you create separate tables for
>each attribute?

i don't know, perhaps transient brain disorder ;)|||Why can't you implement the necessary change control procedures to add
columns as the users require them? Allowing users the unfettered
ability to add new "attributes" to a system is a recipe for creating an
unusable mass of redundant and inconsistent data. Do you really expect
users to go through the process of identifying and eliminating
functional dependencies before they add new attributes? On the whole,
users aren't good database architects. At least if they were they would
work with proper tables and columns, constraints and keys - things
which aren't possible under your model.

Your question was how to enforce integrity but I don't see any
integrity in your design at all.

--
David Portas
SQL Server MVP
--|||Yup, I agree all valid points there David. Thanks for your input on
this one.
Brad

David Portas wrote:
> Why can't you implement the necessary change control procedures to
add
> columns as the users require them? Allowing users the unfettered
> ability to add new "attributes" to a system is a recipe for creating
an
> unusable mass of redundant and inconsistent data. Do you really
expect
> users to go through the process of identifying and eliminating
> functional dependencies before they add new attributes? On the whole,
> users aren't good database architects. At least if they were they
would
> work with proper tables and columns, constraints and keys - things
> which aren't possible under your model.
> Your question was how to enforce integrity but I don't see any
> integrity in your design at all.
> --
> David Portas
> SQL Server MVP
> --|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

2) Google EAV and attribute splitting" design flaws before you destroy
your database. It is virtually impossible to have any data integrity
with this "design" becuase of the need to write a complete RDBMS engine
in SQL constraints.

3) The thing falls apart in about one year of operation. That is based
on fixing these things as a consultant.

4) You need help from someone who knows even a little bit about data
modeling. You have not even gottent o the basic normal forms yet.|||[posted and mailed, please reply in news]

(obhayes@.hotmail.com) writes:
> My question is, what kind of integrity can I apply to avoid the user
> specifying duplicate Ids and Brands in table B. I cannot apply a
> composite key on these two fields as a null is present. Table B will
> probably contain about 50 rows and probably 10 of them will be brand
> specific. The reason its done like this is in the calling client code I
> want to call some function e.g.
> getsetting(weight) ... result = 300
> Or if it is brand specific
> getsetting(weight,A) .... result = 100

I would add a dummy primary key to TableB, possibly an IDENTITY column.
When I would add a UNIQUE constraint on (Id, Brand). A UNIQUE constraint
does permit for NULL values, but only one NULL.

(It's a bit amazing that a thread with so many replies did not include
this simple and straigtforward suggestion.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp