Using SQL Server 2008 Reporting Services with the.NET Framework Data Provider for Teradata

SQL Server Technical Article

 

Writer: Houman Ghaemi, Simba Technologies

Contributors: Craig Guyer, Mary Lingel

Technical Reviewers: Cal Arabshahi, Aaron Myers, Rupal Shah

Project Editor: Deborah Dinzes

 

Published: October 2008

Applies To: Microsoft SQL Server 2008

 

Summary: This article discusses the connectivity and usage of Teradata database servers and the .NET Framework Data Provider for Teradata with Microsoft SQL Server 2008 Reporting Services (SSRS). For Teradata users that are new to working with Reporting Services, this article aims to present tips that can help make the most of SQL Server Reporting Services. For Reporting Services users that are new to using Teradata as a data source, the article serves as an introduction to working with different data types and characteristics that are specific to the Teradata database.

 

Introduction: Reporting Services 2008 Connectivity with Teradata

With the release of SQL Server 2008 Reporting Services (SSRS), Teradata users can take advantage of a rich report authoring environment. Now Reporting Services can interoperate with Teradata using the .NET Framework Data Provider for Teradata and Teradata users can take full advantage of Reporting Services capabilities without migrating data to another platform.

About This Document

This article is designed for customers and partners who are interested in using Reporting Services with a Teradata relational database. Topics covered in this article include:

·         Prerequisites, installation, and configuration

·         Teradata-specific terms and concepts

·         Teradata client tools

·         Report design from Teradata relational data sources

·         Report model design from Teradata relational data sources

·         Teradata native data types and report models

·         Troubleshooting tips

This article is intended to complement the documentation available in SQL Server Books Online. This article assumes a basic understanding of Reporting Services, report and report model authoring, and Report Builder 1.0. It is recommended that the reader complete the tutorials in SQL Server Books Online,**or acquire an equivalent know-how on the prerequisite topics.

Prerequisites

To use a Teradata data source with Reporting Services, you need the following components:

·         Microsoft SQL Server Reporting Services 2008

·         .NET Framework Data Provider for Teradata version 12.00 or higher.

If you are using a .NET Framework Data Provider for Teradata version 12.00.00.xx (xx stands for the minor release and revision number, such as 12.01), you need to add an additional step to your installation process for integration with Business Intelligence Development Studio in SQL Server 2008.

The .NET Framework Data Provider for Teradata installs and supports all required ADO.NET 2.0 interfaces and classes.

The following table summarizes the supported configurations with SQL Server 2008 Reporting Services:

	Teradata Database 12.00	Teradata Database 6.20
SQL Server  2008*	Data Provider 12.00.01.xx Data Provider 12.00.00.xx**	Data Provider 12.00.01.xx Data Provider 12.00.00.xx**

Higher Versions of .NET Framework Data Provider for Teradata

If you are using a .NET Framework Data Provider for Teradata version 12.XX (such as version 12.00.01.00) or higher, then you need to select the Publisher Policy option from the Select Features dialog box when installing the .NET Framework Data Provider for Teradata.

The publisher policy enables the .NET Framework to load a .NET Framework Data Provider for Teradata version other than version 12.00.00.00 at run time. The reason for using a publisher policy is that Reporting Services uses version 12.00.00.00 of the provider, and using a higher version of the provider requires a publisher policy to indicate to the .NET Framework that the higher version of the provider is compatible with version 12.00.00.00 of the driver.

Installation Overview

A default installation of Reporting Services has the capability of connecting with a Teradata database using the .NET Framework Data Provider for Teradata. The .NET Framework Data Provider for Teradata installer can be downloaded from the Teradata web site, which is located at https://go.microsoft.com/fwlink/?LinkId=130392.

For more information about installation of the .NET Framework Data Provider for Teradata, see the Readme file that accompanies the provider or search the Teradata web site for additional help.

SQL Server Business Intelligence Development Studio and Teradata

The SQL Server Business Intelligence Development Studio is the report and report model authoring environment of Reporting Services. This section gives a brief overview of Business Intelligence Development Studio, and any data source specific notes associated with using a Teradata data source in Business Intelligence Development Studio.

There are two types of report projects that can be created in Business Intelligence Development Studio. First, there is the report server project that is used for reporting against queries based on Teradata relational data. A report server project supports the traditional and full report design experience in Business Intelligence Development Studio.

Secondly, there is the report model project that is used to create an end-user–friendly semantic report model. In this way, end-users may leverage a predefined report model to author a report using Report Builder 1.0 without deep technical understanding of the underlying data sources. Both project types are discussed in more detail in the following sections.

Report Server Projects

The purpose of the report server project, in the context of Teradata, is to create a report by directly querying the database and using the rich report design options available in Business Intelligence Development Studio. The following is a summary of the steps that a typical user goes through to create and deploy a report:

1.    Within a report server project, a data source is created. Data sources are representative of the database connection.

2.    One or more datasets are created based on a data source. A dataset is a table of rows and columns that are returned by a query.

3.    A report, which is a visual representation of the data returned by the data set (or query), is created from the datasets.

4.    The report is deployed to the report server.

Upon deployment, end-users can request the server to process the report to see the final results. This section describes some of the steps involved in detail. Specifically, the steps are focused on nuances of working with Teradata as a data source. For more information about working with report server projects, see Reporting Services Tutorials.

Creating a Report Server Project and Data Source

1.    In Business Intelligence Development Studio, click File, then New, and then Project. Select Report Server Project from the list of project templates as shown in Figure 1.

Figure 1:  Creating a report server project

After you create a new project, you will see two nodes (or folders) in the Solution Explorer window. If Solution Explorer is not visible, you can activate it by selecting View then Solution Explorer, or alternatively, by using the keyboard shortcut Ctrl+Alt+L. The first node (or folder) is Shared Data Sources, and second is Reports.

At this point, you have two choices. First, you can create a shared data source to be used in all your reports, or second, create a report with its private data source.

2.    Create a shared data source by right-clicking on the Shared Data Sources folder and selecting Add New Data Source. Type a name for your data source and select Teradata for Type.

At this point, you can either type in a connection string by hand, as shown in Figure 2, or use Edit to open the Connection Properties dialog box which will allow you to create a connection string.

 

Figure 2:  Creating a shared data source

The following is an example of a connection string:

Data Source = 192.168.202.25; User Id = triumph ; Password = triumph; Session Character Set = UTF16;

3.    To build the connection string, click Edit which will open the Connection Properties dialog box shown in Figure 3.

Figure 3: Teradata Connection Properties dialog box

The Connection Properties dialog box is customized for Teradata and was installed when the .NET Framework Data Provider for Teradata was installed. Note that all fields are disabled until a server name or IP address is entered in the Server name box.

Note: The Teradata .NET component uses the Teradata system (TDPID or DBC-Name) name. For more information, see "Teradata Host Naming Convention"  section in this article.

After filling in your connection information, click Test Connection and you should see a message box noting ‘Test connection succeeded’. If you don’t see this message, then you may The Teradata .NET component uses the Teradata system (TDPID or DBC-Name) name. For more information, see "Teradata Host Naming Convention"  section in this article.

After filling in your connection information, click radata Authentication Mechanisms. You can change the authentication mechanism using the **Mechanism****box (as shown in Figure 3).

Also, the Advanced button enables you to edit all connection string properties. For more information about connection string properties, see the Teradata help files that are distributed with the .NET Framework Data Provider for the Teradata installation package.

Creating a New Report and Data Set - Report Server Project Type

 

1.    In Solution Explorer, right-click on the Reports node and then select Add New Report as shown in Figure 4.

Figure 4: Adding a new report in Solution Explorer

2.    The Report Wizard is now open. If you see the Welcome page, click Next to move to the Select the Data Source page as shown in Figure 5.

Figure 5: Specifying a data source

At this point, you have the option of selecting a shared data source or creating a new data source. Click Next to continue.

3.    On the Design the Query page, you can type in your query, or use the Query Builder to create a data set. You can open the Query Builder by clicking Query Builder as shown in Figure 6.

Figure 6: Report Wizard Query Builder page

4.    If you click Query Builder, then you will see the text-based query designer as shown in Figure 7.

 

Figure 7: Text-based query designer

Note:  The query designer that is available for Teradata-based data sources is a text-only query designer. This query designer allows you to directly edit the SQL command text sent to the database. A graphical query designer, such as the designer used for SQL Server, is not currently available when using the Teradata provider.  The text-only designer is used for several data source types in Reporting Services,and therefore is not optimized for any one data source type in particular. 

In the query designer window, the Command Type box contains three modes: Text, StoredProcedure, and TableDirect. The Text mode is the most commonly used and allows you to enter a standard SQL query as well as using a Teradata Macro for a dataset. The StoredProcedure mode can be used with those Teradata database versions which support stored procedures, version 12.0 and higher. TableDirect is not supported by SQL Server Reporting Services for a Teradata data source.

 

5.    After you enter your query and click OK, you will see additional steps in the wizard which allow you to format your report fields and finally you will see a report in the design view as shown in Figure 8.

 

Figure 8: Finished report in design view

Previewing and Deploying the New Report

1.    You can now see the dataset you created in the Report Wizard in the Report Data window. The Report Data window can be opened by selecting View, then Report Data. An example of a dataset is shown in Figure 9.

Figure 9: Report Data window showing new data sets

2.    To see how your new report looks, click Preview.  A sample report preview is shown in Figure 10.

Figure 10: Report preview

3.    The final step in the cycle of report creation is to deploy the report to a Reporting Services server where it can be viewed by many users. For more information about report deployment and processing, see Publishing Data Sources and Reports.

For more information about how to work with datasets and reports, see the following: Report Design Basics and Reporting Services in Business Intelligence Development Studio.

How to Explore Teradata Database Entities

As noted earlier, the Report Designer in Business Intelligence Development Studio provides a text-based query designer when working with a Teradata data source.

To assist in query creation, Server Explorer, found in Business Intelligence Development Studio, can be used to graphically explore a Teradata database structure as well as author queries which can be copied and pasted into a Reporting Services dataset.

To access Server Explorer in Business Intelligence Development Studio, select menu item View, then Other Windows, then Server Explorer. You can also use the keyboard short cut <Ctrl>+<Alt>+<S>. Server Explorer not only reveals the tables, views, and stored procedures; it also shows the fields, and primary and foreign keys of the tables.

 

Figure 11 shows an example of a Server Explorer window.

Figure 11: Server Explorer

 

Working with Query and Report Parameters

Report parameters allow end users to enter values (or use specified defaults), which are used when a report is processed. The new value replaces the parameter placeholder. For example, a report based on sales by country could support a parameter for country name. Therefore, the end user may choose the countries with which to filter the report at report execution time. There are two types of report parameters that one can create: a single-value parameter and a multi-value parameter.

The report parameters can be named or unnamed.

Teradata only supports unnamed parameters. An unnamed parameter is denoted by a ‘?’, and is merely a placeholder for data that is going to be entered at report processing time. For example, the following is a dataset with a single-value unnamed query parameter:

 

Select * from A where A.aid = ?;

 

With a multi-value parameter, the end-user has the option to select from a list of available values. The following query uses a multi-value parameter:

 

Select * from A where A.aid in (?);

 

For more information about the Reporting Services report parameters, see the following: Adding Parameters to Your Report and Using Single-Valued and Multivalued Parameters.

Working with Teradata Macros

Teradata macros are similar concepts to stored procedures. A Teradata macro can be called using exec <macro name>. A Teradata macro normally returns at least one result set. For example, the following macro will generate two result sets:

 

replace macro get_promo(ID smallint) as (

      sel * from promotion where promotion_id = :ID;

      sel * from sales_fact_1997 where promotion_id = :ID;);

 

The Reporting Services query designer will only return and process the first result set. If you have macros that return more than one result set from which you need the data, then you need to create wrapper macros that join the result sets into a single result set, or modify your report design to use multiple datasets; for example, showing two tables rather than one.

Report Model Projects

A report model is a semantic layer which describes the underlying database in business terms to ease creation of queries by end users. The report model maps the data and fields used in report definition to the schema in the underlying data source. Report authors can use the report model as the data source for a report instead of directly accessing a relational database. Therefore, report authors do not need the technical skills to build complex queries using SQL syntax, or even to understand the underlying database schemas. Report models are essentially metadata models of your database entities (for example, tables and views), and their relationship with each other, such as foreign key relations.

A report model definition is an XML file that follows the Semantic Model Definition Language (SMDL) specification. Therefore, report models are also referred to as semantic models, SMDL, or SMDL models.

Report Builder is a client tool that lets users create, edit, view, and save report definitions from report models. Report definitions are stored as an XML files that follow the Report Definition Language (RDL) specification, which is the same specification that all Reporting Services reports use.

The following is a summary of the steps that a typical user goes through to create and deploy a report model:

1.    Within a report model project, create a data source. Data sources are representative of the database connection.

2.    Create a data source view (DSV) based on the data source. A DSV represents all the objects in that data source that the model designer is interested in. Furthermore, a DSV also entails all the entity relations and logical primary keys of those entities.

3.    Generate a report model based on the DSV.

4.    Deploy the report model to the server.

5.    A report author uses the Report Builder application to author a report based on the report model.

6.    Save the report to the report server.

Creating a Data Source

To create a report model, you need to create a report model project. The following steps explain how to create a report model project:

1.    Create a new report model in Business Intelligence Development Studio by clicking on File, then New, then Project, and then select ‘Report Model Project’ from the list of project templates (on the right pane).

2.    Under the Shared Data Sources folder in Solution Explorer, right-click and select Add New Data Source.

If Solution Explorer is not visible, you can open it by selecting the menu item View, then Solution Explorer, or use the keyboard short cut Ctrl+Alt+L.

3.    In the Data Source Wizard, click Next if you see the Welcome page. On the Data Connection page, select NEW to create a new data connection. In the Provider box, select .NET Data Provider for Teradata as shown in Figure 12. Click OK to continue. If you don’t see the provider listed, then you have not installed the provider or your installation is not complete. For more information about the provider installation, see the Troubleshooting section.

Figure 12: Adding a new data source to a report model project

 

4.    In the Connection Manager dialog box, at a minimum, enter the server name and needed credentials.

Figure 13: Entering values for the connection string properties

Notethat the preceding ConnectionManager dialog box is the native .NET Framework Data Provider for Teradata dialog box that is installed with the provider.

Before proceeding, be aware that some additional connection string parameters can provide a better DSV experience when accessing large databases or if the user credentials you are using have access to a large number of schemas.

If you click All on the leftmost pane, you will see all of the connection string properties that are available for the .NET Framework Data Provider for Teradata. The following list includes properties relevant to the performance of the DSV autogeneration.

·         Set Use X Views to False.

By default, the Use X Views property is set to True. Use X Views limits the schema data to rows associated with the requesting user, such as objects the user owns, is associated with, has been granted privileges on, or is assigned by a role which has privileges. You can minimize DSV generation time by setting Use X Views to False and ensuring that the user name (or credentials) has been scoped appropriately for your reporting project.

·         Restrict to Default Database.

To improve the DSV experience, set this property to True. Setting this connection property to True limits the number of calls made by the Data Source View Wizard and restrict all schema collections to just the default database (see the Database property following).

·         Database.

By default, Database is blank. This is the database selected as the default database when a Teradata connection is opened. This can be set explicitly (see Figure 14) or it will default from the user profile. To minimize DSV generation time, set this parameter to the database for which you are creating a model.

 

Figure 14: Advanced connection properties

 

5.    After you click OK and close the Connection Manager dialog box, you see your data source added to the Data Sources folder in Solution Explorer.

Figure 15: Data source added to the project

 

Note: The Data Source Wizard allows you to build a connection string rather than editing one directly. However, after you have a basic data source created, you can edit the data source directly and update the connection string. The following example is a rich connection string including the settings for restricting the default database as well as disabling the use of X views:

Data Source = 192.168.202.25; User Id = triumph ; Password = triumph; Session Character Set = UTF16; Database = db_name; Use X Views = False; Restrict to Default Database = True;

 

Creating a Data Source View

1.    Right-click on the Data Source Views folder and add select Add New Data Source View to open the Data Source View Wizard.

2.    Click Next in the wizard if you see the welcome page. On the Data Source Selection page, ensure that the data source you created in the previous step is selected in the left pane and click Advanced. This will open the Advanced Data Source View Options dialog box where you can enter a schema name for restricting the DSV generation. This is useful if your Teradata login has permissions to many different schemas. Restricting to a specific database schema helps minimize DSV generation time. Click OK or Cancel to return to the Data Source Selection page.

Figure 16: Limiting the schema using the Advanced DSV options

 

3.    Click Next. This step in the wizard may take several minutes if the underlying schema is large or you did not restrict the schema search to a specific database in the previous step. The wizard is inspecting the data source you selected previously and will list the available objects from which you can choose which objects you want to be included in the DSV generation.

Figure 17: Data Source View Wizard Select Tables and Views page

 

Note: The Filter box can also be used for filtering the available objects list. For example, you could enter ‘store’ to return only those objects with ‘store’ in the name.

4.    Click Next to move to the confirmation page where you must provide a name for the DSV, and then click Finish for the wizard to create the DSV. The DSV will be saved and listed under the Data Source Views folder in Solution Explorer. You can then double-click the DSV to open it (as shown in Figure 18).

Figure 18: Data source view

Creating Logical Primary Keys

There is a known issue in .NET Framework Data Provider for Teradata version 12.xx  that causes the primary keys of the tables to be undetected during DSV generation in Business Intelligence Development Studio.

The existence of a logical primary key is essential for the next step, which is model generation. In Business Intelligence Development Studio, to work around the issue of primary key detection, you can assign a logical primary key to a table if it doesn’t have one. To assign a logical primary key, open the DSV, right-click the desired field and select Set Logical Primary Key, as shown in Figure 19. You can then also set new relationships using the same method.

Figure 19: Context menu for setting the logical primary key

 

Alternatively, you can generate a DSV and a report model using the Report Manager web interface, which does use the primary keys from your underlying data sources schema..

Generating a DSV using Report Manager is explained in the section ‘Generating a Report Model on the Reporting Services Server’ in this document. After generating a model on the report server, you can download and save the model to your file system and then add the model, which also contains a DSV, to your report model project.

For more information about logical primary keys, see Defining Logical Primary Keys in a Data Source View.

If you have already generated a model, then you must regenerate the model after assigning logical primary keys to tables, to reflect these changes.

If needed, you can use the Server Explorer to browse through your database tables to view the primary keys and indices of your tables interactively. You can then create logical primary keys.

Creating a Model from a Data Source View

A semantic model is generated from a DSV. A model may be generated from Report Manager or from Business Intelligence Development Studio. The following steps use Business Intelligence Development Studio.

1.    Right-click on the Report Models folder in Solution Explorer and select Add New Report Model.

2.    On the first page of the wizard, you are offered a choice of available DSVs. Select a DSV and click Next.

3.    On the second page of the wizard, you can modify the model generation rules. For information about each option, see Select Report Model Generation Rules (Model Designer).

4.    Click Next on the Report Model Wizard and you will see the Collect Model Statistics dialog box as shown in Figure 20. If the DSV from which the model is being generated is not up-to-date, then you have to select Update model statistics before generating.

Figure 20: Collect Model Statistics page

 

The DSV statistics are statistical metrics extracted from your database entities, such as average and maximum width of your columns, or standard deviation of width, and so on. These statistics are used in the model generation process to set some model entity and attributes properties.

5.    Continue through the wizard and pay attention to any errors or warnings that appear on the wizard. You may see warnings related to the detection of primary keys. As was explained at the end of the preceding section, the .NET Framework Data Provider for Teradata version 12.xx, in conjunction with the Data Source View Wizard, does not detect primary keys. They need to be added manually. The DSV can be created on the server, where primary keys are detected.

When the model generation is complete, you will have one data source definition, one DSV, and one report model, as shown in Figure 21.

Figure 21 Completed report model generation

Deploying the Report Model to Your Server

After you have created your DSV and report model, you are ready to publish the report model to the report server where it can be used by Report Builder 1.0 to build reports.

1.    Right-click on the solution name in Solution Explorer (for example, Report Model Project7 in Figure 21), and select Properties. Make sure that the TargetServerURL property is a valid report server URL. Then, click OK to dismiss the Properties dialog box.

2.    Right-click on the solution again and this time select Deploy from the context menu.

Check the progress of the deployment by looking at the solution output window. If the output window is not visible, you can open it by pressing Alt+Ctrl+O. Make sure that model deployment succeeds. For more information, see Publishing a Report Model Project.

Generating a Report Model on the Reporting Services Server

The procedures in this topic include steps to create a Teradata-based model on a report server configured in native mode. If you have a model created from within Business Intelligence Development Studio, you may proceed to the section Creating Reports from the report models. For more information, see How to: Create a Model Using Report Manager.

On the report server, you can create a model from a shared data source that specifies a connection string to a Teradata database. When you generate the model, it includes a DSV that automatically specifies primary keys for tables and views. To view or modify the model, you can load it into a report model project in Business Intelligence Development Studio.

 

To Create a Shared Data Source on the Server

1.    Connect to Report Manager on your report server. For example http://myreportserver/Reports.

2.    In Report Manager, locate a folder where you have permissions to create a new data source.

3.   

Click New Data Source, as shown in Figure 22. The New Data Source page opens.

Figure 22

 

4.    Type a name for the item. A name must contain at least one character. It can also include spaces and certain symbols, but not the characters ; ? : @ & = + , $ / * < > | " /.

5.    Optionally, type a description to give users information about the connection. This description will appear on the Contents page in Report Manager.

6.    In the Connection Type list, specify the data source type TERADATA.

 

Note:

The data source type TERADATA appears only if the Teradata data provider is installed on the report server. If you do not see TERADATA as a choice, work with your system administrator to ensure the appropriate Teradata components are installed.

 

7.    For Connection String, it is recommended that you do not specify credentials in the connection string.

The following example illustrates a connection string for connecting to a Teradata relational database on a server specified by an IP address, where N represents a digit:

Data Source = NNN.NNN.NNN.NN; User Id = abc; Password = 123; Session Character Set = UTF16;

8.    For Connect using, specify how credentials are obtained when the report runs:

·         If you want to prompt the user for a logon name and password, select Credentials supplied by the user running the report.

·         If you intend to use the data source as a shared data source for multiple users, or with reports that support subscriptions or other scheduled operations (such as automated report history generation), select Credentials stored securely in the report server.

9.    Click OK.

 

For more information about how to configure credentials, see Specifying Credential and Connection Information for Report Data Sources.

To Create a Model on the Server

Creating a model using Report Manager does not require you to manually create a DSV as you did in Business Intelligence Development Studio. If you open a server-generated model inside Business Intelligence Development Studio, a DSV will be automatically extracted from the model information.

1.    In Report Manager, locate the shared data source.

2.    Click the item to open it. The General Properties page opens.

3.    Click Generate Model at the bottom of the page. The Generate New Model page opens.

4.    Type a name for the item.

5.    (Optional) Type a description for the item.

6.    Click OK.

7.    Click Apply.

Reporting Services generates a model for the data source. The model automatically includes primary keys.

NOTE: if you do not see Generate Model in Report Manager, contact your system administrator as they may need to adjust report server permissions. For more information, see Granting Permissions on a Native Mode Report Server and Designing and Implementing Reports Using Report Builder.

To Save the Model on Your Computer

1.    In Report Manager, locate the data source for which you created the model in the previous procedure.

2.    Click Edit. The File Download dialog box opens.

3.    Click Save. The Save As dialog box opens.

4.    Navigate to the location on your computer where you want to save the model.

The model is saved with the file name extension .smdl.

Add the Server-Generated Model to a Business Intelligence Development Studio Project and View Primary Keys

1.    In Business Intelligence Development Studio, create a new report model project.

2.    From the Project menu, click Add Existing Item.

3.    Navigate to the location where you saved the model in the previous procedure.

4.    Click the model file.

5.    Click Add. The model is added to the project. In Solution Explorer, the data source view and the model are added to the project, and the model opens in Design view.

6.    In Solution Explorer in the Data Source Views folder, right-click the data source for the model that you imported, and then click View Designer. The data source view opens in Design view.

Each table or view in the data source view appears on the design surface. In each table, columns that are specified as primary keys display a key symbol in front of the column name.

Creating Reports from the Report Models

A report model is a set of metadata about a data source; that is, metadata describing the actual data types and structure in the data source.

Instead of having to write SQL queries to retrieve data from a relational data store, a report author can use a tool with a graphical user interface, called Microsoft Reporting Services Report Builder 1.0, to perform similar tasks in an interactive way.

You can browse to the report server home and download the Report Builder tool using the tool bar link as shown in Figure 23.

Figure 23 Report Builder link on the Report Server home

If the Report Builder button is not visible, contact your system administrator as they may need to adjust report server permissions.

For more information about permissions and where to find the Report Builder tool, see Designing and Implementing Reports Using Report Builder.

A report author who is creating a report using Report Builder does not need knowledge of the internal workings of the relational database system from which the model was originally created. Thus, the Report Builder functionality is independent of the model data source.

After you design a report in Report Builder, you can preview and save this report on the server. Figure 24 shows a sample report in the design state in Report Builder.

 

Figure 24: Report Builder

Teradata and the Semantic Query Engine

When you use a filter, or apply a function to some of the fields in your model in Report Builder, a semantic query function is added to your report definition file. The Semantic Query Engine component builds and executes the appropriate SQL queries against the Teradata relational database using the report definition file. Therefore, the Semantic Query Engine translates the semantic queries in your report definition to SQL syntax that is consequently executed on the Teradata database and the results of this execution are represented as the rendered report.

The following section explains recommendations and specific behavior when dealing with some of the Teradata specific data types.

For more information, see Working With Models.

Working with Teradata Data Types and Semantic Query Functions

Most of the Teradata native data types are supported in Reporting Services. The following table shows the mapping from Teradata database data types to the .NET Framework types.

 

Teradata database type	System.Data.DbType mapping
BIGINT	Int64
BINARY LARGE OBJECT, BLOB	Binary
BYTE	Byte
BYTEINT	Sbyte
CHAR, CHARACTER	StringFixedLength
CHAR VARYING, CHARACTER VARYING (same as VARCHAR)	String
CHARACTER LARGE OBJECT, CLOB	String
DATE	Date
DEC, DECIMAL	Decimal
DOUBLE PRECISION	Double
FLOAT	Double
GRAPHIC	StringFixedLength
INT, INTEGER	Int32
INTERVAL DAY	StringFixedLength
INTERVAL DAY TO HOUR	StringFixedLength
INTERVAL DAY TO MINUTE	StringFixedLength
INTERVAL DAY TO SECOND	StringFixedLength
INTERVAL HOUR	StringFixedLength
INTERVAL HOUR TO MINUTE	StringFixedLength
INTERVAL HOUR TO SECOND	StringFixedLength
INTERVAL MINUTE	StringFixedLength
INTERVAL MINUTE TO SECOND	StringFixedLength
INTERVAL MONTH	StringFixedLength
INTERVAL SECOND	StringFixedLength
INTERVAL YEAR	StringFixedLength
INTERVAL YEAR TO MONTH	StringFixedLength
LONG VARCHAR	String
LONG VARGRAPHIC	String
NUMERIC	Decimal
REAL	Double
SMALLINT	Int16
TIME	Time
*TIME WITH TIMEZONE	String (not supported)
TIMESTAMP	DateTime
*TIMESTAMP WITH TIMEZONE	String (not supported)
*user-defined type (UDT)	Partially supported. See Note following.**
VARBYTE	Binary
VARCHAR	String
VARGRAPHIC	String

* These types are not supported because their type mapping to System.Data.DbType types were not compatible with types that semantic query engine functions expected.

** Note: There are two types of UDTs: distinct, based on a single predefined type, and structured, that is a collection of one or more fields (similar to a C language struct). For structured UDTs, the Teradata database transforms the structured UDT into a primitive data type when it is selected. Distinct UDTs are converted to their underlying primitive type. In practice, the database column type is exposed using the Columns schema as a UDT. However, when the field data is read, the underlying primitive type is returned. For example, if a UDT is defined as an Integer, then the data reader will report the column type as Int32 and the GetInt32 can be used to retrieve column values.

The INTERVAL data types appear as character strings using the .NET Framework Data Provider for Teradata. Teradata supports using ANSI interval expressions and arithmetic operators on interval data types as well as certain aggregation functions. However, be aware of certain string functions, such as RTrim and LTrim, which do not yield meaningful results when used over database fields of type INTERVAL, or which may result in errors at the query execution time.

Teradata Host Naming Convention

Teradata .NET Framework Data Provider version 12.xx performs the following actions to resolve the host name that is entered into the Connection Properties dialog box  to an IP address:

1.    It will attempt to resolve the hostname using the Teradata host naming convention (TDPID or DBC-Name).

2.    If it cannot resolve the host name using the preceding method, then it will attempt to resolve the server name as it is entered by the user.

Teradata users are most familiar with the Teradata system naming conventions. However, for the Reporting Services users, the host naming convention is explained as follows:

The hostname that is entered into the Server name box in the Connection Properties dialog box is appended with a COPn suffix, where n is a sequential number starting from 1, before resolving the hostname to a network address. Hence the number n corresponds to the number of Gateways (COP) supported by the system. For example, to connect to a system called MYDATA, the following entry is required in the hosts file, which is located under %SystemRoot%\System32\drivers\etc\hosts (assuming the MYDATA machine IP address is 10.0.0.1):

 

10.0.0.1    MYDATAcop1

 

Then, the .NET Framework Data Provider for Teradata resolves the hostname properly. The system name is also restricted to eight characters or less (the system name refers to the part without COPn suffix; MYDATA in this example).

Alternatively, you can use an IP address instead of the hostname to establish a connection.

Known Issues

Working with Large Rows

The maximum row size for Teradata is approximately 64,000 bytes (roughly 64KB). If a SQL or semantic query (queries generated by the Report Builder using a report model) requires row sizes larger than 64KB, then the database will generate an error similar to the following:

 

[Teradata Database] [3577] Row size or Sort Key size overflow

 

If you ever encounter this error, then you may need to restructure your query to accommodate this Teradata 64KB row limit size.

Working with BLOB and CLOB in the Report Models

Teradata database system version 12.xx has a restriction with regards to operations on CLOB and BLOB fields. This restriction might be encountered while working with semantic queries, as more complex SQL statements are executed at the database level. While working with hand-crafted queries, there is much less chance of creating such queries. The following is the exact wording of this error:

 

[Teradata Database] [5690] LOBs are not allowed to be hashed

 

This error does not occur with version 6.2 of the Teradata database system.

Teradata INTERVAL Data Type Range

Teradata databases have a limit of 9999 units on the INTERVAL data type. Therefore, if a SQL or semantic query exceeds this limit, it generates an error. For more information about the INTERVAL data type and its usage, see Teradata reference manuals. Generally, the INTERVAL data type is used for performing arithmetic operations on the TIMESTAMP type.

For example, assume that you have a table called promotions and this table has a columns called start_time and duration; with duration  in hours. To obtain the end date of all promotion campaigns, write the following query:

sel

    start_time as “StartTime”,

    StartTime + cast(duration as interval hour(4)) as “EndTime”

 from promotions;

 

If you have a promotion with a duration greater than 9999 hours, the query will generate an error, because you are using an interval type of greater than 9999 units.

You can run into similar issues when using semantic queries. For example, assume your are using DateDiff and DateAdd functions in Report Builder to subtract the time difference in hours of two columns and then add that difference to a third column. If the number of hours obtained from the first operation is greater than 9999 hours, then you will exceed the INTERVAL type limit.

Installing oReplace User-Defined Functions

By default, Teradata databases do not have a string REPLACE function. A REPLACE function is used for replacing all instances of one string with another. For example, if you replace all instances of aa in Faa with ee, then you will end up with Fee.

However, REPLACE functionality is required by Reporting Services, specifically if Replace functionality is used in Report Builder with your report model. Luckily, Teradata offers a group of User-Defined Functions (UDF) called Oracle UDFs for compatibility purposes. The REPLACE function is one of these UDFs. For more information and to download the UDF bundle from Teradata, see http://www.teradata.com/DownloadCenter/Topic9228-137-1.aspx.

Note: The REPLACE Oracle UDF is called oReplace, perhaps to distinguish that it is an Oracle UDF. The following sequence of commands must be used to install the oReplace UDF:

REPLACE FUNCTION oreplace(

         Str VARCHAR(4000),

       aFrom VARCHAR(512),

         aTo VARCHAR(512)

)RETURNS   VARCHAR(4000)

LANGUAGE C

NO SQL

SPECIFIC oreplace2

EXTERNAL NAME 'SC!oreplace2!<fullpath>/oreplace2.c'

PARAMETER STYLE SQL;

The oReplace is written in C, and upon issuing the preceding commands. The code is compiled and installed on the server. However, before you install this UDF, make sure that values in bold are adjusted based on your requirements. Specifically, you must adjust the value pertaining to the sizes of input and output strings, which is 4000 in the preceding code sample.

Setting the return output to 64KB will trigger the error that was described in the preceding Working with Large Rows section, and setting it too low may truncate your strings without notification. The best solution is to adjust these values based on your reporting requirements before installing the UDF.

Troubleshooting and Additional Information

Version 12.0 of the .NET Framework Data Provider for Teradata does not automatically integrate with Visual Studio 2008 (and therefore Business Intelligence Studio 2008). If you installed version 12.0 of the .NET Framework Data Provider for Teradata, and you are not able to access Teradata using Business Intelligence Development Studio 2008, it may be that you have not performed the additional steps to integrate the provider with the Visual Studio 2008 and Business Intelligence Development Studio 2008 environments.

The 12.0.0.1 update of the provider includes an additional installation package that performs the necessary steps to integrate .NET Framework Data Provider for Teradata with Visual Studio 2008 (and Business Intelligence Development Studio 2008).

For more information, see the installation instructions included with the provider at http://www.teradata.com/DownloadCenter/Topic9240-146-1.aspx.

Teradata Authentication Mechanisms

The following figure shows the authentication mechanisms available in the Teradata Connection dialog box and which a report author can use while creating a data source in Reporting Services.

Figure 25: Authentication mechanism

 

The authentication mechanisms are described as follows:

·         SPNEGO (The Simple and Protected GSS-API Negotiation): SPNEGO protocol negotiates different security mechanisms. It is used to negotiate with the Teradata Gateway to use Microsoft Kerberos on Windows.

·         TD2 (Teradata Method 2): Uses a user name and password to authenticate.

·         LDAP (Lightweight Directory Access Protocol):  LDAP is a standard directory protocol that can be used for authentication as well. For more information about LDAP, see LDAP.

For more information about the authentication mechanisms, session security, and Default authentication mechanism, see the help file that accompanies the .NET Framework Data Provider for Teradata.

 

Exception Caught Instantiating TERADATA Report Server Extension

After you install Reporting Services, you might see the following error message in the Reporting Services log file and the system event log:

 

"Exception caught instantiating TERADATA report server extension."

 

This error is logged under the following circumstances:

·         A new installation of SQL Server 2008 Reporting Services.

·         Each time the Report Server service restarts.

This error occurs because the Teradata extension is registered in the Reporting Services configuration file by default, but the Teradata provider is not shipped with SQL Server 2008 or as part of the .NET Framework. You can ignore this error if you are not planning to need Teradata connectivity. However, if you want to work around this issue, do one of the following:

·         Open the Reporting Services configuration file (Reportserver.config), and remove or comment out the Teradata extension. Do this only if you do not require functionality that the Teradata extension provides.

·         Install the .NET Framework Data Provider for Teradata. Do this only if you require functionality that the Teradata extension provides. You can obtain the provider from the Teradata Web site. Reporting Services requires that the provider be version 12 or later.

You may see a similar exception when trying to deploy a report server, or report model project, to a report server which does not have the provider installed, and when the project contains references to the .NET Framework Data Provider for Teradata:

 

An attempt has been made to use a data extension 'TERADATA' that is either not registered for this report server or is not supported in this edition of Reporting Services.

 

If you encounter this error, then you will need to install the.NET Framework Data Provider Teradata on the report server. For more information, see the Prerequisites section.

Conclusion

This paper explained usage of Teradata as a Reporting Services data source. It also explored tips and tricks of working with Teradata and Reporting Services.

For more information:

SQL Server Reporting Services: https://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx

SQL Server Reporting Services Forum: https://forums.microsoft.com/Forums/ShowForum.aspx?ForumID=82&SiteID=1

SQL Server TechCenter:

https://technet.microsoft.com/en-us/sqlserver/default.aspx

SQL Server DevCenter:

https://msdn2.microsoft.com/en-us/sqlserver/default.aspx

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

·         Are you rating it high due to having good examples, excellent screenshots, clear writing, or another reason?

·         Are you rating it low due to poor examples, fuzzy screenshots, or unclear writing?

This feedback will help us improve the quality of white papers we release. Send feedback.