SAP HANA

This article describes how to connect Tableau to an SAP HANA database and set up the data source.

Note: Beginning with Tableau version 2020.2, this connector for Linux and Mac uses a JDBC driver. For Windows, this connector still uses an ODBC driver.

Before you begin

Before you begin, gather this connection information:

  • Are you making a single-node or multi-node connection?

    • SingleNode: Name of server that hosts the database you want to connect to, and the port number if you’re using a non-default port

    • MultiNode: Names of servers and port numbers that host the databases you want to connect to

  • Authentication method:

    • Windows: Windows Authentication or user name and password

    • Mac: Kerberos or user name and password

  • Are you connecting to an SSL server?

  • (Optional) Initial SQL statement to run every time Tableau connects

Driver required

This connector requires a driver to talk to the database. You might already have the required driver installed on your computer. If the driver is not installed on your computer, Tableau displays a message in the connection dialog box with a link to the Driver Download page where you can find driver links and installation instructions.

Make the connection and set up the data source

  1. Start Tableau and under Connect, select SAP HANA. For a complete list of data connections, select More under To a Server. Then do the following:

    1. Select the type of connection:

      • SingleNode - Enter the name of the server that hosts the database you want to connect to, and the port number if you're using a non-default port.

      • MultiNode - Enter the host name and port number of each server, separated by a comma. For example:

        host1:30015,host2:30015,host3:30015

        For more information about SAP HANA support for failover, see Configuring Clients for Failover on the SAP Help Portal.

    2. Specify how you want to sign in to the server:

      On Windows:

      • Select Use Windows Authentication if your environment supports single sign-on (SSO). For more information about single sign-on and environment requirements, see Support for SAP HANA single sign-on (SSO).

      • Or, select Use a specific username and password. If the server is password protected and you are not in a Kerberos environment, you must enter the user name and password.

      On Mac: Select Kerberos or select Use a specific username and password.

      Select the Require SSL option when connecting to an SSL server.

    3. (Optional) Select Initial SQL to specify a SQL command to run at the beginning of every connection, such as when you open the workbook, refresh an extract, sign in to Tableau Server, or publish to Tableau Server. For more information, see Run Initial SQL.

    4. Select Sign In.

      If Tableau can't make the connection, verify that your credentials are correct. If you still can't connect, your computer is having trouble locating the server. Contact your network administrator or database administrator.

  2. On the data source page, do the following:

    1. (Optional) Select the default data source name at the top of the page, and then enter a unique data source name for use in Tableau. For example, use a data source naming convention that helps other users of the data source figure out which data source to connect to.

    2. From the Schema drop-down list, select the search icon or enter the schema name in the text box and select the search icon, and then select the schema.

    3. In the Table text box, select the search icon or enter the table name and select the search icon, and then select the table.

    4. Drag the table to the canvas, and then select the sheet tab to start your analysis.

      By default, column labels are displayed instead of column names.

      Use custom SQL to connect to a specific query rather than the entire data source. For more information, see Connect to a Custom SQL Query.

      Note: When you create a self-join with an analytic view, if one of the tables contains variables, make sure that the table with variables is on the left. Otherwise, the join may not return the expected results.

Sign in on a Mac

If you use Tableau Desktop on a Mac, when you enter the server name to connect, use a fully qualified domain name, such as mydb.test.ourdomain.lan, instead of a relative domain name, such as mydb or mydb.test.

Alternatively, you can add the domain to the list of Search Domains for the Mac computer so that when you connect, you need to provide only the server name. To update the list of Search Domains, go to System Preferences > Network > Advanced, and then open the DNS tab.

Convert an SAP HANA level hierarchy to a user hierarchy

In all Tableau versions, when you create an SAP HANA data source or workbook, you can create your own hierarchy in Tableau that’s solely for use in Tableau.

Starting in Tableau 2019.4, in addition to support for creating your own hierarchy for use in Tableau, Tableau now supports level hierarchies created in SAP HANA. This means that when you connect Tableau to an SAP HANA calculation view with a level hierarchy, the structure of the SAP HANA level hierarchy appears in the Data pane.

If you want to modify the SAP HANA level hierarchy in Tableau, you need to convert the SAP HANA level hierarchy to a user hierarchy. After you convert an SAP HANA level hierarchy to a user hierarchy, you can't reverse it. If you decide you want to use the SAP HANA level hierarchy instead of the user hierarchy, you need to start over.

To convert to a user hierarchy, follow these steps:

  1. To the left of the sheet, in the Data pane, select the hierarchy, and then select the drop-down menu.
  2. In the drop-down menu, select Convert to User Hierarchy.

Customize JDBC connections

On Mac and Linux, SAP HANA uses a JDBC connection, which you can customize using a properties file. For more information, see Customize JDBC Connections Using a Properties File in Tableau Community.

Install trusted SSL certificates on a Mac

If you want to use SSL/TLS to encrypt communications between Tableau Desktop and your database, you may need to add certificates to the Java certificate trust store. This is required if your database uses self-signed certificates.

  1. Start the Terminal application, and navigate to your Java Home directory, which is usually located at /Library/Internet Plug-Ins/JavaAppletPlugin.plugin/Contents/Home, unless you have installed the JDK or set the JAVA_HOME environment variable.

  2. Run the following command. (You might need to use the sudo Terminal command, which is used by administrators to execute commands as a different user, such as root).

    bin/keytool -importcert -keystore cacerts -alias <certificate name>  -file <path to certificate file>

    You can now connect Tableau Desktop to your database using SSL/TLS.

For more information on installing trusted certificates, consult the documentation for your Java Runtime Environment.

Select variables and input parameters

If the table you use includes required or optional variables or parameters, the Variables and Input Parameters dialog box opens.

  • Required variables and parameters display their current value or *Required.

  • Optional variables and parameters display their current value or are blank.

  • Prompt for the variable when the workbook is opened by selecting the check box under Prompt.

Select a variable or parameter and type or select a value for it. Repeat for all required values and any optional values that you want to include, and then select OK.

Note: You can use SQL query-based SAP HANA prompts.

Support for SAP HANA single sign-on (SSO)

When SAP HANA is configured to support single sign-on (SSO), after you sign in to the SAP HANA server, you can access data, and publish data sources and workbooks to Tableau Server, without having to re-enter your user name and password. And, you can publish a data source or workbook so that other users with SSO can access the published data sources and workbooks without having to enter their user names and passwords.

To use SSO, you need to publish data sources and workbooks to Tableau Server with Viewer credentials authentication. To enable Viewer credentials authentication, you must connect to SAP HANA using Windows Authentication on a Windows computer or Kerberos on a Mac. For more information, see Publish to the Server.

Important: Your environment must be correctly configured to support SSO for SAP HANA:

  • Tableau Desktop requires SAP HANA driver version 1.00.85 and later.

  • Tableau Server must be configured to support SSO for SAP HANA. For information, see Configure SAP HANA for Single Sign-On in the Tableau Server Help.

Sign in to the server

When SSO is set up in your environment, to sign in to SAP HANA, simply enter the server name, select Use Windows Authentication on a Windows computer or Kerberos on a Mac, and then click Sign In.

Publish to the Server

In an SSO environment, users don’t have to enter their credentials to access a data source or a workbook if you publish it to Tableau Server with the correct authentication mode.

To enable SSO when you publish a data source or workbook to Tableau Server, follow these steps:

  1. Select Server> Publish Data Source or Server > Publish Workbook.
  2. Under Data Sources, click Edit.

  3. In the Manage Data Sources dialog box, do the following:

    1. Select the Publish Type: Embedded in workbook or Published separately.

    2. Select Viewer credentials for Authentication.

  4. Click Publish.

How to refresh HANA extracts when you use SSO

When you use single sign-on (SSO) to connect to SAP HANA, because of authentication restrictions, you can't schedule an extract refresh when you publish the data source or workbook to a server. You can, however, use the Tableau Data Extract Utility to automate the extract refresh task.

For example, the following command refreshes an SAP HANA extract named "mydatasource" that has been published to Tableau Server. This command specifies the following:

  • The name of your Tableau Server

  • The name of the data source to refresh

C:\Program Files\Tableau\Tableau 10.2\bin>tableau refreshextract --server https://mytableauserver --datasource mydatasource

For more information about the utility, see Tableau Data Extract Command-Line Utility.

Performance tips

You can improve performance by enabling one or both of these capabilities:

  • Query parameter binding
  • Shared metadata caching

The following sections describe how to use these features.

Use bind variables

Tableau can use bind variables for your SAP HANA queries. This improves performance:

  • For queries — HANA can compile a query once and execute it many times​.
  • For multi-node HANA deployments — Parameterized queries are optimally routed to the proper node.

To enable this feature, your Tableau Server Administrator can add the CAP_ODBC_QUERY_USE_PREPARE_PARAMETER_MARKER capability in a TDC file.

Note: This feature requires Tableau 2018.3 or later.

Use shared metadata cache

Tableau relies heavily on SAP HANA metadata, which in turn may be complex, and therefore difficult (slow) to retrieve.

Tableau supports a shared metadata cache. In many cases, and especially with single sign-on (SSO), it maybe useful to share a metadata cache between database users. This can improve performance.

When Tableau shares the metadata cache, these things are true:

  • Table and/or column metadata is cached. (The values that are cached are based on the authorization of the first user to access the metadata.)
  • Metadata cache can be shared across identical connections in Tableau.
  • Metadata cache is distributed in a high-availability (HA) cluster.

One possible risk: If you have developed a process where different column names display depending upon the User/Group, then a user might see too few or too many columns.

To enable this feature, your Tableau Server Administrator can add these capabilities using the following TSM commands:

  • tsm configuration set -k enable_metadatacache -v shared --force-keys
  • tsm pending-changes apply

Note: This feature requires Tableau Server 2019.3 or later. For more information about TSM files, see Tableau Services Manager Overview.

Use column metadata collection

Tableau supports column metadata collection, resulting in great load-time improvements for some views.

When Tableau reads the column metadata for a table or view, it uses a prepared query on the table by default. This is usually the most accurate option, but may not be the most efficient.

Optionally, you can use column metadata collection. That is, you can disable prepared query metadata using a TDC file. When you do this, Tableau uses the ODBC API, SQLColumns. While this method is faster, it may not be as accurate. That is why you should use this option cautiously and only when it seems like a good approach for your data.

Using column metadata via SQLColumns is enabled using these capabilities:

  • CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY
  • CAP_ODBC_METADATA_SUPPRESS_PREPARED_QUERY

Note: To apply these capabilities to temporary tables, you must install Tableau Server 2019.3.6 or later. Then you can add those capabilities to the TDC file.

TDC example for SAP HANA

Your TDC file should look like the following example. In most cases, you will need no other capabilities. In particular, be sure to avoid capabilities such as CAP_ODBC_METADATA_SUPPRESS_SQLPRIMARYKEYS_API and CAP_ODBC_METADATA_SUPPRESS_SQLFORIEGNKEYS_API. These may speed up the initial connection time, but will cause us to generate less efficient queries later.

<?xml version='1.0' encoding='utf-8' ?>
<connection-customization class='saphana' enabled='true' version='9.3'>
<vendor name='saphana' />
<driver name='saphana' />
<customizations>
<customization name='CAP_ODBC_QUERY_USE_PREPARE_PARAMETER_MARKER' value='yes' />
</customizations>
</connection-customization>

For more information about using TDC files, see "Create a TDC file" in the Customize and Tune ODBC Connections topic. Also see the Knowledge Base article called Using a .tdc File with Tableau Server.

Important: Be sure enabled=’true’ is part of your connection-customization line.

See also

Thanks for your feedback! There was an error submitting your feedback. Please try again.