Advanced Teradata Options

When connecting to Teradata databases you can optionally set up query bands and initial sql. These advanced options are used to increase performance and take advantage of the built in security rules of the database.

Query Bands

When connecting to a Teradata database, you can optionally define query band statements that run during connection. Query banding allows you to pass parameters into the Teradata environment. Use these to set up a workbook to filter the data based on security rules that exist in the database. For example, you can pass in the Tableau Server username for the current user so when the view is loaded it only shows the data specific to that user. Query bands can also be used to improve performance. When connecting to Teradata, you can define a map between the name of the attributes passed into the query band and the corresponding values from Tableau.
To set up query banding:
  1. In the Teradata Connection dialog box, click the Advanced button at the bottom.
  2. In the subsequent dialog box, specify name/value pairs in the top text box labeled Query Banding. You can use the Insert drop-down menu to add Tableau values. The Tableau Values are described in the table below.
    Value Description Example
    <TableauMode> The mode Tableau is operating in when generating queries. This value will either be “Connect” when retrieving metadata or “Analytical” when retrieving actual data. Connect or Analytical
    <LoginUser> The username of the person logged into the database. jsmith
    <ServerUser> The logged in Server user. Does not include domain name. jsmith
    <ServerUserFull> The logged in server user including the domain name (if the server is using Active Directory). domain.lan\jsmith
    <ProxyUser> Used when setting up impersonation on the server. Provides the username of the current server user. jsmith
    <ProxyUserFull> Used when setting up impersonation on the server. Provides the username and domain name of the current server user. domain.lan\jsmith
    <TableauApp> The name of the Tableau application. Tableau Desktop Professional or Tableau Server
    <TableauVersion> The version of the Tableau application 6100.11.0428.0300
    <WorkbookName> The name of the workbook. Financial-Analysis
An example query band statement is shown below. this example passes the username for the current server user.
ApplicationName=<TableauApp>Version=<TableauVersion>ProxyUser=<ProxyUser>TableauMode=<TableauMode>
Tableau checks the statement for errors as you type. When it is valid a green checkmark displays at the bottom of the text box.

Initial SQL

When connection to Teradata databases, you can optionally specify a SQL command that will be run once upon connection. This query is useful to set up temporary tables that will be used during the session and can be used to set up a custom data environment. This initial SQL is different than a Custom SQL connection because it is only run when you first open the workbook rather than affecting every query to retrieve rows from the database.
To set up initial SQL:
  1. In the Teradata Connection dialog box, click the Advanced button at the bottom.
  2. In the subsequent dialog box, type the SQL command into the lower text box labeled Initial SQL.

Note: Tableau does not examine the statement for errors. This SQL statement is simply sent to the database upon connection.
Your software license may restrict you from using initial SQL with your Teradata connection. If you are publishing to Tableau Server, the server must be configured to allow initial SQL statements. By default, the server software is configured to allow these statements to run when the workbook is loaded in a web browser. Administrators can disable the functionality on the Data Connections tab of the Tableau Server Configuration utility. If the server does not allow initial SQL statements, the workbook will still open, but the initial SQL commands will not be sent.

5 comments:

  1. I was looking for the Teradata Online Training courses and your website really help me in finding my needs. This site contains all the stuff which i was looking . Thanks for this great work and i hope this will help a lots of users to achieve their goals.

    ReplyDelete
  2. Thanks for giving Good Example It are very useful to us.
    Fantastic article, Viral. Very well written, clear and concise. One of the best links explaining one to many and hierarchy Teradata. Thanks a lot.It is useful to me and my training Teradataonline Training

    ReplyDelete
  3. Thanks for sharing these information. It’s a very nice topic. We are providing online training classesteradataonlinetraining

    ReplyDelete
  4. Thank you for the useful information.
    It is very useful to me and who are searching for Teradata online training

    ReplyDelete
  5. Hi friend iam providing theTableau training in hyderabad various cretification training.

    ReplyDelete