FM and Teradata Stored Procedures

I have been working on using Stored Procedures to update tables through Event Studio based on a condition. The back end DB is Teradata.

With Teradata if you have a separate schema to create Stored Procedures, the schema will not show up in FM when you use the run meta data wizard unless it has at least 1 table or view. To overcome this issue, a dummy table was created in the schema after which the schema showed up in FM. I then imported the SP. The SP is a data modification SP. Teradata SPs cannot be run through FM or through Event Studio because Cognos fires a "CALL SPName" statement rather than an "EXEC SPName" statement.

To work around the above issue create macros and use them through FM or Event Studio. This is only for Teradata. Oracle Stored Procedures do not have any issues.
 

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.
 

Metadata Callbacks and Teradata Data Sources

For the past couple of days we have been stumped with an issue. We are using Teradata as our DB and any report seems to be firing numerous Help Column statements against the DB. And these are numerous statements ranging in 100 - 300 depending on the tables we are hitting.

Setting the Governor Property - Allow enhanced model portability at run time - unchecked is not helping. When you look at the query response though you will notice that for metadata callbacks though the information provided is that the metadata information retrieved is from the Query Subject (QS) that doesn't seem to be the case.

Here's what seems to be working: Removing the Data Source name from the Data Source Query Subjects and replacing it with the schema names and removing the schema name component from the Data Source in FM.

For example replace the SQL:

Select * from [TEST].Table1

To

Select * from Test_Schema.Table1

This seems to have removed the "Help Column" statements.

Here's my take on how this solution works: For Teradata based QS, when the schema name is set and the QS select statements are based on the Data Source, Cognos doesn't probably store metadata information since schema name resolution is dynamic and hence metadata is not being stored or Cognos is forced to go the DB for metadata retrieval.

Schema name provided as part of the Data Source is not stored as part of the metadata resulting in no storage of metadata for the QS and this could be getting resolved during run time thus forcing Cognos to retrieve metadata from DB.

When the schema name is removed from the Data Source and when the schema name is provided as part of the QS, we are informing Cognos that this is the schema name and will not change unless we change and save the model during which time the metadata for the QS also gets saved. In this case Cognos probably stores the metadata information and is not required to go to the DB for metadata retrieval.

Any thoughts on the same?

And here is a Reader Input. A big thanks to Greg for sharing the below information -

Greg -

Cognos will always have situations where it makes metadata callbacks to the data source, and you should always attempt to model in a manner that reduces these whenever feasible. Teradata is different in that it processes metadata call backs at a much lower priority than other request types when compared to other databases, so you need to be very mindful of how you model metadata on top of TD data sources in order to minimize metadata callbacks entirely. You should not have to remove the data source aliases from the data source query subjects - doing so makes your model less portable and could complicate future updates.