Re: On what OLAP can and what OLAP can't - A little problem

Hi Jan. Thanks for the input. "semijoin" ? hmm I have to take a look at that ...

I make a first example for discussion. Let take 5 simple tables. Let's avoid talking about name, key issue or pathological design issue. Let's just focus on the abstract reporting problem.

CLIENT
Name
U1
U2
U3

LINE_A
ItemA

A_1
A_2
A_3
A_4

B_1
B_2
B_3

TRANS_A
Name	ItemA	Amount
U1	A_1	10
U1	A_3	20
U1	A_1	20
U1	A_3	10
U1	A_1	20
U3	A_2	10

TRANS_B
Name	ItemB	Amount
U1	B_1	20
U1	B_1	10
U1	B_3	10
U1	B_1	20
U1	B_2	10
U3	B_1	20

Assume one wants this simple report (given the above):

Name | ItemA | ItemB | TotalAmountItemA | TotalAmountItemB

One first question is what is the best way to create this kind of result. And
for a general scheme which might incorporate similar problems what is the best strategy to build a query that provides the expected info. Other examples might include functions on "dimension" tables.

-P

Jan Hidders ha scritto:

> pamelafluente_at_libero.it schreef:
>
> >
> > Make it simple. Assume you have a few tables. Assume that there are a
> > few relationships and that for instance if you have 2 table A, B in
> > 1-N relationship, you
> > have on table A defined some function that is, say, "replication
> > sensitive", such as count (not count distinct) or sum. When whe make
> > some join of these tables and compute such functions we obtain an
> > incorrect calculation of the functions due to record replication.
> > Leader softwares such as Business Objects are able to devise some union
> > of subquery to avoid that replication problem. Experiment show that
> > such software are usually able to deal with that if functions are on
> > "fact" table (say on the N side of the relationship), but they seem to
> > have big problems when the functions are applied on the dimension table
> > (say on side 1 of relationship).
>
> What do you mean by "algorithm"? Do you want the SQL expression or a
> program that would implement that expression?
>
> For now, if I understand your question correctly, it seems the magic
> word you are looking for is "semijoin". There are several algorithms
> for that operator and the choice depends on several factors such as the
> presence of indexes, selectivity of join conditions,sizes of buffers,
> sizes of the involved tables, et cetera.
>
> -- Jan Hidders
Received on Wed Sep 13 2006 - 14:16:35 CEST