Stiched Queries on large tables

[Rosanero4Ever]

Hi all,

I have a FM relational model based on this simple schema

[FACT Table A] ------------- [DIM Table D] -------- [FACT Table B]

When I create a report using a measure of Facte Table A and a measure of Fact Table B and an item of DIM Table D a stiched query is provided.
Ok....I know this behavior but it's a big problem when the two fact tables are huge (about 3GB each one)
In this case, a full join is produced on millions of record  (each fact table contains about 8 miliions of record) and the query is very very slow!!!
In fact, the sql query produced has a full join which defines a cartesian product (8mln * 8 mln of record = 16k billions of records!)

For your experience, how can I solve this problem?
Many thanks in advance

[MFGF]

Hi all,

I have a FM relational model based on this simple schema

[FACT Table A] ------------- [DIM Table D] -------- [FACT Table B]

When I create a report using a measure of Facte Table A and a measure of Fact Table B and an item of DIM Table D a stiched query is provided.
Ok....I know this behavior but it's a big problem when the two fact tables are huge (about 3GB each one)
In this case, a full join is produced on millions of record  (each fact table contains about 8 miliions of record) and the query is very very slow!!!
In fact, the sql query produced has a full join which defines a cartesian product (8mln * 8 mln of record = 16k billions of records!)

For your experience, how can I solve this problem?
Many thanks in advance

Hi,

In a multi-fact situation, a stitch query is exactly the right thing to do. You shouldn't be seeing a Cartesian product, though. You should see each dimensional value from Table D along with its aggregated measure values from Table A and Table B. Is this what you see? If so, Cognos is doing exactly what it's supposed to. If not, can you share the Cognos SQL of your query so we can see what's happening? If your query is delivering correct results but is performing slowly, you need to look at how to optimise the database to retrieve the fact rows more quickly - perhaps by adding an index to each fact table based on the key being used to join from Table D?

Cheers!

MF.

[Rosanero4Ever]

ok, you're right. It isn't a cartesian product ...I evaluated bad my query. Indexes in my data warehouse are optimized and I haven't big performance issue (at the moment). Anyway, in the meantime, I have edited my model using a Bridge Table. In fact, I have to model invoices and relative payments. For each invoice I can have multiple payments, so i edited the model using a bridge table which avoid full join and can satisfy my requirements better because i don't need to drag in my report the item that "links" invoices to payments.
The model is now as follows:

Invoice <- 1,1--------1,n [Bridge table invoice_payments]-0,1-------------0,n-[Payments]

I specified 0,1 and 0,n for payments side because not all invoices could be paid

In the bridge item I inserted a determinant.
IdInvoice (gropu by)
IdPayments (group by)
Idvoice, idPayments (identifier)

Invoices table has cardinalities 1,n to dimesion tables excpet to bridge table

First test are positive.... what do you think about this (even if this isn't about stiched queries)?