Only run queries that are rendered

[freswood]

Hi all! I've been having some performance problems with a long-running report that has many pages. I've now added a prompt page so that the users can select which pages are rendered, therefore cutting down the report run time if not all pages are required. This is all working well, but the part I'm having some difficulties with is the Summary page. The report has a Summary page that summarises the data found on all of the other pages. Ideally, the Summary page should only show the data related to the pages that the user selected in the initial prompt. Each query relates to a list column in the Summary page, so if only 1 page is selected in the prompt, then only that 1 related column should display in the Summary page.

I assumed that if you conditionally render columns, Cognos would only run the queries related to the columns that are being rendered. The Summary page is taking an incredibly long time to run regardless of what is selected in the prompt page so I suspect it must actually be running all queries. So if that's the case, does anyone know if there's a way to get Cognos to check whether a query is rendered (and therefore required) first before running the queries?

Thanks for your help :)

[Francis aka khayman]

these are just suggestions, it may or may not work.

1. try to set the Execution Method of the queries to concurrent. It MIGHT help.
2. how about putting the render condition in the query filter so that it will set the whole query condition to false if the render condition of that query is no?
ex.

and Render_var = ?render_condtiion?

[freswood]

Thank you so much for your advice! I just started to implement your second idea and realised that it wouldn't work in this particular scenario because of the way each of the individual queries are joined together. It's a bit of a mangled report and we're using a relational database with lots of joins and queries to create something dimensional-looking. But thank you so much for the idea, that will definitely come in handy for other reports in the future. As for this particular report - well I think we might have to give the Summary page a miss.

[navissar]

If I may, before you throw away your summary page, some thoughts:
1. If you're using many in-report joins to create a dimensional feeling, why not create a DMR in the data model? Using DQM over DMR would allow you to get a dimensional feeling while making smart use of cache and hybrid query.
2. If you take all the SQLs your report is generating and run them against your relational, how long do they take to return? It could be that the problem isn't with the queries as much as it's on Cognos end.
3. How big are your facts? It can be a different approach if pulling data from a fact that has a million rows than when pulling data from a fact with 10 mil. rows.
4. Suppose you take the SQL that makes your summary filter, and use a stored procedure, an ETL process or a materialized/indexed view to create a summary table that refreshes periodically using that exact query, and then point your summary page to that table. Even if the query takes an hour to run, you can run it at night in the database, and it'll be ready for you in the morning.