Teradata SQL & cognos. How can I customize Cognos to accept a customized & more efficiently performing SQL

Question

RIght at the outset I'd like to say that I am NOT a Cognos Guy .So I have totally disconnected myself from developing cognos cubes / reports whatever you want to call it. There are COGNOS queries auto generated - very badly written that will cause the Teradata ( DBS 15.1.x ) system to Hog on spool & CPU . I can tune them beautifully after I pull them out from DBQL. I want to know HOW can I implement Custom Queries that can be run periodically as batch reports instead of Cognos auto-generating these queries. E.g. You create a cube - its writes code behind it and then you can open the code and write custom code that is equivalent to the original code but performs a lot better. Then when you open the cube again - it remembers there is a custom SQL and runs that instead of its own auto generated SQL . This is just how I imagine one way it can do it but again- I am not a cognos resource so pl dont flag me down for lack of knowledge. That is exactly what I am trying to get an idea about Thanks for bearing with me

Answer 1

You need to use stored procedure to return your expected data and add it to Model.

Then instead of using couple of tables in Cognos report studio (and joins), add one query and point it to your stored procedure. This way your Cognos report will execute the procedure instead of generating query (which may not be efficient in many cases)

Answer 2

In Framework Manager you can create one Query Subject with complex query inside. Do not import tables etc. Just create QS in put your query inside.