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.
SQream does not currently support Tableau on MacOS. SQream DB supports both Tableau Desktop and Tableau Server on Windows and Linux distributions.
In this topic:
- Installing Tableau Desktop
- Installing the ODBC driver and customizations
- Configure the ODBC connection (DSN)
- Connecting Tableau to SQream DB
- Setting up SQream DB tables as data sources
- Tableau best practices
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.
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:
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 (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.
Download the TDC file to your computer
Alternatively, copy the text below to a text editor.
<?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>
Change the highlighted line to match your major Tableau version. For example, if you’re on Tableau
- 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
- For Tableau Desktop - save the TDC file to
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.
Start Tableau Desktop and select “Other Database (ODBC)”, by navigating
In the DSN selection window, select the DSN that you created earlier and select.
If prompted by Tableau, you may need to specify the user name and password again after clicking Connect.
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).
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.
- 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.
- 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.
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.
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.
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.