How to: Retrieve Data from a Teradata Data Source
To use data from a Teradata relational database in your report or report model, you must define a Teradata data source. When you define the data source, you must specify a connection string and credentials so that you can access the data source from the client computer.
Before you can connect to a Teradata data source, the system administrator must have installed the correct version of the .NET Data Provider for Teradata on your report authoring client and on the report server. For more information, see Connecting to a Data Source (Reporting Services).
The following list applies to working with a Teradata database in a report server project:
You can create an embedded data source definition for use by a single report or a shared data source definition that can be used by multiple reports. The procedures in this topic describe how to create an embedded data source. For more information about shared data sources, see Shared Data Sources and Embedded Data Sources and Creating, Modifying, and Deleting Shared Data Sources.
After you create a Teradata data source definition, you can create one or more datasets. After you create a dataset, the name of the dataset appears in the Report Data pane as a node under its data source. For more information, see Working with Report Designer in Business Intelligence Development Studio.
After you publish your report, you may want to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.
The following list applies to working with a Teradata database in a report model project:
You can create a data source definition in a report model project by using the Data Source Wizard.
After you create a Teradata data source definition, you can create data source views. For more information, see Creating and Using Teradata-Based Report Models.
To create an embedded data source that uses the .NET Managed Provider for Teradata data processing extension
On the toolbar in the Report Data pane, click New, and then click Data Source.
In the Data Source Properties dialog box, type a name in the Name text box or accept the default name.
Verify that Embedded connection is selected.
From the Type drop-down list, select TERADATA.
Specify a connection string that works with your Teradata data source.
Contact your database administrator for connection information and for the credentials to use to connect to the data source. The following connection string example specifies a Teradata database on the server specified with an IP address:
Data Source=<IP Address>
Set the credentials to use to connect to the data source. For more information, see Specifying Credential and Connection Information for Report Data Sources.
The data source appears in the Report Data pane.
To create a dataset for a Teradata data source
In the Report Data pane, right-click the name of the data source that connects to a Teradata data source, and then click Add Dataset.
In the Query page of the Dataset Properties dialog box, type a name in the Name text box or accept the default name.
In the Data source text box, verify that the name of the data source that you right-clicked in step 1 appears.
Verify that Text is selected in the Query type box. Text is the only mode supported for a Teradata database.
Click Query Designer to open the text-based query designer.
In the Query pane, type a Transact-SQL command.
Alternatively, you can paste a query, or import an existing Transact-SQL query from a file or from another report. For more information, see Text-based Query Designer User Interface.
Click Run Query (!) to run the query and see the result set.
The dataset and its field collection appear in the Report Data pane under the data source node.
To create a data source for a report model from a Teradata database
In a Report Model project, in Solution Explorer, right-click Data Sources, and then click Add New Data Source. The Data Source Wizard dialog box opens.
Click Next. The Select how to define the connection page opens.
Click New. The Connection Manager dialog box opens.
In Provider, from the drop-down list, select the data provider for Teradata. For example, .NET Data Provider for Teradata.
In the following steps, if you do not know the information requested, contact your system administrator.
In Server name, type the IP address for the server.
In Login Details, for User name and Password, type the name and password of an account that has access to the server you specified in the previous step.
Click All. The connection properties appear in a grid.
In Data Dictionary, fill in the following three values.
Database. Type the name of the database.
Restrict to Default Database. Set to True.
Use X Views. Set to False.
These settings restrict the data source view to the default database to improve data retrieval time. If you do not apply these settings, all schemas that you have permissions to on the data source server are retrieved. For more information about these settings, see the third-party documentation from Teradata for the .NET Framework Data Provider for Teradata.
In the Select how to define the connection page, click Next.
In Data source name, type a name or accept the default.
The new data source appears in the Report Data pane under the Data Sources folder. You can now proceed to create a data source view for the Teradata database. For more information, see Creating and Using Teradata-Based Report Models.