Dashboard running slow - need to improve performance

[mp3909]

Hi All,

I am new to this forum and also am new to Cognos (been thrown in the deep end  :'().
I am using Cognos version 11.

I have been handed over a dashboard built by someone else in Cognos. Users of the existing dashboard have complained that the dashboard runs very slowly for some customers. Users enter customer number in the prompt page before running dashboard. So for some customer numbers it takes long to load.

I have been asked to look into this issue and finds ways of improving it. The thing is, I have no experience with Cognos prior to this and have only been using it for 2 weeks. But I seemed to have got familiar with quite a lot of it so all good (i hope).

There are few things that I have in mind to help improve performance:

1). I noticed there are 2 queries which are the same except one has a detail filter on Country and the other has a detail filter on Currency...........but everything else is the same. I noticed both queries are being used individually to create separate tables within the dashboard. So query 1 is used to create a table which shows breakdown by Country and query 2 is used to create a table to show breakdown by Currency. But given the fact that all the data items in both queries are the same and only the filter differs, can I improve performance by creating a standard query with all the data items needed but with no filters and then make query 1 and 2 reference that standard query with filters added in them. Does that make sense? I am trying to ask if Reference Query is sensible to use in this scenario and will it help to improve performance.

2). I see there is a setting where you can run queries 'concurrently'. Will that help to improve performance? I have 12 different queries. Will setting their Execution Method to be Concurrent help? Should I only be targeting specific queries for concurrent execution and leave the rest as they are? If so, how do I know which ones to target?

3). I have tried to push as much logic as I can away from the dashboard and into the database. For example, case-when statements and filtering.

4). I do not have access to Framework Manager but is there a setting in there that can improve performance. We have a guy in our team who looks after the Framework and the Admin side of things for Cognos. Should they need to take the responsibility of doing something like applying settings or something which can vastly help improve performance? I get the feeling they are just staying mute and they have many years of experience in Cognos but not pulling their weight. Should I be asking them specific questions related to performance? 

Your advice will be much appreciated.

Thank You

[MFGF]

Hi All,

I am new to this forum and also am new to Cognos (been thrown in the deep end  :'().
I am using Cognos version 11.

I have been handed over a dashboard built by someone else in Cognos. Users of the existing dashboard have complained that the dashboard runs very slowly for some customers. Users enter customer number in the prompt page before running dashboard. So for some customer numbers it takes long to load.

I have been asked to look into this issue and finds ways of improving it. The thing is, I have no experience with Cognos prior to this and have only been using it for 2 weeks. But I seemed to have got familiar with quite a lot of it so all good (i hope).

There are few things that I have in mind to help improve performance:

1). I noticed there are 2 queries which are the same except one has a detail filter on Country and the other has a detail filter on Currency...........but everything else is the same. I noticed both queries are being used individually to create separate tables within the dashboard. So query 1 is used to create a table which shows breakdown by Country and query 2 is used to create a table to show breakdown by Currency. But given the fact that all the data items in both queries are the same and only the filter differs, can I improve performance by creating a standard query with all the data items needed but with no filters and then make query 1 and 2 reference that standard query with filters added in them. Does that make sense? I am trying to ask if Reference Query is sensible to use in this scenario and will it help to improve performance.

2). I see there is a setting where you can run queries 'concurrently'. Will that help to improve performance? I have 12 different queries. Will setting their Execution Method to be Concurrent help? Should I only be targeting specific queries for concurrent execution and leave the rest as they are? If so, how do I know which ones to target?

3). I have tried to push as much logic as I can away from the dashboard and into the database. For example, case-when statements and filtering.

4). I do not have access to Framework Manager but is there a setting in there that can improve performance. We have a guy in our team who looks after the Framework and the Admin side of things for Cognos. Should they need to take the responsibility of doing something like applying settings or something which can vastly help improve performance? I get the feeling they are just staying mute and they have many years of experience in Cognos but not pulling their weight. Should I be asking them specific questions related to performance? 

Your advice will be much appreciated.

Thank You

Hi,

From the details you've provided here, you're not actually dealing with a Dashboard (in the Cognos Analytics sense of the term). You are describing aspects that only apply to reports (eg a prompt page, multiple queries etc). I have moved your post to the Reporting section as you'll likely get more success here.
On to the main question you asked - will changing your two queries to a single query in the report make a difference. Sadly the answer is no - if there are multiple containers (eg lists, crosstabs, charts etc) that are based on the same query, that query will fire multiple times - once for each container.

Regarding the second question about concurrent queries, there's a great article (below) that details what this means and the implications of you setting it:

https://www.ibm.com/support/pages/how-improve-report-performance

Cheers!

MF.

[oscarca]

Is the report built by using an cube or relational data ?

[mp3909]

Sorry, excuse my ignorance but how can I know if its a cube or relational?

Thank You

[MFGF]

Sorry, excuse my ignorance but how can I know if its a cube or relational?

Thank You

Hi,

Take a look at FAQ#5 in the post below. This was for Cognos 10 so the UI looks a little different now, but the same principles hold true.

https://www.cognoise.com/index.php/topic,27563.0.html

Cheers!

MF.

[mp3909]

Thanks MF for your help.

After viewing the suggested post, I can now confirm I am definitely using a relational model and not a cube.

[oscarca]

Somethings you could try are:
1. Delete all unused data items in the queries
2. If you have sortings and filtering make sure they are filtered on a column with data format of an integer rather than a string. For example if you want to sort or filter a customer, do it on the customer number and not the name.
3. Strip the report apart into seperate runnable queries two figure out what part of the report that really takes the longest to execute.
4. Limit the time frame of the fact table in the datawarehouse. Let say you have 20 years of data but really only need 2 years then filter that already in the DW.