Cognos issuing multiple queries for charts within master/detail reports

[barrytown]

Hello community,

I have a serious performance issue with a report I'm working on. The report contains a master/detail relationship, with three charts at the "detail" level. I find that Cognos issues one query for every chart at the "detail" level. If I have 200 charts in my report, Cognos issues a query for every chart, even though all of the data is available within a single query. When I design the exact same report using a list within the "detail" section, rather than charts, Cognos will use a single query to compose the entire report.

Is there a way to make Cognos cache the query and reuse that to draw the detail graphs, rather than issuing so many queries?

Thanks in advance,

Barry

[navissar]

The easy way is to work with DQM. The hard way is this: http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html

[barrytown]

Thanks for your reply. I don't have experience with DQM, but reviewing the documentation, it looks like that won't work for me: I'm using mysql as the relational backend, and it's not in the "supported" list. I did also try the "hard" way, which also didn't work for me (didn't change Cognos' querying behavior) and which won't work in my customer's environment, because we cannot change server settings.

If you have any other ideas or suggestions, I would be grateful. Thank you.

[navissar]

That's the two methods there are for getting MDR to work on a single query, I'm afraid.

[MFGF]

Thanks for your reply. I don't have experience with DQM, but reviewing the documentation, it looks like that won't work for me: I'm using mysql as the relational backend, and it's not in the "supported" list. I did also try the "hard" way, which also didn't work for me (didn't change Cognos' querying behavior) and which won't work in my customer's environment, because we cannot change server settings.

If you have any other ideas or suggestions, I would be grateful. Thank you.

Hi,

You can connect to MySQL via JDBC (and therefore use DQM) in Cognos 10.2.2. What build are you currently using? If an earlier version, do you have the opportunity to upgrade?

Cheers!

MF.

[Andrei I]

Hi guys,
Has anyone successfully achieved  Optimized master detail relationships in reports in a Master-Details report ?
http://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.dyn_query.10.2.2.doc/c_dqm_master_detail.html%23master-detail?lang=en

I am attaching:
1) md.xml - report source
2) md-dqm-log.zip  - DQM logs for this report
3) master-detail-package.zip  - Cognos export package (the report in the folder  Samples_DQ ‎> Models ‎> GO Data Warehouse (query) ‎> Master-detail‬ )

Can you please try this in your environments?
Or just confirm that you did make it work with your projects and have ONE Detail query only for a Master-detail report.

My environment:
Cognos 10.2.2
C8CRN_version=C8-AW64-ML-RTM-10.2.6100.235-0
DB:  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Thanks,
Andrei