Connecting to Tableau

You can use Tableau to connect to a SQream DB cluster. This tutorial is a guide that will show you how to connect to Tableau, as well as provide some guidelines and best practices for exploring data with Tableau and SQream DB.

Note

SQream does not currently support Tableau on MacOS. SQream DB supports both Tableau Desktop and Tableau Server on Windows and Linux distributions.

Installing Tableau Desktop

SQream DB has been tested with versions 9.2 and newer. If you do not already have Tableau Desktop installed, download and install Tabelau Desktop. https://www.tableau.com/products/trial

Tableau offers a time-limited trial version.

Installing the ODBC driver and customizations

If you’ve already installed the SQream DB ODBC driver, we recommend that you re-run the ODBC driver installer after installing Tableau, and select the Tableau customizations checkbox, as in the image below:

../../_images/odbc_windows_installer_tableau.png

This is necessary because by default, Tableau has a tendency to create temporary tables and run lots of discovery queries which could impact performance. The ODBC driver installer installs customizations for Tableau automatically.

If you want to perform this step manually, follow the instructions in the next section.

The TDC file

The TDC file (Tableau Datasource Customization) helps Tableau make full use of SQream DB’s features and capabilities.

Before you start, check which version of Tableau is used. The version needs to be placed in the TDC file.

  1. Download the TDC file to your computer odbc-sqream.tdc.

    Alternatively, copy the text below to a text editor.

    SQream DB ODBC TDC
    <?xml version='1.0' encoding='utf-8' ?>
    <connection-customization class='genericodbc' enabled='true' version='2019.3'>
        <vendor name='SQream DB Server' />
        <driver name='SqreamODBCDriver' />
        <customizations>
          <customization name='CAP_SKIP_CONNECT_VALIDATION' value='yes' />
          <customization name='CAP_CREATE_TEMP_TABLES' value='no' />
          <customization name='CAP_QUERY_GROUP_BY_ALIAS' value='no' />
          <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLFOREIGNKEYS_API' value='yes' />
          <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLPRIMARYKEYS_API' value='yes' />
          <customization name='CAP_QUERY_JOIN_ACROSS_SCHEMAS' value='yes' />
          <customization name='CAP_SELECT_INTO' value='no' />
          <customization name='CAP_QUERY_SUBQUERIES' value='no' />
          <customization name='CAP_QUERY_SUBQUERY_DATASOURCE_CONTEXT' value='yes' />
          <customization name='CAP_QUERY_SUBQUERY_QUERY_CONTEXT' value='yes' />
          <customization name='CAP_QUERY_SUBQUERIES_WITH_TOP' value='yes' />
          <customization name='CAP_QUERY_TOP_N' value='yes' />
          <customization name='CAP_QUERY_TOPSTYLE_TOP' value='yes' />
          <customization name='CAP_STORED_PROCEDURE_PREFER_TEMP_TABLE' value='no' />
          <customization name='CAP_SUPPRESS_DISPLAY_LIMITATIONS' value='yes' />
          <customization name='CAP_QUERY_GROUP_BY_DEGREE' value='yes' />
          <customization name='CAP_FAST_METADATA' value='yes' />
          <customization name='CAP_MULTIPLE_CONNECTIONS_FROM_SAME_IP' value='yes' />
        </customizations>
    </connection-customization>
    
  2. Change the highlighted line to match your major Tableau version. For example, if you’re on Tableau 2019.2.1, writing 2019.2 is enough.

    • For Tableau Desktop - save the TDC file to C:\Users\<user name>\Documents\My Tableau Repository\Datasources, where <user name> is the Windows username Tableau is installed in.
    • For Tableau Server - save the TDC file to C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources.

Configure the ODBC connection (DSN)

Create an ODBC DSN before connecting Tableau with SQream DB. See the section titled 3. Configuring the ODBC Driver DSN for information about creating an ODBC DSN in Windows.

Remember to test the connectivity before saving the DSN.

Connecting Tableau to SQream DB

  1. Start Tableau Desktop and select “Other Database (ODBC)”, by navigating Connect ‣ To a server ‣ More ‣ Other Database (ODBC)

    ../../_images/tableau_more_servers.png
  2. In the DSN selection window, select the DSN that you created earlier and select Connect ‣ OK.

    If prompted by Tableau, you may need to specify the user name and password again after clicking Connect.

    ../../_images/tableau_choose_dsn_and_connect.png

Setting up SQream DB tables as data sources

Once connected, you are taken to the data source page.

The left side of the screen contains a database and schema drop-down. Select the database name and schema name you wish to use (public is the default schema in SQream DB).

../../_images/tableau_data_sources.png

Drag tables you wish to use to the main area, marked as Drag tables here. This is also where you specify joins and data source filters.

When data source setup is completed, navigate to a new sheet to start analyzing data.

Tip

  • Read more about configuring data sources, joining, filtering, and more on Tableau’s Set Up Data Sources tutorials.
  • Rename the connection with a descriptive name for other users to understand. Alternatively, Tableau will generate a default name based on the DSN and tables.

Tableau best practices

Cut out what you don’t need

  • Bring only the data sources you need into Tableau. As a best practice, do not bring in tables that you don’t intend to explore.
  • Add filters before exploring. Every change you make while exploring data will query SQream DB, sometimes several times. Add filters to the datasource before exploring, so that the queries sent to SQream DB run faster.

Let Tableau create the queries

Create pre-optimized views (see CREATE VIEW) and point the datasource at these views.

some cases, using views or custom SQL as a datasoruce can actually degrade performance.

We recommend testing performance of custom SQL and views, and compare with Tableau’s generated SQL.

Create a separate service for Tableau

SQream recommends that Tableau get a separate service with the DWLM. This will reduce the impact of Tableau on other applications and processes, such as ETL. This works in conjunction with the load balancer to ensure good performance.

Troubleshoot workbook performance before deploying to Tableau Server

Tableau has a built in performance recorder that shows how time is being spent. If you’re seeing slow performance, this could be the result of a misconfiguration such as setting concurrency too low.

Use the Tableau Performance Recorder to view the performance of the queries that Tableau runs. Using this information, you can identify queries that can be optimized with the use of views.