Report using all the Temp space

[ghostraider]

Hi All,

I have a report which has a join at the report level. The first query has about 1.8 millions of rows being joined to another query which has a few thousands of rows. When i run this report my whole temp space is filled up and the report fails. This is when i used the traditional join and later i tried refering Query 2 with Query1 and although this process eats a whole lot of temp space my report ran well. Could someone let me know what exactly is the difference between referencing queries and joining them using the normal join? Does one method have better performance than the other? I find the Reference method to have better performance in my case. Please let me know.

Thank You!!

[goose]

Hi

In general you dont want to do joins in report studio unless you are reporting on data from multiple data sources (different databases etc). Im not sure what you mean by the term "referencing" can you give some more info? also are the two query subjects using data from different data sources?

The reason for the temp space filling up is that cognos is processing each query seperately and then joining them locally on the cognos app server.

Cheers

[bonniehsueh]

In general you dont want to do joins in report studio unless you are reporting on data from multiple data sources (different databases etc).

Can you explain why in general its better to avoid doing joins or unions in reports when possible? By concept I understand it, but not sure how to articulate it. I have a client (who doesn't really know Cognos, but is technical) who usually suggests doing unions/joins and I'm not sure how to explain to them the reasons why its better to avoid it when possible.

Thanks

[blom0344]

In general terms:

1. Your RDBMS is probably using some real serious hardware whereas the Cognos server would be capable of handling ' just'  Cognos requirements.
2. Nothing is more efficient than a modern RDBMS in handling joins. The optimizer can use the best strategy in terms of using indices and re-using execution paths and it simply has the best knowledge beforehand in terms of having statistics on the tables that are associated with the join.

No B.I.-tool is especially designed for performing database tasks , but to a point it does allow for these tasks.

For heterogeneous sources the situation may be quite different and often the only solution at hand..

[goose]

Ghostraider doing joins is a good thing but they must be executed in the correct location which is generally the database engine. If you are joining two tables from the same database lets say the sales table and the customer table, you dont want to have two query subjects in your report with a join, you would rather model the relationship in Framework Manager (FM) and then just use the various data items in your report. Behind the scenes Cognos will generate the correct sql needed to get the data from both tables.

Are you using Framework Manager?