Monday, March 26, 2012

Debugging Cognos Issues

Like the programmers on the Jersey Shore, I like to follow a GTL approach to debugging cognos issues.

  1. Google
  2. Ticket
  3. Logs
Google. Cognos is an off the shelf product and has standard error messages. Anything you might see there's a guy with a blog explaining how to fix it. *cough*. Google that error code, message text or other identifying issue.

Ticket. Open a ticket with Cognos if you have a support contract. They are usually not the quickest in coming up with a solution so I like to get this started early. Also check that there hasn't been a fix pack released that addresses your issue. Many things, like difficult to debug server stability issues, are constantly being updated by fix packs.

Logging. Enable more detailed logging on the server side if you need more context for the error message. And repeat!

(Yes I made this post because I thought of an acronym)

Thursday, December 15, 2011

Why do you want a new job?

Top Ten Reasons Why Large Companies Fail To Keep Their Best Talent

Is it one of these reasons? I've defiantly changed jobs because of some of these complaints. Developers in the BI area have somewhat of a green field of jobs to choose from. Companies need to recognize the retention risks around keeping their most talented employees.

Tuesday, October 18, 2011

The Downside of Stored Procedure Based Query Items

In Cognos Framework Manage one of the query item options is a stored procedure. This item seems promising, but I would suggest avoiding it. First of all, to setup a stored procedure based query item in framework manager:
  1. Write your stored procedure. It can expect prompts.
  2. Select that stored procedure in Framework Manager as a query item source. (I would suggest pulling this in at the database level, or at the business level if you want to make it clear that it's not a base table) It will be scanned and expected prompts will be presented, similar to a drill through definityion.
  3. Enter a prompt macro in the 'Value' field. At the report level we can setup a prompt to populate this value. This procedure expects 1 prompt of 'dept'. We can define a parameter of dept in a report to pass this value.

Within the framework model, this will behave as an any other query item. It can be related to other items in the model.

The major downside to this is in the execution plans cognos gennerates around these stored procedures. In a normal query cognos' query engine will look at the selected objects and gennerate an optimal query based on the relationships defined in the framework. Ideally, all the query work can be gennerated in 1 query that can be sent to the database. In the case of stored procedures, since cognos doesn't know about the contents of the stored procedure, the sql in the procedure is not included in the optimization plan. Meaning local processing will be required to join the results of the stored procedure to any other object in the model. Be careful to only include absolutely necessary queries in stored procedures.

I would use stored procedures for any procedure needing to use several temporary tables or user defined functions. (Although, in the case of user defined functions, I'd rather registered them with the Cognos gateway so that they can be called directly from Cognos reports)