Joins /' Union in report vs FM

[cogcurious]

I have heard that in case of unions and joins in the report, Cognos engine retrieves the data in the two queries and then joins the datasets locally. And, this is bad for performance as compared to the join being in the FM.. In case of FM the join would have been pushed to the database. Is that true?

[cogcurious]

anyone?

[blom0344]

If the recordssets are small (as a result of aggregation) the cognos server will cope pretty well, but a database is a much better tool for joining large sets. In an ideal world the model should cope with the combination of data..

[cogcurious]

Apologies for asking again

1) So it does retrieve the datasets and joins locally. Its just that if its less of a hit on performance if the datasets are small Correct?

2) if the join were made at the FM level would the join have been pushed to the datbase with the native sql

3) are stitchetd queries done locally. I know the ones with RSUM are but are all of them done locally?

[blom0344]

1/ Inclined to say 'yes' to this one

2/ I think this also depends on the complexity involved. The suggested native SQL sometimes consists of multiple 'independent'sets

3/ I ran a lot of traces when starting with DMR models years ago and noticed that very frequently multifact was still handled within 1 well-defined SQL on the database..

Personally I think it is unwise to assume 1 out of 2 scenarios. Too many parameters involved for my taste  ;)