tag:blogger.com,1999:blog-769243699990058619.post8263200762713356216..comments2023-10-23T23:55:48.217+05:30Comments on Cognos and Me: Limited Cache, Query Re-Use CapabilitiesZephyrhttp://www.blogger.com/profile/07017132376090028553noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-769243699990058619.post-21000570552847745062011-03-10T11:52:27.761+05:302011-03-10T11:52:27.761+05:30Hi Ranjeeta, Since your report has multiple data c...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: <br /><br />http://cognosandme.blogspot.com/2010/09/multiple-queries-for-different-data.html<br /><br />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.<br /><br />Hope this helps.Zephyrnoreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-55376148643856468962011-03-09T16:59:19.216+05:302011-03-09T16:59:19.216+05:30Hi , I think you blog really helps.
Pls help with...Hi , I think you blog really helps. <br />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 seperatleyRanjeetahttps://www.blogger.com/profile/14455104195572055732noreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-84243845398998679542010-10-15T01:23:48.726+05:302010-10-15T01:23:48.726+05:30I had to propose alternative options:
1- Using a ...I had to propose alternative options:<br /><br />1- Using a temporary table for complex queries (but this customer is afraid of datawarehouse and does not want to build one)<br />2- Using Cognos Virtual View Manager (Composite Information Server in the older version of cognos 8.4)<br /><br />What I did not say is these reports are using burst options and master detail relationship...<br /><br />The aim is to cache the result of complex queries to optimize reports generation.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-64863095385485864732010-10-14T12:29:24.418+05:302010-10-14T12:29:24.418+05:30Good to know that the articles are of use. And I a...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.<br /><br />So were you able to convince your users with regards to the performance issue?Zephyrnoreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-81802389059655257782010-10-14T00:21:52.600+05:302010-10-14T00:21:52.600+05:30Hi Zephyr,
I am an old Cognos consultant now and ...Hi Zephyr,<br /><br />I am an old Cognos consultant now and I have to say that your article is very relevant. <br /><br />Do you know Cognoise web site ? What do you think about this site ?<br /><br />About your article, this is funny because I am currently working on this topic at the moment.<br /><br />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...).<br /><br />The customer is complaining about Cognos reports performance, they would generate too much queries...<br />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...<br /><br />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:<br />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.<br /><br />Kind regardsAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-14302422665307263732010-08-16T13:03:50.159+05:302010-08-16T13:03:50.159+05:30Hi, I have published a post detailing the steps. L...Hi, I have published a post detailing the steps. Let me know if this helps.Zephyrnoreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-64050489629105751732010-08-13T16:35:22.981+05:302010-08-13T16:35:22.981+05:30Thanks Zephyr for your prompt reply.
The First Is...Thanks Zephyr for your prompt reply.<br /><br />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.<br /><br />On the second Issue, I tried the case statement and created a new column - Top 3 Summary<br /><br />case<br />when [Rank] in (1,2,3) then 'Sub-Total' <br />else null <br />end<br /><br />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 ?<br />How do I remove/hide the null column keeping the "Top 3 Summary' intact ??quintessencehttps://www.blogger.com/profile/06766666135983493401noreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-14465847518412611562010-08-13T13:29:03.827+05:302010-08-13T13:29:03.827+05:30Great to know the articles are of use.
For the f...Great to know the articles are of use. <br /><br />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.<br /><br />Else I think till Cognos improves its cache features we are at a loss. <br /><br />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 :-(.<br /><br />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. <br /><br />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.<br /><br />Let me know if the above helps.Zephyrnoreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-14177743316285665972010-08-12T19:54:52.301+05:302010-08-12T19:54:52.301+05:30Hi Zephyr,
I really appreciate the information y...Hi Zephyr, <br /><br />I really appreciate the information you have put up on query re-use in-capabilities of Cognos.<br />there are two things I'd like to look at as I am really stuck in the middle of a project<br /><br />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'.<br />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 ??<br /><br />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 ?quintessencehttps://www.blogger.com/profile/06766666135983493401noreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-88335821947083619672010-07-12T11:18:32.076+05:302010-07-12T11:18:32.076+05:30Thanks Augusto. Really nice to hear that people do...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.<br /><br />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.<br /><br />The only disadvantage with using JS would be upgrade / migration issues with later versions of Cognos.<br /><br />Let me know if this is the route you would like to take and I can try and dig up some code for you.Zephyrnoreply@blogger.comtag:blogger.com,1999:blog-769243699990058619.post-64945185368864077222010-07-10T17:51:46.724+05:302010-07-10T17:51:46.724+05:30Hi Zephyr. I'm a huge fan of yours and I your ...Hi Zephyr. I'm a huge fan of yours and I your blog helps me a lot. <br />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.<br /><br />Thanks in advance.Unknownhttps://www.blogger.com/profile/10001742126483951668noreply@blogger.com