Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?

[post here a copy of the reply for readers' convenience]

Hi Jan, thanks,

> Do you really want to repeat in
> it the information for each ItemB for a Name for each ItemA for that
> Name? Why so much redundancy?

It is a perfectly legal request a(ny) user can make. The user want to list the items and the total amount. But the point is not this. The point is to find a general method to deal with any request of report. It's an abstract problems which is not related with semantic value of items. It must only depend on table structures, relationships, aggregate function properties and fields involved in report.

The point is not to discuss this specific example. Examples are uuseful to fix ideas or find counterexamples. This is a "Theory" group. So I am expecting abstraction and a systematic method to build these queries.

> I can of course give you the SQL statement to

You may have missed the preceding post where such a statement has already been provided. For readers' convenience I have repacked this in a new thread:

http://groups.google.it/group/comp.databases.theory/browse_frm/thread/538735631f141027?hl=it

Please respond there so that other can follow.

I >suggest you first give
> what you think is the content of your report in the example you gave.

That's the point. Content is unimportant. What matters are only formal the properties of involved objects.
The input of the problem are:

• Tables (in abstract sense, as a list of field) and a list of their field including datatype
• Relationships
• Structure (list of fields) of the report with properties of involved functions:
  • Function is aggregate: Yes/No
  • Function is sensitive to replication (eg. sum): Yes/no

That's it. Simple to state.

-P

pamelafluente_at_libero.it ha scritto:

> FreeData ha scritto:
>
> Hi FreeData, thanks!
>
> > Two options for you:
> > 1. Make your dimension Type 1 which will remove duplicates.
>
> Sorry? Not sure to get what you mean.
>
> > 2. Remove your measure from your dimension.
>
> hmmm, You must assume a programmer's perspective. Not a user's.
> The program must do what the user says not viceversa.
>
> The terms of the problem are: given a set of tables and relationships
> and given a set of fields, possibly equipped with aggregate function,
> which result in the report, determine the correct split of
> relationships and the subqueries that will yield the desired result.
> You can see an implementation of that, for instance, in Business
> Objects (the well know BI program) [I believe that implementation fails
> under some conditions]
>
> For instance in my example the first query is a wrong result. Report
> computation is actually wrong. The second query (a UNION of 2
> subqueries) should be the best result, given the user request).
>
> -P
Received on Thu Sep 14 2006 - 16:59:12 CEST