• Article

Creating and Using Teradata-Based Report Models

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 connect a Teradata data source, the system administrator must have installed the correct version of the .NET Data Provider for Teradata on your report authoring client and on the report server. For more information, see Connecting to a Data Source (Reporting Services).

Connection Strings

When building a report model based on a Teradata database, the connection string has the following format:

Database=<data base>;Data Source=<IP address>;userid=<user name>;password=<password>

Important

When you generate a data source view for your Teradata model, it is recommended that you specify a schema. Otherwise, all schemas that you have access to on the data source will be retrieved. For more information, see How to: Retrieve Data from a Teradata Data Source.

Version Support

To create a model from a Teradata database, you must use the correct version of the .NET Framework Data Provider for Teradata for the version of the Teradata database that you are using. This component is available from Teradata. Work with the system administrator to verify the correct versions are installed 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 database, 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 Primary Keys for a Teradata-Based Report Model (Reporting Services).

    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 Teradata .NET Framework Data Provider documentation for the version of the data provider that is installed on the report authoring client and on the report server.

When using Report Manager or Office SharePoint Server  to autogenerate 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.