Write sql query in framework manager

[saumil287]

Hi all,
Can we write sql queries in framework manager to achieve some functionalities after importing appropriate tables in cognos framework manager
If yes then where to write sql queries.

thanks in advance

[blom0344]

When you create a new query subject you have 3 options. The 2nd one 'datasource' allows for coding your own definition in SQL. Default is Cognos SQL type, which allows using cognos functions. Look under options to change the SQL type. If you can stick to Cognos type, then this is the best option.  If you need very complex constructions or want to use database functions, then change to native.
Stay away from the 'pass-through' option. We have seen some nasty side effects with that option

[cognostechie]

Blom -

Can you mention what side effects you had when using Pass-Thru SQL. I know it is extreme and has
side effects, even I had problems and Cognos does not recommend it either but I would like to know your experiences for future reference.

[blom0344]

To our surprise we found out that performance issues in some reports could be traced to part of an older model where SQL objects where defined as passthrough. What happened was that Cognos decided to skip pre-aggregation filters and normal joins and decided to read contents of all SQL query subjects and rejoin them on the server.  This caused massive performance issues and the BiBus process 'going through the roof'.

When we reset to 'native' both joins and filters where executed as we had expected and performance was restored to a few seconds. This was a real eye opener, but the actual reason for the difference between behavior of 'passthrough' and 'native' in this case still baffles me.

It should be said that the old model was very far from a best-practice example, but I also fail to see what scenario would merit 'pass-through' in any case..

[saumil287]

Hi Blom,
thank u very much for your responses.
I got it

[cognostechie]

To our surprise we found out that performance issues in some reports could be traced to part of an older model where SQL objects where defined as passthrough. What happened was that Cognos decided to skip pre-aggregation filters and normal joins and decided to read contents of all SQL query subjects and rejoin them on the server.  This caused massive performance issues and the BiBus process 'going through the roof'.

When we reset to 'native' both joins and filters where executed as we had expected and performance was restored to a few seconds. This was a real eye opener, but the actual reason for the difference between behavior of 'passthrough' and 'native' in this case still baffles me.

It should be said that the old model was very far from a best-practice example, but I also fail to see what scenario would merit 'pass-through' in any case..

My understanding is:

1> Cognos SQL - Cognos will interpret the SQL and then execute it.
2> Native SQL - Cognos will not interpret the SQL but interpret the filters, joins, calculations and then execute it.
3> Cognos will not interpret anything and will just throw the SQL at the Database. This is why it was probably not considering the filters.

I would presume pass-thru will be used for Query subjects that have very complex SQL and should be used just by themselves without any relation to any other query subjects.
 

[blom0344]

Could be just about it. I am going to make a little testmodel and see if I can reproduce the pass-through behaviour.  ;)

[MFGF]

The other consideration is that any query issued will require a projection list to provide the items to be included in the result set.  One of the big differences between Native SQL and Passthru SQL is that native SQL can still be tweaked at runtime to generate the required projection list, whereas passthru SQL  is just executed blindly.  If necessary, extra work then needs to be done via local processing after the query returns stuff back in order to cobble the required projection list together.

Generally I would recommend Passthru SQL queries are avoided more fervently than timeshare salesmen with the plague and severe halitosis. :)  The only time I would even consider them is if the database has some weird function or process that you know you can call but that causes the query parsing in Cognos to barf.

MF.

[Lynn]

Generally I would recommend Passthru SQL queries are avoided more fervently than timeshare salesmen with the plague and severe halitosis. :)  The only time I would even consider them is if the database has some weird function or process that you know you can call but that causes the query parsing in Cognos to barf.

MF.

Oh please! No need to mince words and beat around the bush!! Tell us how you REALLY feel about passthru SQL queries for goodness sakes!

:D

[MFGF]

Oh please! No need to mince words and beat around the bush!! Tell us how you REALLY feel about passthru SQL queries for goodness sakes!

:D

Oh all right! I admit it! I secretly love them and was just saying those things to keep my desperate hankering for them secret! Sigh.  Now my guilty secret is out for all the world to see. ;) :D