Teradata - Pointers

When using Teradata, ensure that you thoroughly check the native SQL as well as have a look at the query that Cognos fires against the back-end.

As I have come across situations wherein the Native SQL looks weird cause there are some functions that Cognos doesn't seem to pass to Teradata even though the function used is valid in Teradata.

For example : sel current_time works in Teradata but when used in Cognos this doesn't get passed to the DB. This results in Native SQLs that may not be what is expected.

In one other case I had a master-detail relationship for burst reasons and the master and detail queries were joined on ID columns and a date column. In the query that Cognos fired against the DB I expected to see the detail queries having a filter on the ID columns and the date column. But that was not the case. The join on date column went completely missing from the detail query. This was being handled by the Cognos server rather than the DB.

So to figure out why this was happening, I set the processing to database only on all queries and validated the report.

This threw "requires local processing.." error because of some Cast. The report doesn't have any casted fields. Then I removed the join on Date field in the master-detail relationship. This then validated fine. Next step, I included a cast on date on both sides of the relationship, re-included the relationship and then validated. The report validated perfectly. I bursted the report and then noticed that the detail query included the required date filter.

Here's an advice in case you are using Teradata as your back-end or even otherwise as well. Build your report queries without using any Cognos functions to begin with. Set all queries to database only and then validate the report. After this if required then use Cognos functions. This way you are assured that whatever you expect to get passed to the DB gets passed. So you don't have any surprises like I did.