Connect Tableau to a SAP HANA database

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

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:30015For more information about SAP HANA support for failover, see Configuring Clients for Failoveron 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.

SAP HANA data source example

Here is an example of an SAP HANA data source using Tableau Desktop on a Windows computer.

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.

Pass an option to the JVM on a Mac

To connect Tableau Desktop on a Mac to SAP HANA, you must have Java installed on your Mac. For more information, see SAP HANA on the Driver Download page.

If you’re familiar with how to pass a system property to the Java virtual machine (JVM), and you are using an Oracle JVM, you can use the environment variable _JAVA_OPTIONS to override the default options.

The _JAVA_OPTIONS variable takes a string, which is equivalent to command-line JVM arguments. The following example sets the maximum heap size to 256 MB, and specifies the path to the Kerberos configuration file:

export _JAVA_OPTIONS="-Xmx256m"

Tableau starts the JVM with the following default options:

  • -Xmx512mSets the maximum heap size to 512 MB
  • the JVM to delegate Kerberos authentication to the operating system library

If the default options don’t work, you can use one of the following options to configure Kerberos authentication:

Customize JDBC connections on a Mac

On a Mac, 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.

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, 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 ServerPublish 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.

@repost from Tableau blog

Leave a Reply

Your email address will not be published.