I have always had a major complaint with Cognos and that is with regards to caching and query re-use. I am not sure if this is the case with other BI tools as well. And it is really frustrating when you have to explain this to the users.
For instance you are asked to implement dynamic sorting on your report that includes a number of columns and retrieves a huge amount of data. Now with Dynamic sorting each sort fires a query against the DB which is performance impacting. And when you explain this to the users their query is "Why doesn't Cognos just retrieve the data and push it to the client side, so that each sort would not hit the DB?" Good question, but that is not for me to answer.
Likewise imagine a scenario where you have to display 3 Lists in your report one each for YTD, QTD and WTD. Ideally what I would like to do is to get the YTD data and QTD, WTD would all be a subset of the YTD data. So if the YTD data can be retrieved and cached and this data re-used for the other queries that would be great. But unfortunately that is not the case with Cognos. When I create a query object and point other query objects to this the base query still gets executed multiple times.
These are all required features in Cognos.
The only cache and query re-use capabilities Cognos offers is when you have multiple lists all pointing to the same query and having the same order by, group by and columns which is ever rarely the case.
Friday, July 9, 2010
Subscribe to:
Post Comments (Atom)
Posts
11 comments:
Hi Zephyr. I'm a huge fan of yours and I your blog helps me a lot.
Not sure if you can help, but I'm looking for a way to remove the border on a checkbox in cognos. I can't access the prompt global css do this. Also, I only want to remove it from a single checkbox on my prompt page.
Thanks in advance.
Thanks Augusto. Really nice to hear that people do find my articles useful. It gives me all the more impetus to write more articles and be of help to others.
About your requirement, I think if its really a must-required feature to show the checkbox without borders then I would suggest you could write a JavaScript code to display a custom checkbox prompt as required.
The only disadvantage with using JS would be upgrade / migration issues with later versions of Cognos.
Let me know if this is the route you would like to take and I can try and dig up some code for you.
Hi Zephyr,
I really appreciate the information you have put up on query re-use in-capabilities of Cognos.
there are two things I'd like to look at as I am really stuck in the middle of a project
1. There are 4-5 views in a single report (tabs in excel spreadsheet). Each view is a actually a different subset of the 'Total View' in itself. I have a join query to get my 'Total View'.
Currently, to get the other sub-setted views I have to re-run the the main query and put a filter each time. Is there a way where I can subset the data for each of the views from the 'Total View' without hitting the Data-base again and again ??
2. I have a cross tab in the report where the columns header is Rank which takes values (1,2,3,4,5). I need a sub-total column right next to the 'Rank' showing aggregation of only the top 3 ranks. I know we have an aggregate option {aggregate(currentmeasure within [Rank])} but that works for all the five ranks !! Is there a way where I can aggregate measure for only a certain category ?
Great to know the articles are of use.
For the first issue, you could try setting the local cache option for the query, but I doubt if that would help you. If your Total View is more like a union of the different views you could create a section based list report assuming all your lists look similar in structure and a single list made to look like multiple lists on separate pages is what you are looking for.
Else I think till Cognos improves its cache features we are at a loss.
It would have been great if a single query object data could be cached based on a property and then all other query objects referencing it could just re-use the data. But that is not available :-(.
For the second issue, you could use a case statement to calculate the total for the required ranks and drag this as a separate column in the crosstab instead of using the Summary function.
In case yours is a dimensional model, you would need to create a set that filters out the unrequired ranks and then do a total based on this set.
Let me know if the above helps.
Thanks Zephyr for your prompt reply.
The First Issue I guess will have to go un-resolved and I'll have to re-run my joins to get the query each time for each of the view.
On the second Issue, I tried the case statement and created a new column - Top 3 Summary
case
when [Rank] in (1,2,3) then 'Sub-Total'
else null
end
When I drop this in the cross tab as a column I get do get a Top - Summary as desired BUT the summary for ranks 4 and 5 also come along in the column. I do not need a null column wandering in the report ?
How do I remove/hide the null column keeping the "Top 3 Summary' intact ??
Hi, I have published a post detailing the steps. Let me know if this helps.
Hi Zephyr,
I am an old Cognos consultant now and I have to say that your article is very relevant.
Do you know Cognoise web site ? What do you think about this site ?
About your article, this is funny because I am currently working on this topic at the moment.
A customer has chosen Cognos but they directly query the operational database using a home made "federation server". The reports are very complex with many data containers, layout and the queries are also very complex (dozen of outer join, sub-queries, union...).
The customer is complaining about Cognos reports performance, they would generate too much queries...
But when you have a look about the number of queries, it corresponds to the number of data container. If they are linked to only one query, there are different sort, and different group by...
What I find very funny, that is because the customer would like Cognos to manage in local the different sort and group by instead generating multiples queries. When I told them that to optimize Cognos reports, I always avoid to let Cognos work in local, they were surprised but when I show them the properties like database only, I hope they understand what you explain very well:
The only cache and query re-use capabilities Cognos offers is when you have multiple lists all pointing to the same query and having the same order by, group by and columns which is ever rarely the case.
Kind regards
Good to know that the articles are of use. And I agree with you that Cognos' caching features are not great. Wish they would do something about it.
So were you able to convince your users with regards to the performance issue?
I had to propose alternative options:
1- Using a temporary table for complex queries (but this customer is afraid of datawarehouse and does not want to build one)
2- Using Cognos Virtual View Manager (Composite Information Server in the older version of cognos 8.4)
What I did not say is these reports are using burst options and master detail relationship...
The aim is to cache the result of complex queries to optimize reports generation.
Hi , I think you blog really helps.
Pls help with an issue. I have some reports which are very slow, I checked the cognos queries in db. The queries retrieves the data really fast within few seconds but the same queries take lot of time in rendering the reports. The same query is being used in bar charts and cross tabs also. Can we spilt the queries for bar-chart and cross tabs seperatley
Hi Ranjeeta, Since your report has multiple data containers, Cognos fires multiple queries against the DB even if all objects point to the same query. You can take a look at this article for more info on this:
http://cognosandme.blogspot.com/2010/09/multiple-queries-for-different-data.html
You can definitely split the queries for the different objects, by creating multiple query objects specific to those objects alone and pointing your objects to the various queries.
Hope this helps.
Post a Comment