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)

5 comments:

  1. Hi, Great article.

    Can you please elaborate on your last statement, "..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"?

    Is this the same as importing the function in Framework Manager or is this something complete different?

    ReplyDelete
  2. Appreciation for nice Updates, I found something new and folks can get useful info about BEST COGNOS ONLINE TRAINING

    ReplyDelete
  3. Hi,

    I am using Teradta bsed SP with Insert, update and delte statements. Able to import and validate/test success fully.

    I am getting confused, this Stored Procedure query subject not showing any query items. I have three IN parameters and mapped the same with parameters in FM using 'promt macro'.

    Can you help, which step I am missing..?

    ReplyDelete
  4. It was very nice article and it is very useful to Cognos learners.We also provide Cub training software online training.

    ReplyDelete
  5. Great Work, after reading this post I felt comfortable with this post thank you very much......................To Get More Information about Salesforce Training in Dallas

    ReplyDelete