I have report that has about 20 odd queries other than the prompt query. Two are joined into the third which is joined again into someother query. There are some unions as well. I understand in such a scenario data sets are fetched and combined on the cognos server. This is a performance issue. How can i avoid such scenarios? any past experiences?
could go for building a cube for the data which is generally tidier
however until we know why so many queries are joined we can suggest a way to get round it
is it relative time categories you are joining?
If the report was first created in Reportnet it will have extra queries as there used to be a concept of Tabular Sets and Queries, but in the upgrade to report studio all tabular sets became queries too.
If it's a new report, then I'd suggest the problem lies with the package being designed to store the data in one way, but the reports requiring the data to be displayed in another.
It happens quite often in older datawarehouses where the user requirements and business rules change and the datawarehouse gets patched and adjusted.