what is preference on Procedure VS. Straight pass-through

[JPCogAdmin]

Good morning,

I'm wondering about query building in Cognos. I don't know if you have ran into questions like this but here it goes. If you have a bunch of self-queries, table joins, nested queries and the like, what is the best option? Should one write store procedures or straight pass-through code in Cognos?

The two options I'm thinking of go like this. If I write store procedures it would be good because the code stays on the server and more tools could be used as front-end using the existing procedures.

If we write pass-through sql code then we bypass all the procedure building and focus more on simply the complex SQL. What is the "best practice" and lessons learned fromt this? Thanks for your comments and answers.

-J

 

[mfgf]

Hi,

Be aware that coding pass-through SQL may result in some extra processing (required by a report - usually a wrapper to build a projection list) being run locally on the C8 or CRN server. Coding as Native SQL would normally add your query as a derived table to the wrapper query and pass it all to the DB to be executed.

I guess the decision to build SPs vs SQL will also be influenced by the ease of coding/maintaining the relevant objects. If your business has people with good database skills but less FM/modelling skills, then SPs would be a good way to go. If you have lots of good FM modellers but few or no db gurus, then code the work in FM instead.

Just my penny (the Sterling equivalent of 2 cents)

MF.

 

[JPCogAdmin]

;-)
Thanks mfgf.

Very nice points you're making. Also, if the DB is huge then a SP would run much faster right? How about two scenarious:

Limited number of FM Modeler and Report Writers.
Limited DB people.

Also, for maintenance reasons or in case other groups use other reporting tools SPs would be more portable right!
Maintenance wise, I would also consider that SPs would be more reliable than pass-throughs because "what if there is a bug in the reporting tool", right?

Ok, and there is the other question. What is the real business need when using "Native SQL"?

-J


-J

 

[mfgf]

Hi J,

All good points, yes.

Of course, if we're using Cognos reporting tools, we all know there are never any bugs! ;-)

MF.

 

[JPCogAdmin]

MFGF,

So what are the scenarios you've had when you HAD to use native-sql? Can you explain if you can?

-J

 

[CognosChicago]

SP's can be great, but just remember, you might have to go back and republish your FM model every time you make a change to the SP. Not that this is a problem, just something to remember.

 

[JPCogAdmin]

Thanks CognosChicago.

That is certainly something I didn't know. So if the change is considerable then reports that depend on the previous SP would be affected either positively or negative right?

-J

 

[CognosChicago]

They may just render an error. Let's say you have params you want passed to the SP, pretty common thing to do. If you change anything about the params you need to fix it in the FM model and republish. The FM model is only as good as the last time it published.

At the same time if you make internal changes to the way the SP works and performs and it has no effect on the params or how the output is displayed, then you do not need to updated the FM model.

 

[JPCogAdmin]

Oh Ok.
Thanks CognosChicago.

-J

 

[mfgf]

Hi,

Coming back to your question about coding SQL (Native, Pass-Through or Cognos - whatever your preferred flavour), this is something I religiously try to avoid in my reports, and mostly try to avoid in Framework Manager.

Coding SQL queries by hand in a report makes maintenance of the report much more difficult, and also bypasses some of the nice inbuilt functionality such as auto grouping and aggregation. It also makes it very easy to overlook business rules built into the package, such as security filters.

Coding SQL queries in Framework Manager may result in less efficient queries being generated later on in your reports (assuming you are not hand-coding these as well). If you leave each query subject in Framework Manager as 'Select * from <table>', the query engine has a much better chance of minimizing the query later on where it's used in a report, and there's more chance the metadata for the table will be cached in the RunTime Metadata File (RTM) for each user too. If you code your own custom SQL query for a query subject, then because it's more complex, there's less chance of the Query Engine being able to minimize it, and it probably will not be included in the RTM file either, increasing the callbacks to the data source and impacting performance.

I know this is not always a viable solution, and sometimes there's no option but to code a specific query to retrieve the required data, so I treat this as more of a guideline than a rule.

Sorry if I'm rambling on again - probably the onset of senility

Best regards,

MF.