deploying IBM Cognos 8 with Teradata as a data source.

This document provides a set of tips and techniques when deploying IBM Cognos 8 with Teradata as a data source.
The tips and techniques explained below are intended as a "help" tool; therefore, they should be treated as a recommendation or suggestion only. Every customer site is different; therefore, some of these tips and techniques may or may not apply.
The tips and techniques described below are an accumulation of experiences from the field at different customer sites.
This document should also be considered as "work in progress". New tips and techniques will be added as time goes by, and some of the existing tips and techniques listed below could be amended at any time without prior notice.
Document prerequisites
This document assumes the reader has a basic knowledge of Teradata, SQL, databases and a strong knowledge of IBM Cognos 8. This document also assumes the reader has a proficient knowledge of IBM Cognos 8 implementation and modeling proven practices.
It is strongly recommended that the reader be very familiar with the items listed in the References section below.
Scope of document
This document has been written for the following versions:

• IBM Cognos 8.2 BI, IBM Cognos 8.3 BI
• Teradata EDW V2R6.2

IBM Cognos integrates with Teradata at different levels. This document is broken down into the different levels of integration and the corresponding tips and techniques.
This document is based on a Teradata Star/Snowflake physical tables schema.
References

• IBM Cognos Product Documentation
• Data source connectivity
• Framework Manager
• Teradata documentation
• Teradata Product Documentation
• Teradata Deployment Best Practices
• IBM Cognos Proven Practices Documentation (other IBM developerWorks documents found under the Modeling section)
• IBM Cognos 8 Framework Manager - Flexible Model Solutions
• IBM Cognos 8 Framework Manager - Durable Models
• Modeling with Prompts and Parameter Maps

---

Back to top

Teradata Deployment
Profile data and identify Primary Index (PI) candidates
A typical mistake in designing a Teradata Primary Index is to simply combine a number of columns to evenly distribute data across Teradata AMPs. While data distribution is one of many design criteria, the primary focus should be on the business questions that will be posed by the business. A primary index is a unique feature of Teradata’s database – it offers the benefit of an index without the traditional overhead associated with an index.
There are two types of Primary Indexes – Unique Primary Index (UPI) and Non-unique Primary Index (NUPI). If there is a column in a table that constitutes a common access path (i.e., queries leverage that column most commonly to retrieve data from the table) then it can be considered a candidate for Primary Index. If it is unique then it would be a candidate for UPI; else it would be a candidate for NUPI.
Any query that utilizes the Primary Index column will encounter faster response times.
In order to define a quality primary index, data profiling (sometimes referred to as data analysis) is required. The following query can be used to choose a PI for certain tables based on data access and also to provide reasonable data distribution across the different AMPs:

select hashamp(hashbucket(hashrow())), count(*) from group by 1Leverage Teradata Database Statistics Regardless of schema (3NF or Star), the Teradata query optimizer relies on statistics to help it determine the best way to access data. Statistics also help the optimizer ascertain how many rows exist in tables being queried and predict how many rows will qualify for given conditions. When trying to improve performance of a specific report, there is a simple method for determining which statistics are worth collecting. First, connect to the data source using Teradata SQL Assistant. Execute a statement to turn on diagnostic helpstats for the remainder of the session ("diagnostic helpstats on for session"). Next, copy the report's native Teradata SQL from within IBM Cognos Report Studio. Paste the SQL into Teradata SQL Assistant's Query frame. Press the F6 key to perform an Explain. "High Confidence" results indicate columns that are good candidates for collecting statistics. Leverage partitions and Primary Indexes in filters and prompts. When authoring reports, be sure to leverage columns that have indexes wherever possible. If the database designer has created a Primary Partition Index on a table, queries should incorporate the partitioning column. When a join is executed that incorporates an index that is selective, any column in the dimension table can be used. The optimizer will deduce key value selection based on the functional dependency of the column within the row. When defining prompts in Report Studio, you can take advantage of two separate properties: "Use Value" and "Display Value". For example, designate Sales Org Code as the "Use Value" and Sales Org Description as the "Display Value". Partition elimination can be achieved by passing a literal value or a join. Leverage Teradata Soft Referential Integrity (Soft RI) Relationships between tables provide valuable input to the Teradata database optimizer. These relationships are identified through the creation of constraints between tables. The data volumes in data warehouse databases make traditional referential integrity constraints too costly. As a result, Teradata created Referential Constraints, which are commonly referred to as "Soft RI", by adding the phrase "WITH NO CHECK OPTION" to the constraint. This feature identifies which side is the master entity (driver) and which entity is the child and enhances the data access plan. Soft RI in conjunction with statistics may enable the optimizer to generate a more efficient access path. The following are some considerations for implementing Soft RI: Ensure that the column on which the Primary Key (maybe same as Primary Index in many cases) is to be defined in the driving table/dimension is unique. Non-unique columns cannot provide a Foreign Key reference to a different table. Ensure that both the Primary Key in the driving table and the Foreign Key in the target table are of the same datatype. The key columns on both the tables cannot be compressed. The statement to create Soft RI is shown below: ALTER TABLE FOREIGN KEY () REFERENCES WITH NO CHECK OPTION (); After enabling Soft RI, ensure that appropriate statistics are collected on all columns in the constraint.See the section "Using Referential Integrity" in the Teradata Database Administration manual. Back to top Teradata ModelingAvoid Outer Joins in the Physical Data ModelOuter joins may have a negative impact on query performance. Thisrecommendation applies to both 3NF and Star schemas for joins betweenvery large tables and is not specific to Teradata.Outer joins can be converted to inner joins by augmenting facttables with default values for foreign keys. This performance enhancingchange can be implemented without restructuring the physical data model.ODBC ConfigurationTeradata recommends their ODBC driver as the preferred method ofconnectivity. This ODBC driver has several options for which thefollowing is recommend.Store User credentialsIf the customer would like to use only one TD user account as aproxy user, then the user credentials should be stored at the ODBCdriver level rather than at the IBM Cognos 8 Connection level.In Windows, fill the "Username:" and "Password" fields: In UNIX, fill the options "Username=" and "Password=" in the ".odbc.ini" file. For example: [ODBC] InstallDir=/usr/odbc Trace=0 TraceDll=/usr/odbc/lib/odbctrac.so TraceFile=/usr/odbcusr/joe/trace.log TraceAutoStop=1 [ODBC Data Sources] financial=tdata.so [financial] Driver=/usr/odbc/drivers/tdata.so Description=NCR 3600 running Teradata V2R5.1 DBCName=123.45.67.10 DBCName2=123.45.67.11 DBCName3=123.45.67.12 Username=odbcadm Password=password Database= DefaultDatabase=sales When using this technique, it is recommended not to check the “UserID” option when creating the data source in IBM Cognos Administration. Use X ViewsThe TD ODBC driver provides several options.It is strongly recommended to turn off the option "Use X Views".This option will extend the time of Metadata calls on objects that arenot used by the IBM Cognos application.In Windows, deselect the option from the Teradata ODBC Driver Options. In UNIX, ensure that the option "UseXViews=" is not in the".odbc.ini" file. If the option is in the file, then make sure that isset to "No". For example: [ODBC] InstallDir=/usr/odbc Trace=0 TraceDll=/usr/odbc/lib/odbctrac.so TraceFile=/usr/odbcusr/joe/trace.log TraceAutoStop=1 [ODBC Data Sources] financial=tdata.so [financial] Driver=/usr/odbc/drivers/tdata.so Description=NCR 3600 running Teradata V2R5.1 DBCName=123.45.67.10 DBCName2=123.45.67.11 DBCName3=123.45.67.12 Username=odbcadm Password=password Database= DefaultDatabase=sales UseXViews=No No HELP DATABASEThe "No HELP DATABASE" option can also be turned on. This is veryvaluable when a multiple logical database query is sent to Teradata.Teradata will do all of the processing versus IBM Cognos 8 forcing thejoin in the middle tier.In Windows, check the option "No HELP DATABASE". In UNIX, ensure that the option "DontUseHelpDatabase=" is set to "Yes" in the ".odbc.ini" file. For example: [ODBC] InstallDir=/usr/odbc Trace=0 TraceDll=/usr/odbc/lib/odbctrac.so TraceFile=/usr/odbcusr/joe/trace.log TraceAutoStop=1 [ODBC Data Sources] financial=tdata.so [financial] Driver=/usr/odbc/drivers/tdata.so Description=NCR 3600 running Teradata V2R5.1 DBCName=123.45.67.10 DBCName2=123.45.67.11 DBCName3=123.45.67.12 Username=odbcadm Password=password Database= DefaultDatabase=sales DontUseHelpDatabase=Yes ODBC Advanced optionsThe TD ODBC also provides advanced options. In the advanced options, the "Maximum Response Buffer Size" can beadjusted. The default value is 8192. The maximum allowed value is1048575. There is no specific value that is recommended. Every customersite will require a different value. If the value is too high, it willimpact metadata calls. There is no side effect for using a low value.This option should be fully tested prior to deploying the option in Production.In Windows, enter the value in the "Maximum Response Buffer Size:" option. In UNIX, use the option "MaxRespSize=" with the desired value. For example: [ODBC] InstallDir=/usr/odbc Trace=0 TraceDll=/usr/odbc/lib/odbctrac.so TraceFile=/usr/odbcusr/joe/trace.log TraceAutoStop=1 [ODBC Data Sources] financial=tdata.so [financial] Driver=/usr/odbc/drivers/tdata.so Description=NCR 3600 running Teradata V2R5.1 DBCName=123.45.67.10 DBCName2=123.45.67.11 DBCName3=123.45.67.12 Username=odbcadm Password=password Database= DefaultDatabase=sales MaxRespSize=65477 If after changing this option, FM generates an error message whenimporting objects into the model, then the "Maximum Response BufferSize" needs to be reduced. Also test running the reports over and over.Test running one report several times before moving to the next report.Custom Catalog ModeIt has been noted that a small performance gain is achieved whenturning on the "Enable Custom Catalog Mode for 2.x Applications".This option should be fully tested prior to deploying the option in Production.In Windows, turn the option "Enable Custom Catalog Mode for 2.x application" on. In UNIX, turn on the option “USE2XAPPCUSTOMCATALOGMODE=”. For example: [ODBC] InstallDir=/usr/odbc Trace=0 TraceDll=/usr/odbc/lib/odbctrac.so TraceFile=/usr/odbcusr/joe/trace.log TraceAutoStop=1 [ODBC Data Sources] financial=tdata.so [financial] Driver=/usr/odbc/drivers/tdata.so Description=NCR 3600 running Teradata V2R5.1 DBCName=123.45.67.10 DBCName2=123.45.67.11 DBCName3=123.45.67.12 Username=odbcadm Password=password Database= DefaultDatabase=sales USE2XAPPCUSTOMCATALOGMODE=Yes Back to top IBM Cognos ConnectionWhen creating an IBM Cognos 8 data source connection, use the "Teradata (ODBC)" and not the "ODBC" option. Back to top IBM Cognos ModelingIBM Cognos 8 metadata modeling for Teradata is probably the most important task in the IBM Cognos 8 implementation.It is very important to receive some form of education on modelingin Framework Manager for a successful implementation and to takeadvantage of some of the IBM Cognos Modeling Proven Practices documentslocated on the IBM developerWorks website. Teradata best practices recommend connecting directly to theTeradata Business Views Layer (also known as the Semantic Views Layer)and not the Teradata Physical Tables. IBM Cognos 8 will be able toconnect to either level; however, it is recommended to follow theTeradata best practices.The Teradata Business Views should prevent READ Locks on the table: Create/Replace view {view name} as Locking Row for Access Select * From {table name} Local CacheIBM Cognos Framework Manager offers the ability to re-use a localcache of information rather than going to Teradata every single time.It is recommended to turn this option on unless going against theTeradata Active Data Warehouse. To do so, under the Project menu,select Edit Governors and then select Allow usage of local cache. SQL Join SyntaxSQL Join Syntax can have an impact on performance. It has been notedthat better performance gains can be achieved by using Explicit Joinsvs. Implicit Joins.To ensure the desired SQL Join Syntax is used, turn on the option in the project Governors dialog. WITH ClauseA small performance gain has been noticed when turning off the "Use WITH clause when generating SQL" option. Join on Like Data TypesQuery performance can be improved by leveraging like data types(i.e. INT32 to INT32) when joining foreign keys in a fact table toprimary keys in a dimension table. By making data types consistentwithin the DDL, there is less overhead associated with conversion ofdata at query run time.Create Durable ModelsTo minimize the impact of changes to an underlying data source, IBMCognos recommends using a layered approach to model design within IBMCognos Framework Manager. For information on how to implement this,please refer to the IBM Cognos Proven Practice document titled "IBMCognos 8 Framework Manager - Durable Models".Avoid Additional Metadata CallsIt is strongly recommended to minimize metadata calls to Teradata.To achieve this, keep the database/physical layer as clean as possible.Changes to this layer that can cause additional metadata calls includealtering the simple select statements on the SQL tab of a data sourcequery subject definition, and adding calculations or filters. Thosetypes of activities can take place at a higher level of modeling, suchas in a Business View of the metadata comprised of model query subjectsrather than data source query subjects. To find out if a database/physical query subject is making metadata calls or not, look at the "Response" of a Query Test.Example of a Query Test that is making Metadata Calls: Example of a Query Test that is NOT making Metadata Calls: To avoid metadata callbacks to the underlying data source, onerecommendation is to create a set of dedicated query subjects toprovide the cached metadata. To do so, one would create a FrameworkManager namespace in which all imported tables and views remainunmodified. These unmodified data source query subjects can then bereferenced by other query subjects, calculations, and filters withinthe model. Back to top Working in Report StudioWhen creating highly formatted reports using Report Studio, there are several advanced options that should be considered.ProcessingProcessing should be set as "Database only" as much as possible.Avoid "Limited Local" whenever possible. If "Limited Local" needs to beset, then re-visit the Framework Manager model to see if there is a wayto pass the processing to Teradata. Query Joins, Unions, Intersections and ExceptsIBM Cognos 8 provides the ability for the report developer tocombine their analysis across multiple Queries. This is very powerful;however, when the majority (if not all) of the reports require a largenumber of queries to be joined at the report level, it is recommendedto re-visit the database schema within Teradata to better leverage theprocessing power of the Teradata database.Execution MethodIBM Cognos 8 provides the ability to run multiple report queriesconcurrently or sequentially. By default, IBM Cognos 8 will run thequeries sequentially. The report developer can change the executionmethod to concurrent if desired.NOTE: Turning the option to Concurrent does NOT mean that the reportwill run faster. There are several reasons for which a report willprovide better performance when running sequentially. This feature mustbe fully tested before deploying the report to production. Back to top Testing and TroubleshootingIt is recommended to have access to non-IBM Cognos tools whentesting and/or troubleshooting. For example, if a Query Subject isgiving errors, the SQL Statement can be copied and pasted in one ofthese tools to validate the SQL Statement.Some Tools that are available are: On Windows, Teradata SQL Assistant On UNIX, Adhoc.c sample application Both tools will connect to Teradata via ODBC, which will also validate the SQL Statement against the ODBC driver as well. Back to top SummaryIBM Cognos and Teradata provide a very powerful BI/EDW solution totheir customers. The tips and techniques listed above are a combinationof observations at different customer sites. It is strongly recommendedto fully test each tip or technique listed above to ensure maximumperformance at your specific site.