caching reports on a proxy server - anyone have experience or thoughts?

[tlambert]

Has anyone here ever tried to cache output report web pages on a proxy web server or have words of advice on whether this is a dumb idea? ANY help is appreciated.

This has "these guys aren't using Cognos correctly" written all over it. However, please consider our use case.

We have an anonymously accessible instance (click on the stroke burden link to open dashboard - http://wisdom.dhhs.nh.gov) and some reports can take a few minutes to run (especially the map reports). Customers are highly satisfied with the product when it works well so I'm not keen on reducing functionality. There are 3 keys to the problem problems. 1) The nature of the query calculations being very expensive, especially related to the map reports. We have pined over the underlying queries extensively. User requirements are just plain expensive to implement. 2) the no-prompt-page interactivity (notice the change button which displays a hidden div with prompts) means that users need to interact with the report and means that using saved report versions doesn't work well. 3) The application running these reports uses the cognos launch method with parameters and I'm not sure if the proxy-cache will work with this method.

We're in the process of moving to DQM/Dynamic Cubes which should alleviate the problem at the query level. However, I'm being asked to look into other options in the short term. Here's a few of my conerns:

1) If the cached version is used will the users get authentication cookie and will the prompts within the change button still work correctly? I know they are anonymous but Cognos basically "logs them in" as a user named 'Anonymous'.
2) Will there be any suprises related to releasing the caches
3) How will this behave with the Cognos launch method

[SomeClown]

We're looking at something similar only with Active Reports (pre-built).  I don't think that's an option on your release, though.

[bdbits]

Looks nice. :-)

I think how well your ideas work will depend a lot on the specific proxy server being used and how it handles caching. I do not have a high comfort level with the approach, but I suppose it might work. I do not think authentication will be a problem, though.

DQM and Dynamic Cubes are very aggressive with caching so I expect that will help quite a bit.

You might try using aggregate views, if your database supports it. This is transparent to any database user, Cognos or otherwise, and would require no changes to your models. Essentially, you create aggregate views in the database and materialize them (Oracle calls them materialized, SQL Server indexed, not sure about others). If the database receives a query that can use the aggregate view, it will fetch the results from the view rather than the table. Note: I am only aware this exists and have not tried to use transparent aggregate views like this myself. But it seems designed for use cases like yours, so might be worth checking into.

[tlambert]

Thank you for your replies. I'm being advised by our network admin that proxying on dynamic content is not the easiest or most reliable way to go. It is recommended only for frequently accessed static HTML. I'm not sure what our proxy server platform is.

Looks like dynamic cubes is the way to go, too bad we're getting a lot of cryptic errors resulting from our simple tests of dynamic cube functionality. Slowing us down a lot.

[bdbits]

Have your server admins looked at the Cognos server utilization? Is there enough capacity there? Have you tried running a report trace? Or taking the queries behind the reports and running them in the database to check response times?

I guess I am wondering if some of your performance issues could be from local processing on the Cognos server, rather than the database.

I work in state government these days. We similarly have a public-facing site utilizing an anonymous Cognos instance for some of the reports on a transparency site. Mostly boring old tables instead of pretty maps and graphs. :) We have Transformer cubes behind most of the reports, unless you drill down to very low-level details where it falls back on some relational queries. I would like to transition to dynamic cubes, but I do not have budget allocated for a rebuild at this time. I really really wish IBM would create a tool to migrate a Transformer model to Dynamic cubes - maybe someday. Our site is at http://data.share.nd.gov/pr. The Cognos stuff is under the sections Checkbook, Budget, Funds, and Employee Compensation.

[tlambert]

Thank you for the questions. These queries run on powerplay cubes. The map reports are theh slowest especially when multiple year aggregates are what they want. We toyed for a while with an elaborate dimension with all the possible year group possibilities included but the cube became huge. It was "cuge". I don't want to invest more time into transformer/powercubes - it's not in our future.

We're currently trying to port all our cube data sources to an Oracle database (which we use when we're not using cubes) and remodeling in dynamic cubes with a big ole in memory cache set. No luck yet, lots of errors even in modeling the simplest dimensions. Cognos tech support is starting to help us figure out what might be wrong.