Avoiding Meta data Callbacks due to Hard-Coded SQLs

You would probably have noticed that Cognos creates meta data call-backs when you have reports based on Query Subjects that have SQLs hard-coded in them. To avoid such call-backs, import all the tables referenced by your query and leave them untouched. Cognos will reference the meta data from these tables while generating the SQL hard-coded in the query subject.

I would suggest it best to avoid hard-coding SQLs in query subjects but instead to create them as views in the DB and reference them through Cognos. This would help ensure better maintenance of objects and in easier impact-analysis of DB changes. Hard-Coded SQLs in query subjects should only be used when dynamicity is required through the usage of Cognos macros in the queries.
  

Metadata Callbacks due to Table Type not being Set

Recently while testing my FM Model I noticed that one of the query subject was causing metadata callbacks and this was with the message "The metadata for...will be retrieved from the Database due to the table type not set for query subject...". I verified that the model query subject did not have any relationships to other query subjects. The data source query subjects did not have any filters / determinants / calculations.

It was only when I tried re-creating the query subject that I figured out the cause for this as being a difference in the case on the table name in the Query Subject and in the Schema. The Query Subject had the table name as "TABLE" while in the schema it was "table".

Once I modified the data source query subject to point to "table" as in the schema, there were no metadata callbacks.
 

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.