Using SQL Server 2008 Reporting Services with the.NET Framework Data Provider for Teradata
.gif)
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 http://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.
.jpg)
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.
.jpg)
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.
.png)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
Figure 13: Entering values for the connection string
properties
Note
that the preceding Connection Manager 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.
.jpg)
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.
.jpg)
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.
.jpg)
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.
.jpg)
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).
.png)
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.
.png)
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.
.jpg)
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.
.jpg)
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.
|
.png) 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.
.jpg)
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.
.jpg)
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.
.png)
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: http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx
SQL Server Reporting Services Forum: http://forums.microsoft.com/Forums/ShowForum.aspx?ForumID=82&SiteID=1
SQL Server TechCenter:
http://technet.microsoft.com/en-us/sqlserver/default.aspx
SQL Server DevCenter:
http://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.