Performance issues with report using 2 queries and 6 combination chart

[NicolaTesla1]

Hi,

I have this report that:
- Is made out of 2 queries.
- I have created a second query called "Summary" based on the first query named "Detail"
- The report ss made out of 2 pages.
First page has a simple cross tab + 3 "Combination Charts" based on the query "Summary".
Second page, has a simple cross tab + 3 "Combination Charts" also based on the query "Summary"
- So in total i display 6 "Combination Charts" on the report + 2 crossTabs
- The main reason i have created a second query is because i couldn't find a way to do a calculataion of ALL the products ...
- So i first created a "data item" in the first query with only "1" in the expression.
- Then i create another "data item" in the second query and put this in the expression:
count([Detail].[1] for any[Detail].[StoreKey] )
- Another reason to a second query is because i needed to FIRST execute some totals and then once those totals have been calculated, i needed to summarize that etc ...

I know that there is an option or propertie called "Solve Order" but that didn't seem to work for me.

Question:
- The report runs in 1 minute. I need to make the report execute under 15 seconds (if possible). I am NOt using "Use Local Cache"
on purpose because i need to make sure the report runs FAST even after the 2nd time it is executed.
- I am able to make the report run under 10 seconds BUT I NEED TO DELETE ALL 6 "combination charts" for that ...
- Is there anything sonmeone would suggest ? Or am i doomed with that execution time and to delete my 6 "combination charts" ?...


Thanks !

 

[blom0344]

1. The only way to really speed up things is to make sure aggregates are performed by the database. Databases are much more suited than the Cognos server here.
2. Not using local cache means the query will be executed every time, even if the Cognos server has the previous result still cached. Which means it will ALWAYS be slower, though more reliable.
3. Any Graphic display of data will stall a report. The more graphs the more time the cognos server will need..

Ties Blom

 

[NicolaTesla1]

Hello Ties,

1- In deed, i found out that i need to delete all 4 graphics from my report to gain performance. I have decided to provide an option to the users that lets' them the choice between "Display graphics" or "Don't display graphics". I use conditional blocks for this and a block variable.

2- Also, the people from BW team might need to create new package with data summarized in new levels currently not available. Not sure that it will be accepted because of project time ending very soon ... We'll see.


Good day to all in this great forum !