Connecting Teradata with Qlikview

Analytics
Enthusiast

Connecting Teradata with Qlikview

Access to the QlikView Teradata (TPT) Connector is provided by QlikView. QlikView applications connect to data sources through Edit Script.

Edit Script enables you to build a data model with ETL (Extract, Transform & Load) processes using the QlikView data load script language. The script language enables you to perform complex transformations and create a scalable data model.

Do the following:

  1. Open a new or existing QlikView app and select Edit Script... from the File menu.

  2. Select the Teradata (QvTptConnector.exe) Connector from the drop-down list next to Connect... on the Data tab in the lower part of the Edit Script dialog.

  3. Select Connect... to open the TPT Connection dialog.

  4. Enter the server name or its IP address and the name of the database to access along with the user name and password credentials.

    The user name and password are used to establish the connection in the FastExport and BTEQ scripts.

  5. Select the transport type.

    Named pipes brings the data directly into QlikView.

    File causes the data to be written to an intermediate file. QlikView then reads the data from the file rather than taking it directly through the named pipe.

    The TPT data can be large, so the file system must have enough space to hold the file. By default, the file is deleted once the data has been read into QlikView. If you want to keep the file, uncheck the Delete file after load option.

    Using the File option for loading data can improve performance in some situations. If you are not getting the performance you expect through the named pipe, you can try using the File option.

    If you use the File option, you can specify the directory location in which to write the file. If the specified directory does not exist, the file is written to the user’s temp directory. The user’s temp directory is the default location and is also used when no directory is specified.

  6. Enter the Execution Properties:

    Log table name: The name for the log table the FastExport script will write. By default, the log table is written to the database from which the data is being read. The full name of the log table is generated using the process id of the FastExport script and an instance number. The base name can be a maximum of 20 characters, it must start with a letter, and it can contain only letters, numbers, and underscores.

     

    To run the Teradata Connector, the user must have permissions to create tables within the Teradata database. This permission is required to create a log table in the database. The log table is used when Teradata exports the data.

    You can also write the log table to another database to which you have to write permissions. To create the log file in another database, you must attach the name of the database to the name of the log file. For example, “myDB.logfile”. The FastExport script will then write the log file to “myDB” rather than the default database.

    Session count: The number of sessions to use for the FastExport script.

    Max VARCHAR Length: The maximum number of characters for fields with the VARCHAR data type. Fields that exceed the maximum are trimmed. The actual size used is two less than the number specified because two characters are used for delimiting fields. The total size of all columns must be less than or equal to 64,000. If one of the selected columns for the table contains the LONGVARCHAR data type (which is VARCHAR(64000)), the maximum size will be reached, and no other columns could be selected for loading. The MaxVarCharLength value can be used to trim all VarChar and LongVarChar columns to the specified value. For example, if you know data contained in LongVarCharfields does not exceed 1,000 characters, you could set MaxVarCharLength = 1000, which would allow the user to select multiple LongVarChar columns. A clear error message is sent to QlikView when the maximum column size is reached.

    Record cache count: The number of records the connector caches in memory while waiting to deliver to QlikView. After this limit is reached, the read is blocked until there is room to queue the next record.

    Read buffer size: The size (in bytes) of the buffer used to read from FastExport.

     

  7. You can click Test Connection to verify that a connection can be made with the properties entered.

    The connection is tested before it is created. Clicking Test Connection enables you to test the connection before you attempt to create it.

     

  8. Click OK to create the connection string that used when sending the load statement to the Teradata database.

Tags (2)