How to avoid each graph to generate separate query?

[ShacharH]

Hi all,

Platform : Cognos 10.2.1 with FP1
Problem: Even when all graphs are from the same Cognos query,using Data items from the same query and doing the same Group by, report is generating for each graph separate query in DB, only the measure is differ in the graphs.

How can i optimize cognos to send one query to DB with the requested Group By and with all required measurements and then to draw the graphs ?
I tried to use hidden list containing the group by and measurements but then i got one more query in DB...

Any suggestions ?
Thanks In Advance.

[ShacharH]

BTW,
Using reference query for each object solves it, if we dealing with lists.
Graphs acts the same  and send separate query for each graph.

[navissar]

Hi Shachar,
I cannot test this right now, but read this link: http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html
It's orginally made to set up single query in MDR, but the changes to the CQEConfig and the set up of the reference and actual query should enforce a single-query for your situation as well.
Also, if you're having performance issues, a report such as you're describing would be a great candidate for DQM - caching will improve performance when all charts are using the same data item. Consider this as a way to go.
Good luck!

[ShacharH]

Hi Nimrod,
Thanks for your answer,
I'll try ti figure how can i test this solution in our systems

[bdbits]

One word of caution - changing server xml files as suggested in the linked article will affect all reports. And, you may have to reapply such changes after every Cognos fix pack or update. Personally we try really hard to avoid server changes like that, but sometimes there is no other way.

[navissar]

Configuration files are there to be edited and  changed as required. There's a good reason Cognos configuration files are on an open format (xml)  and not precompiled, same reason Cognos CSS is available for editing:Cognos is a platform, that can be modified in several different ways.
It's important to keep a list of changes (in my environments I keep an excel sheet with file name, rows changed, reason and so on) for upgrade and maintenance, but I wouldn't go as far as to try and avoid changes for a once-a-year-upgrade.
With regards to affecting all reports, this particular change will only affect the reports where queries are set to cache.

[bdbits]

If that works for you, great. Just because you can does not mean it is necessarily a good idea, but whatever works for you. We prefer not to make server-side changes unless required, and we have multiple servers to keep in sync, some of which are clustered. It's just another potential point of failure for us when updating. We don't modify server CSS files either. In most cases, there are other ways to accomplish the same thing without server changes.