Strategy One
Connect via API
Teradata Parallel Transporter API (TPT API) is an alternative means to loading and unloading data between a Teradata Database Server and Client application apart from the traditional ODBC. These capabilities can be extended with customizations or third-party products. Teradata over the years has built a number of utilities to exchange data with, these include FastLoad, MultiLoad, TPump, and FastExport. TPT API combines all these utilities in a way that they are accessible via a single scripting language utility.
There are several benefits to using TPT API. One of the biggest benefits of Parallel Transporter is the ability to scale the load process on a client load server to circumvent performance bottlenecks. This can offer huge performance gains as compared to the legacy Teradata Stand-alone load tools, such as FastLoad and MultiLoad.
For example, in the following figure, the Stand-alone load tool has a single process that is either reading data from a single data source or it is feeding the data into buffers that are sent to the Teradata Database through multiple sessions.
In contrast, the Parallel Transporter job on the right is using multiple processes to read the data asynchronously and pass the data to a buffer in-memory referred to as the data stream. Asynchronously, multiple processes (Load Operators) are reading the data from the data stream and filling up buffers that are sent to the Teradata Database over multiple sessions.
The architectural advantages of Parallel Transporter are multiple processes running asynchronously (functional parallelism) used to scale around CPU bottlenecks and parallel data flows (data parallelism) used to scale around I/O bottlenecks.
MicroStrategy has specifically implemented the Fast Export protocol utilizing the Export Operator from TPT API and has been able to export data quickly out of Teradata into MicroStrategy cubes. The FastExport protocol is capable of exporting data out of Teradata utilizing parallel sessions and, therefore, has a higher throughput rate than a single session traditional to ODBC. MicroStrategy started support for TPT API in MicroStrategy 10.0, the supporting OS are Windows and Linux.
In order to connect via TPT API, you must install certain Teradata software on the Intelligence Server‘s host so that the server can connect via TPT API. TPT API is a CLI application (vs. ODBC), therefore, in addition to ODBC, Teradata CLIv2, and TPT API (which is currently included in the TPTBASE package) are required. MicroStrategy will utilize the EXPORT Operator only.
The following (64-bit versions) are needed:
- Shared ICU Libraries for Teradata (TDICU)
- Teradata GSS Client (TERAGSS)
- ODBC Driver for Teradata
- Teradata CLIv2 (Cliv2)
- Teradata Parallel Transporter Base (TPT Base)
Installing on Windows
You must install Teradata Tools and Utilities (TTU). In the following example, version 16.20.17.00 is selected and all the available modules are installed.
- Download TTU from Teradata Official Website
- Extract the downloaded package.
-
Double-click the TTU_Base.exe to install. Ensure you install all the available modules.
- If you see an error that says the ODBC driver install failed, click continue. This error is expected. The Installation may last for 7-8 minutes.
Installing on Linux
- Download TTU package for Linux from Teradata Download Sites
-
Install it by executing the following command:
sudo ./setup.sh
- Go to /[MicroStrategy I-Server installation path]/env/ODBC.sh.
- Search for the TPT API configuration for Teradata.
-
Locate TPT_LIBRARY_PATH and modify the value according to your TTU installation path.
TPT_LIBRARY_PATH='<TPT_LIBRARY_PATH>'
- Restart your MicroStrategy service.
You must have a functional ODBC or JDBC driver to use TPT API because the driver is used to access catalogs, tables, and preview data during the import process. The API is only used to publish a cube.
MicroStrategy supports the TPT configuration at both the Database Instance level and the report level.
Configuring TPT in MicroStrategy Developer
Although the following VLDB settings are available at both the report level and database instance level, it is recommended to set the following VLDB settings at the report level for specific cube reports on a case-by-case bases.
- Create a new report or edit an existing report.
- Select Data > VLDB Properties.
- Go to Select/Insert > Data Retrieval Mode.
- Uncheck the Use default inherited value checkbox.
- Select Allow Native API.
- Go to Select/Insert > Data Retrieval Parameteres.
- Set the Data Retrieval Parameters to the following:
TD_TDP_ID must be supplied by Teradata database administrator.
TD_TDP_ID=<MICRO>;TD_MAX_SESSIONS=2;TD_MIN_SESSIONS=2;TD_MAX_INSTANCES=2;
- Click Save and Close.
- Run the report. The message “TPT API IS TURNED ON AND TD_MAX_INSTANCES=*” appears at the top of all the select passes in the SQL view of the report.
Configuring TPT in MicroStrategy Web
-
Create a database instance. Ensure you set the additional parameters:
- Select the Use Teradata Parallel Transporter checkbox. This sets the Data Retrieval Mode VLDB setting at the database connection level.
- Enter a value for TD_TDP_ID.
- Enter a value for TD_MAX_SESSIONS.
- Enter a value for TD_MIN_SESSIONS.
- Enter a value for TD_MAX_INSTANCES.
- Click Save.
- In MicroStrategy Web, click Create > Add External Data.
- Click Databases and select Build a Query.
- Select your newly created database instance.
- Enter the relevant namespace and select the tables.
-
Click Execute SQL. The calls for retrieving the tables are made through the ODBC.
- Click Finish. The publish dialog opens and the data is retrieved via TPT API.