Increasing the speed of multiple queries when one query has all the data

[SpareTire]

Hi All,

I am working on a relational database and I have 10 Queries. The 1st Query has the "consolidated information" of all the organizations of the company, the next 9 queries use a subset of that 1st query by filtering for a specific sub-organization.

I am using several different queries as the client wants to render the same report 9 different times in excel.

Solution 1 - My first attempt was to use sub-queries. Create only the consolidated query and 9 sub-queries based off that. But received a "Current CES Measure not supported".

Solution 2 - Then went with just recreating the consolidated query but filtering for the specific organization. However, I know that increases the report time significantly and I suspect its due to Cognos executing 10 separate queries to the DB.

Solution 2B - I tried checking "Use local cache" for the consolidated query and "Concurrent" execution for the other queries.

My end goal is (and this might not be possible) is to have one query to the DB for consolidated and for Cognos to re-use that dataset to populate the other queries.

Thanks for any suggestions. I attached a visual of my explanation as well if that helps.

[dax]

Hi

Have you thought about using page sets to do this?  The steps would be something like this:

1.  Create a single query and ensure that it includes the "Sub Organization" field.
2.  Add a page and create your consolidated report on this page.
3.  Add a second page and create a report in the format required for the remaining 9 reports.  Ensure that the Sub Organization field is on the report.
4.  Select the Sub Organization field.  Choose Structure, Set Page Break.  You should see a message long the lines of "A new page will be displayed for each value of [your field name]"
5.  Cross your fingers and run the report.

If you look in the Page Explorer it should look something like the attached (obviously with whatever names you have in your actual scenario).

Hope that is of some help.  Let us know how you get on :)

Dax

[SpareTire]

Hi Dax,

Thanks for your reply!

Yes, I tried page sets but as you may know the page set performance is horrendous. After your reply, I asked myself why though. After some digging around I realized that page sets are slow due to the master detail relationship which forces Cognos to filter locally, hence performance is effected.

Maybe that is the answer to my question....having cognos try to filter one query locally is actually slower than it hitting the DB 10 times. For now I made every page manually with a corresponding query   :'(

I keep coming back to it to try something new, so I'll let you know if I run into something (probably not) but Cognos is fun in that way, right?

[BigChris]

That's a very interesting point - I'd be interested in what you find out. I generally use page sets when I want to burst a report out to multiple recipients, so I'm not sure how easy it would be to do that with multiple queries and pages...

[dax]

Hi

I did a little experimenting and reading this morning.  Initially I built a report based on a list and then used the Page Break to create separate pages (i.e. a page set).  I then traced the SQL to see what was happening when I ran the report.  For a list report it only sent one query to the database so I guess the work of breaking up the report into separate pages is done by the Cognos application server (local processing).  In my example i'm not working with a large amount of data so am not seeing any performance issues.

I then read the documentation here: http://www.ibm.com/support/knowledgecenter/SSEP7J_10.1.0/com.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc/ug_cr_rptstd_id29331cr_rptstd_frmat_rep_create_page_set.html and noted that it says that Cognos will not use master detail on lists but it will on crosstabs.  Sure enough, I changed my list to a crosstab, exported by report to Excel and I can see in the SQL profiler that it's sent a query to the database for each page in the report, which is clearly going to cause some rubbish performance for anything other than simple queries.

Am i right in saying that you're using a crosstab in your report?  Do you have access to your database to see what SQL is being sent through?  Might give you some clues...

Don't know if this information is of any use.  To be honest I still find Cognos a bit of a mystery when it comes to solving performance issues.  Some days I create reports that pull out massive amounts of data in a few seconds and other days the simplest of reports runs at a snail's pace....like you say...Cognos is "fun" that way :)

Cheers
Dax

[SpareTire]

Hey Dax,

Your spot on. I did the exact same thing yesterday with a small subsection of my report. I am using a crosstab and each page generates its own query in SQL.

I always say this is more art than science.