SAP HANA

Applies to: Tableau Desktop, Tableau Prep

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

Note: This connector uses JDBC drivers for Mac, Linux and Windows. The JDBC driver for Windows is new in Tableau version 2021.1. JDBC driver version 2.3.4 is the recommended version.

Before you begin

Before you begin, gather this connection information:

  • Are you making a single-node or multi-node connection?
    • SingleNode: Database server name and port number.
    • MultiNode: Database server name, host name and port numbers.
  • Authentication:
    • Windows Authentication (Windows only)
    • Kerberos (Mac only)
    • Username and password
    • OAuth 2.0/OIDC. For more information on how to configure this, see External OAuth for SAP HANA.
  • 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. 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(Link opens in a new window) 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.

    1. Select the type of connection:

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

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

        host1:30015,host2:30015,host3:30015

    2. Select how to sign into the server:

      Windows:

      • Select Use Windows Authentication, if your environment supports single sign-on (SSO).
      • Select Use a specific username and password if the server is password protected and you aren't in a Kerberos environment – you must enter the username and password.
      • Select Sign in using OAuth to use OAuth 2.0/OIDC to federate identity from an external IdP to HANA, and select the OAuth Provider that you configured. For more information on how to configure this, see External OAuth for SAP HANA.

      Mac:

      • Select Kerberos
      • Select Use a specific username and password.
      • Select Sign in using OAuth to use OAuth 2.0/OIDC to federate identity from an external IdP to HANA, and select the OAuth Provider that you configured. For more information on how to configure this, see External OAuth for SAP HANA.

    3. Select the require SSL option when connecting to an SSL server.

    4. (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.

    5. 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 for more assistance.

  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.

      Tableau Desktop also supports connecting to SAP HANA table functions, which appear under Stored Procedures in the left pane of the Data Source page.

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

      The default display is column labels.

      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, and one of the tables has 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

When you create an SAP HANA data source or workbook, you can create your own hierarchy for use in Tableau. Tableau supports level hierarchies created in SAP HANA starting in version 2019.4. 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 must convert the SAP HANA level hierarchy to a user hierarchy.

Warning: 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 must start again.

To convert to a user hierarchy, complete the following steps:

  1. In the Data pane, select the hierarchy.
  2. In the drop-down menu, select Convert to User Hierarchy.

Customise JDBC connections

On Mac and Linux, SAP HANA uses a JDBC connection. You can customise the JDBC connection using a .properties file. For more information, see Customise JDBC Connections Using a .properties File(Link opens in a new window) 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 must add certificates to the Java certificate trust store. This is required if your database uses self-signed certificates. To connect Tableau Desktop to your database using SSL/TLS, complete the following steps:

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

  2. Run the following command:

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

Note: Beginning with Tableau version 2020.2, you can import a custom certificate into the "System" keychain. For details, see One-Way SSL for JDBC Connections.

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 ticking the 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)

You can access data, publish data sources and publish workbooks to Tableau Server, without having to re-enter your username and password by configuring SAP HANA to support single sign-on (SSO). To use SSO, you must 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.

Sign in to the server

You can sign into the SAP HANA server by entering the server name. Or you can select Use Windows Authentication on a Windows computer or Kerberos on a Mac. To finish, select Sign In.

Publish to the Server

You don’t have to enter credentials to access a data source or a workbook if you publish it to Tableau Server with SSO enabled. To enable SSO when you publish a data source or workbook to Tableau Server, complete the following steps:

  1. Select Server, Publish Data Source or Server, Publish Workbook.

  2. Under Data Sources, choose Edit.

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

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

    • Select Viewer credentials for Authentication.

  4. Choose Publish.

How to refresh HANA extracts when you use SSO

You can use the Tableau Data Extract Utility to automate the extract refresh task. For example, the following command refreshes a published SAP HANA extract named "mydatasource". 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 query parameter binding.

Use bind variables

To improve performance, Tableau can use bind variables for your SAP HANA queries.

  • Queries: HANA can compile a query once and execute it many times​.
  • Multi-node HANA deployments: Parametrised 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.
You can use CAP_ODBC_QUERY_USE_PREPARE_PARAMETER_MARKER with JDBC and ODBC.

Use column metadata collection

Tableau supports column metadata collection, which may improve the load time for some views.

When Tableau reads the column metadata for a table or view, it uses a prepared query on the table by default.

Note: Column metadata collection is usually the most accurate option, but it can take longer to process.

You can disable prepared query metadata using a TDC file. When you use column metadata collection, Tableau uses the ODBC API, SQLColumns. While this method is faster, it may not be as accurate. Use this option cautiously and only when it seems like a good approach for your data. Use the following capabilities to enable column metadata via SQL Columns:

  • 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.

TDC example for SAP HANA

Your TDC file should look like the following example.

<?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 Customise and Tune ODBC Connections topic. Also see the Knowledge Base article called Using a TDC File with Tableau Server.

Important: Make sure you include enabled=’true’ in your connection-customisation line.

See also

Back to top
Thanks for your feedback!Your feedback has been successfully submitted. Thank you!