Cognos Active Report Performance Issue

[ricky]

Hi,

We are using Dynamic cubes to create a dashboard in active report format. This is a yearly dashboard that will be used for multiple clients. It takes around 50 minutes to generate the dashboard for a single client when run in the background. The report size is around 5 MB's and there is no literally no lag when the cards are switched. When the report is executed in the report editor we get a gateway error after 6 - 7 minutes. When we try pulling the same data in the Cross tab format it takes around 30 seconds. I have already used the filter function to ensure that only the relevant data is being pulled and there are no cross joins.

The graphs are generated using data decks for tops and sub tops based on the combination of:

1) Quarter (1,2,3,4,ALL)
2) Month (All, Jan, Feb,Mar) and onto the last quarter
3) Business Type (max 6 values)
4) Contact Type (2 values)
5) Top - The charts are generated in 3-4 minutes till this level using the combination of 1,2,3 and 4 above.
6) Sub Top - The charts are generated based on the combination of 1, 2, 3, 4 and 5 above. Adding the sub topic charts makes the report really slow. Either we get a gateway error (running the report in the editor) OR it take around 50 minutes to generate the dashboard. I even tried displaying the cross tab, the same thing happens (either the report errors out OR takes around 45 minutes to generate)

Since we plan to create a virtual cube(based on the combination of this cube plus another cube) we may end up generating overall around 1200+ charts. Looking for ways to enhance the performance of the report and reduce the execution time to 8-10 minutes. Please let me know if I am missing something here. Not sure if this is possible, but any help will be appreciated.  :)

Best Regards,
Ricky

[Deep750]

What version are you on?

It was an issue with 10.2.2 < FP3

http://www-01.ibm.com/support/docview.wss?uid=swg1PI40967

[AnalyticsWithJay]

Your first order of business should be to identify whether the performance issue is due to the cube model, report design, or data model.

1. Have you captured the SQL of these queries? How long do they take to run directly against the database?
2. What's the size in MB of this active report?
3. Could you post the debug info so we could see the details? ctrl+shift+d.
4. Take a look at the guidelines for active reports if you haven't read it already. Lots of good performance improvement tips.

You mentioned a query takes 30 seconds in a crosstab, but I'm assuming you have more than one query in your dashboard.

[ricky]

Hi,

Thank you both for the reply.

@Deep750: We are running Cognos 10.2.1.

@CognoidJay: Here are the answers for the questions
1. Have you captured the SQL of these queries? How long do they take to run directly against the database?
Since it is a dimensional model, not sure if the MDX can be run against the database.

2. What's the size in MB of this active report?
The size of the report is 5.21 MB.

3. Could you post the debug info so we could see the details? ctrl+shift+d.
I tried doing this but the page is added a favorite on the top. As i am new to this so still struggling to learn.  :)

4. Take a look at the guidelines for active reports if you haven't read it already. Lots of good performance improvement tips.
Will be going through this document to look for optimization tips.

Once again I appreciate your help.

[MFGF]

@Deep750: We are running Cognos 10.2.1.

Did you look at the link Deep750 posted for you? It's an APAR (defect) report that says "Active report running three times as slow in 10.2.1 compared to
10.1.1." in the title.

The solution it gave was to upgrade to Cognos 10.2.2 Fix Pack 3 (or later).

MF.

[AnalyticsWithJay]

Here's how to capture the SQL instead of the MDX:
http://www-01.ibm.com/support/docview.wss?uid=swg21579065

If the APAR suggested doesn't fit your criteria, it's worthwhile executing the SQL directly against the DB and comparing run times. Note that there may be more than one query captured in the logs.

You'll find it in <cognos>/logs/xqe.

When you run your active report, right click, go to Info, and post screen captures of the information please.

[Deep750]

Hi,

Thank you both for the reply.

@Deep750: We are running Cognos 10.2.1.

1. Have you captured the SQL of these queries? How long do they take to run directly against the database?
Since it is a dimensional model, not sure if the MDX can be run against the database.

Do you know which fixpack you have?
I have been running on 10.2.1 FP9, and have good performance - comparable to 10.2.2 FP7, so I guess the issue was also fixed for later fix packs for 10.2.1.

1. When you use Dynamic cubes, queries are actually sent to the database. Be sure to restart the cube, before checking, so that you are sure the cube actually sends queries to the DB, and not using the cache.