Why does Cognos generate Multiple Queries even when you have the different objects pointed to the same Query?
Now before I answer that, here's a tip to all the newbies on seeing the SQLs generated by Cognos. To identify the SQLs that Cognos fires against the DB, you would need to use the Generated SQL/MDX option from the Tools Menu and not the one available for each individual Query Object in Cognos. This will display the various SQLs that Cognos would fire against the DB and here's where you would notice that even if you have multiple objects pointing to the same Query Object, Cognos would still fire multiple queries.
Now if you have say multiple lists all pointing to the same Query Object, then unless the columns pulled in, the sort order, group by clauses match, Cognos would fire multiple queries. To make the columns pulled in to match you could use the Properties property of the lists and select all those columns that are missing from each list to match the other lists based out of the same Query Subject.
In case you have different data containers say like a list and crosstab or a bar graph and crosstab pointing to the same query subject Cognos would fire multiple queries as that's how its designed. So no luck over there.
Friday, September 10, 2010
Subscribe to:
Post Comments (Atom)
Posts
4 comments:
Hi. I'm experiencing this same thing. I built my report architecturally to avoid firing the DB more then once, but later realized that despite my efforts Cognos unnecessarily goes against the DB for each object. I confirmed this by viewing the SQL and noticed the many query containers with native sql.
I have several objects and lists. This has slowed down a report that should only take 2 mins and increased the amount to over 14 mins. Did you ever find a workaround?
Thanks!
No luck. If you have several lists based on a single query, and if you can get them to have the same sort order, group clauses, you can reduce a few of your queries. But for the other objects, I do not see a way out.
Wish IBM had addressed such basic issues in Cognos 10 rather than adding fancy features.
Have you tried sharing the queries among lists?
This should reduce the amount of separate SQL sent to the database.
Also using Local Procesing should improve this issue.
Cheers.
Hi Mau, Unfortunately unless the queries across the lists are the same in terms of data items used, sorting order, group by order, Cognos will fire multiple queries. So unless the SQL across the various lists are the same Cognos will not share datasets.
Also, it is not a good practice to use local processing as this will cause Cognos server to take on some processing which is not advisable.
Post a Comment