Creating and Using Teradata-Based Report Models

  • Article

New: 17 November 2008

You can generate report models based on a Teradata database running version 12.00 or version 6.20 by using Model Designer, Report Manager, or Microsoft Office SharePoint Server 2007 when running in SharePoint integrated mode.

Note

Before you can create a model based on a Teradata database, the system administrator must have installed the .NET Framework Data Provider for Teradata version 12.00.00.01 on the report server and the report authoring client. For more information, see Configuring Reporting Services for Teradata-based Report Models.

Connection Strings

To connect to a Teradata database, you must build or specify a connection string that identifies the database, the database server, and credentials. In addition, it is recommended that you specify a schema. If you do not specify a schema, all schemas that you have permissions to on the data source are retrieved. For more information, see How to: Create a Teradata Data Source File for a Report Model Project.

Version Support

To create a model from a Teradata database, you must use the correct version of the .NET Framework Data Provider for Teradata version 12.00.00.01. This data provider supports Teradata databases that are running version 12.0 and 6.20, and is available directly from Teradata Corporation. Work with the system administrator to verify that correct versions are installed and configured on your report authoring computer and on the report server. For more information, see Data Sources Supported by Reporting Services.

Functional Limitations

Because of the underlying behavior of the Teradata data provider, generating a model from a Teradata database differs from generating a model from other data source types as follows:

  • Primary Keys. The Data Source View wizard does not detect primary keys on a Teradata database. Primary keys are needed to generate a report model. You must use the Data Source View menu in the Report Model project in Business Intelligence Development Studio to define logical primary keys that can be used to generate a model. For more information, see How to: Create a Report Model with Primary Keys from a Teradata Database.
    If there are too many tables or objects for which to manually define logical keys, you can create a model with a data source view from the report server. A model that is generated on the report server includes a data source view that includes primary keys. After you create the model on the report server, you can download it to the report authoring client and import it into the report model project. After you add the model as an existing item, the data source view appears under the Data Source Views folder.
  • Semantic Model Definition Language (SMDL) query translator. The SMDL query translator has some behavioral differences when used with a Teradata database. These behaviors can include minor differences in functionality and configuration switches that are required to control workarounds for bugs and design limitations in the target database.

The following sections describe these differences.

Data Types

SMDL supports only the following Teradata data types.

SMDL Type Teradata Type

String

CHAR, CHARACTER, CHAR VARYING, CHARACTER VARYING, CHARACTER LARGE OBJECT, CLOB, GRAPHIC, INTERVAL DAY, INTERVAL DAY TO HOUR, INTERVAL DAY TO MINUTE, INTERVAL DAY TO SECOND, INTERVAL HOUR, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL MINUTE, INTERVAL MINUTE TO SECOND, INTERVAL MONTH, INTERVAL SECOND, INTERVAL YEAR, INTERVAL YEAR TO MONTH, LONG VARCHAR, LONG VARGRAPHIC, VARCHAR, VARGRAPHIC

Int16

SMALLINT

Int32

INT, INTEGER

Int64

BIGINT

Decimal

DEC, DECIMAL, NUMERIC

Double

DOUBLE PRECISION, FLOAT, REAL

Binary

BINARY LARGE OBJECT, BLOB, VARBYTE

Byte

BYTE

SByte

BYTEINT

Date

DATE

Time

TIME

DateTime

TIMESTAMP

For a Teradata data source, SMDL does not support any data type that is not listed above, including the following:

  • TIME WITH TIME ZONE
  • TIMESTAMP WITH TIME ZONE
  • Some USER DEFINED TYPE data types (UDTs)
  • DECIMAL with a precision over 29 digits that cannot be represented in the CLR Decimal data type. For example, a field on the data source might have a precision of 38 digits. Depending on the precision and scale of the data, retrieving the data at run time might cause an exception in the data provider. For more information, see the .NET Framework Data Provider documentation from Teradata.

When using Report Manager or Office SharePoint Server to automatically generate a report model, columns that contain unsupported data types are excluded. In addition, if an unsupported data type is part of a primary key, the entire entity is not generated.

When creating a report model using Model Designer, everything is added to the report model. Therefore, you will have to filter out all unsupported data types listed above manually when refining the model.

Functions

The behavior of some functions may depend on the underlying database. If you specify a value less than zero for the length argument in the Left, Right, or Substring function, the behavior is undefined in SMQL.

Defaults

The following defaults are used by Teradata:

Character string literals are from the Unicode character set.

The Date data type supports year, days, and months and does include time in hours, minutes, and seconds.

Configuration Switches

Due to the underlying data source, many configuration switches are available to modify the behavior of the query translator based on the specifics of the data source and the environment. The following switches are defined in the RSReportServer.config file, which is installed in the <SysDrive>:\Program Files\Microsoft SQL Server\MRRS10.MSSQLSERVER\Reporting Services\ReportServer\ folder by default.

Name Type Definition default Description

EnableMath

OpCasting

Boolean

False

Switch turns on casting of results of math operations into a Microsoft .NET Framework decimal format.

Certain math operations in queries may result in decimal values with a combination of precision and scale that cannot be represented in the .NET Framework decimal data type. While you can explicitly cast the results of such decimal expressions as decimal, it is unlikely that your users will know that this is necessary at design time. In the event that such expressions are frequently created, this switch can be set to True, to automatically perform the needed cast.

See Also

Concepts

Working with Model Designer
Data Sources Supported by Reporting Services
Connecting to a Data Source
RSReportServer Configuration File

Help and Information

Getting SQL Server 2005 Assistance