Connect to Data

Note: Starting in version 2020.4.1, you can now create and edit flows in Tableau Server and Tableau Online. The content in this topic applies to all platforms, unless specifically noted. For more information about authoring flows on the web, see Tableau Prep on the Web.

Tableau Prep supports connections to popular types of data as well as Tableau data extracts (.hyper) and published data sources.

If you have the Data Management Add-on(Link opens in a new window) with Tableau Catalogue enabled, you can also search for and connect to databases, files and tables stored on Tableau Server or Tableau Online. For more information about Tableau Catalogue, see "About Tableau Catalogue" in the Tableau Server(Link opens in a new window) or Tableau Online(Link opens in a new window) Help.

New data connectors are added with each new version of Tableau Prep. However, depending on which version you are using or whether you are creating and editing flows on the web, some connectors might not be available. To see the most current list of available connectors, open Tableau Prep Builder or start a flow on the web and click the Add connection button to see if a connector for your data is listed in the left pane under Connect.

Note: If you open a flow in a version where the connector isn't supported, the flow may open but might have errors or won't run unless the data connections are removed.

While most connectors work the same across all of our platforms, some have different requirements when using them with Tableau Prep and are described in this topic. For all other connectors, see Supported Connectors(Link opens in a new window) in the Tableau Desktop help.

Start or open a flow

Start a new flow by connecting to your data, just like in Tableau Desktop. You can also open an existing flow and pick up where you left off.

Note: Some connectors might require you to download and install a driver before you can connect to your data. See the Driver Download(Link opens in a new window) page on the Tableau website to get driver download links and installation instructions.

In Tableau Prep Builder, you can see and access your most recent flows right on the Start page, so it's easy to find your work in progress. When working with flows on the web, all your flows are conveniently listed on the Explore page under the All Flows menu.

After you connect to your data, use the different options in the Input step to identify the data that you want to work with in your flow. Then add a cleaning step or other step type to examine, clean and shape your data.

When your flows include many different data source connectors, Tableau Prep helps you easily see which connectors and tables are associated with your Input steps. When you click on the Input step, the associated connector and data table is highlighted in the Connections pane. This option was added in Tableau Prep Builder version 2020.1.1 and is also supported when editing flows on the web.

Start a new flow

  1. Open Tableau Prep Builder and click the Add connection button.

    In web authoring, from the Home page, click Create > Flow or from the Explore page, click New > Flow. Then click Connect to Data.

  2. From the list of connectors, select the file type or server that hosts your data. If prompted, enter the information needed to sign in and access your data.

    Note: In web authoring, the list of file connectors may differ.

  3. From the Connections pane, do one of the following:

    • If you connected to a file, double-click or drag a table to the Flow pane to start your flow. For single tables, Tableau Prep automatically creates an Input step for you in the Flow pane when you add data to your flow.

      Note: In web authoring, for file connections, you can only download the files one at a time. Direct connections to a file network share isn't currently supported.

    • If you connected to a database, select a database or schema, and then double-click or drag a table to the Flow pane to start your flow.

      Note: In Tableau Prep Builder, you can union multiple files or database tables from a single data source in the input step using a wildcard search. In web authoring you can't create or edit input unions but they are supported in flows published from Tableau Prep Builder. For more information, see Union files and database tables in the Input step(Link opens in a new window).

Open an existing flow

Tableau Prep Builder

On the Start page do one of the following:

  • Under Recent Flows, select a flow.

  • Click Open a Flow to navigate to your flow file and open it.

You can also click File > New or File > Open to open a new workspace. You can open multiple Tableau Prep Builder workspaces using these options if you want to work on multiple flows at the same time. In Tableau Prep Builder version 2019.3.1 and earlier, if you select File > Open, Tableau Prep Builder replaces your current open flow with the new flow you select.

Tableau Prep on the web

  1. To open and edit an existing flow, on the Explore page select All Flows from the top drop-down menu and select your flow from the list.

  2. On the Flow Overview page, click Edit to edit your flow.

    Your flow will open in a new tab. As soon as you start making changes, Tableau will automatically save your changes every few seconds and save your modified flow as a draft. Drafts are only visible to you and your administrator.

    When you're finished, you can close your flow and continue making changes later or publish your flow to apply your changes, creating a new version of the flow.

    Like other tools, flow publishing uses a first-in method. If another users modifies and republishes the flow before you, their changes are committed first. But you can track and revert to a previous version using the Revision History page. For more information, see Work with Content Revisions(Link opens in a new window) in the Tableau Desktop help.

Connect to published data sources

If you publish data sources from Tableau Prep Builder (version 2019.3.1 and later) to Tableau Server or Tableau Online from Tableau Desktop or as output from your flow, you can connect to these published data sources and use them as an input data source for your flow. You can also connect to published data sources when creating or editing flows on the web.

Note: When you publish a flow that includes a published data source as an input, the publisher is assigned as the default flow owner. When the flow runs, it uses the flow owner for the Run As account. For more information about the Run As account, see Run As Service Account(Link opens in a new window). Only the Site or Server Administrator can change the flow owner in Tableau Server or Tableau Online.

When using this feature the following requirements apply:

  • Connecting to published data sources that are available on Tableau Server version 2019.3 and later are fully supported. All features may not be available if connecting to published data sources on earlier version of Tableau Server.
  • You can connect to published data sources when creating or editing flow on the web starting with version 2020.4.1.
  • You must be assigned a role of Explorer or higher in the Tableau Server site you are signed into to connect to published data sources. Only Creators can create or edit flows on the web. For more information about site roles, see Set User's Site Roles(Link opens in a new window) in the Tableau Server help.
  • Credentials must be embedded to connect to the published data source. If credentials aren't embedded for the data source, update the data source to include the embedded credentials.
  • Published data sources that include multi-dimensional (cube) data or includes user filters are not currently supported.
  • Multi-server connections aren't supported. You must use the same server or site connection to do the following:
    • Connect to the published data source.
    • Publish flow output to Tableau Server or Tableau Online.
    • Schedule the flow to run on Tableau Server or Tableau Online.

To connect to a published data source, follow the instructions for your Tableau Prep version:

Tableau Prep Builder version 2020.2.1 and earlier
  1. Open Tableau Prep Builder and click the Add connection button.

  2. From the list of connectors, select Tableau Server.

  3. Sign in to connect to your server or site.

  4. Select your data source or use the search option to find your data source and drag it to the flow pane to start your flow. The Tableau Server tab in the Input pane shows details about the published data source.

Tableau Prep Builder version 2020.2.2 and later and on the web

You can connect to published data sources and more that are stored on Tableau Server or Tableau Online directly from the Connect pane. If you have the Data Management Add-on(Link opens in a new window) with Tableau Catalogue enabled you can also search for and connect to databases and tables and view or filter by meta data about the data sources, such as descriptions, data quality warnings and certifications.

For more information about Tableau Catalogue, see "About Tableau Catalogue" in the Tableau Server(Link opens in a new window) or Tableau Online(Link opens in a new window) Help.

  1. Open Tableau Prep Builder and click the Add connection button.

    In web authoring, from the Home page, click Create > Flow or from the Explore page, click New > Flow. Then click Connect to Data.

  2. On the Connect pane, under Search for Data, select Tableau Server.

  3. Sign in to connect to your server or site.

    In web authoring, the Search for data dialog opens for the sever you are signed into.

  4. In the Search for Data dialog, search from a list of available published data sources. Use the filter option to filter by connection type and certified data sources.

  5. Select the data source you want to use, then click Connect.

    If you don't have permission to connect to a data source, the row and the Connect button is greyed out.

    Note: The Content Type drop-down isn't shown if you don't have the Data Management Add-on with Tableau Catalogue enabled. Only published data sources are shown in the list.

  6. The data source is added to the Flow pane. In the Connections pane, you can select additional data sources or use the search option to find your data source and drag it to the flow pane to build your flow. The Tableau Server tab in the Input pane shows details about the published data source.

  7. (Optional) If you have the Data Management Add-on with Tableau Catalogue enabled, use the Content Type drop-down to search for databases and tables.

    You can use the filter option in the top right corner to filter your results by connection type, data quality warnings and certifications.

Connect to cloud data sources

You can connect to cloud data sources in Tableau Prep just like Tableau Desktop, but if you plan to publish flows that connect to cloud data sources and run them on a schedule in your server, you'll need to configure your credentials in Tableau Server or Tableau Online.

You set up your credentials in the Settings tab in the My Account Settings page and connect to your cloud connector input using these same credentials.

Tableau Prep Builder

When publishing the flow, on the Publish dialog, click Edit to edit the connection, then in the Authentication drop-down, select Embed <your credentials>.

You can also add credentials right from the publish dialog (Tableau Prep Builder version 2020.1.1 when publishing your flow and then automatically embed them in your flow when you publish. For more information, see Publish a flow from Tableau Prep Builder.

If you don't have saved credentials set up, and you select Prompt user in the Authentication drop-down, after you publish the flow, you must edit the connection and enter your credentials in the Connections tab in Tableau Server or Tableau Online or the flow will fail when run.

Tableau Prep on the web

In web authoring, you can embed credentials from the top menu under File > Connection Credentials. For more information, see Publish a flow in web authoring.

In Tableau Prep Builder version 2019.4.1, the following cloud connectors were added and are also available when creating or editing flows on the web:

  • Box
  • DropBox
  • Google Drive
  • OneDrive

For more information about how to connect to your data using these connectors, see the connector-specific(Link opens in a new window) help topic in the Tableau Desktop help.

Connect to Salesforce data

Tableau Prep Builder (version 2020.2.1 and later) and on the web supports connecting to data using the Salesforce connector, just like Tableau Desktop, but with a few differences.

  • Tableau Prep Builder supports any join type you want to do.
  • Custom SOQL isn't currently supported.
  • Using a standard connection to create your own custom connection isn't currently supported.
  • You can't change the default data source name to be something unique or custom.
  • If you plan to publish flows on Tableau Server and want to use saved credentials, the server administrator will need to configure Tableau Server with an OAuth client ID and secret on the connector. For more information, see Change Salesforce.com OAuth to Saved Credentials(Link opens in a new window) in the Tableau Server help.

Tableau Prep imports the data by creating an extract. Only extracts are currently supported for Salesforce. The initial extract may take some time depending on the amount of data that is included. You will see a timer in the Input step while the data loads.

For general information about using the Salesforce connector, see Salesforce(Link opens in a new window) in the Tableau Desktop and Web Authoring help.

Connect to Google BigQuery

Tableau Prep Builder (version 2019.3.1 and later) and on the web, supports connecting to data using Google BigQuery just like Tableau Desktop.

You must configure OAuth credentials to enable Tableau Prep to communicate with Google BigQuery. If you plan to publish flows to Tableau Server or Tableau Online, OAuth connections must also be configured for those applications.

Note: Tableau Prep doesn't currently support using Google BigQuery customisation attributes.

Configure SSL to connect to Google BigQuery (MacOS only)

If you are using Tableau Prep Builder on Mac and you are using a proxy to connect to Big Query, you my need to modify the SSL configuration to connect to Google BigQuery

Note: No extra steps are required for Windows users.

To configure SSL for OAuth connections to Google BigQuery, complete the following steps:

  1. Export the SSL certificate for your proxy to a file, for example proxy.cer. You can find your certificate in Applications > Utilities > Keychain Access >System > Certificates (under Category).
  2. Locate the version of java that you are using to run Tableau Prep Builder. For example: /Applications/Tableau Prep Builder 2020.4.app/Plugins/jre/lib/security/cacerts

  3. Open the Terminal command prompt and run the following command for your Tableau Prep Builder version:

    Note: The keytool command must be run from the directory that contains the version of java that you are using to run Tableau Prep Builder. You may have to change directories before running this command. For example cd /Users/tableau_user/Desktop/SSL.cer -keystore Tableau Prep Builder 2020.1.1/Plugins/jre/bin. Then run the keytool command.

    keytool –import –trustcacerts –file /Users/tableau_user/Desktop/SSL.cer -keystore Tableau Prep Builder <version>/Plugins/jre/lib/security/cacerts -storepass changeit

    Example: keytool –import –trustcacerts –file /Users/tableau_user/Desktop/SSL.cer -keystore Tableau Prep Builder 2020.4.1/Plugins/jre/lib/security/cacerts -storepass changeit

If you get a FileNotFoundException (Access denied) when running the keytool command, try running the command with elevated permissions. For example: sudo keytool –import –trustcacerts –file /Users/tableau_user/Desktop/SSL.cer -keystore Tableau Prep Builder 2020.4.1/Plugins/jre/lib/security/cacerts -storepass changeit.

Set up and manage your Google BigQuery credentials

The credentials that you use to connect to Google BigQuery in your Input step must match the credentials that are set up in the Settings tab in the My Account Settings page for Google BigQuery in Tableau Server or Tableau Online.

If you select different credentials or no credentials in your authentication setting when publishing your flow, the flow will fail with an authentication error until you edit the connection for the flow in Tableau Server or Tableau Online.

To edit your credentials, do the following:

  1. In Tableau Server or Tableau Online, on the Connections tab, on the Google BigQuery connection, click More actions .
  2. Select Edit Connection.
  3. Select the saved credentials that are set up in the Settings tab in the My Account Settings page.

For more information about setting and managing your credentials, see the following topics:

Manage Your Account Settings(Link opens in a new window) in the Tableau Desktop and Web Authoring help.

Publish a flow from Tableau Prep Builder(Link opens in a new window) for information about setting authentication options when publishing a flow.

View and resolve errors for information about resolving connection errors in Tableau Server or Tableau Online.

Connect to SAP HANA

Tableau Prep Builder (version 2019.2.1 and later) and on the web, supports connecting to data using SAP HANA just like Tableau Desktop but with a few differences.

Connect to the database using the same procedure you would use in Tableau Desktop. For more information see SAP HANA(Link opens in a new window). After you connect and search for your table, drag the table to the canvas to begin building your flow.

Prompting for variables and parameters when opening a flow isn't supported in Tableau Prep. Instead, in the Input pane, click the Variables and Parameters tab and select the variables and operands you want to use, then select from a list of preset values or enter custom values to query your database and return the values you need.

Note: Starting in Tableau Prep Builder (version 2019.2.2) and on the web, you can use Initial SQL to query your connection and if you have multiple values for a variable, you can select the value you need from a drop-down list.

You can also add additional variables. Click the plus button in the Variables section and select a variable and operand, then enter a custom value.

Note: This connector is only supported in Tableau server version 2019.2 and later. If you create a flow in Tableau Prep Builder that uses this connector and publish the flow to an earlier server version, you can publish the flow but you won't be able to run the flow using the scheduling features in Tableau Server. Instead, you can run the flow using the command line interface to keep your data fresh. For more information about running flows from the command line see Refresh flow output files from the command line(Link opens in a new window). For more information about version compatibility, see Version Compatibility with Tableau Prep(Link opens in a new window).

Connect to Spatial files and databases (version 2020.4.1 and later)

You can connect to spatial files and spatial data sources in Tableau Prep Builder or when creating or editing flows on the web.

Tableau Prep supports the following connection types:

  • Spatial File formats
    • Tableau Prep Builder: Esri Shapefiles, Esri File Geodatabases, KML, TopoJSON, GeoJSON, extracts, MapInfo MID/MIF, TAB files and zipped shapefiles.
    • Tableau Server and Tableau Online: Zipped shapefiles, KML, TopoJSON, GeoJSON, Esri File Geodatabases and extracts.
  • Spatial databases (Amazon Redshift, Microsoft SQL Server, Oracle and PostgreSQL).

You can also combine spatial tables with non-spatial tables using a standard join and output spatial data to an extract (.hyper) file. Spatial functions, spatial joins through intersects, and visualising spatial data on a map view in Tableau Prep is not currently supported.

Supported cleaning operations

When working with shape file data, some cleaning operations are not supported. Only the following cleaning operations are available in Tableau Prep when working with shape file data.

  • Filters: Only to remove Null or unknown values
  • Rename Field
  • Duplicate Field
  • Keep Only Field
  • Remove Field
  • Create Calculated Field

Before you connect

Before connecting to spatial files, makes sure the following files are in the same directory:

  • Esri shapefiles: The folder must contain .shp, .shx, .dbf and .prj files as well as .zip files of the Esri shapefile.
  • Esri File Geodatabases: The folder must contain the File Geodatabase's .gdb or the .zip of the File Geodatabases’s .gdb.
  • KML files: The folder must contain the .kml file. (No other files are required.)
  • GeoJSON files: The folder must contain the .geojson file.(No other files are required.)
  • TopoJSON files: The folder must contain the .json or .topojson file. (No other files are required.)

Connect to spatial files

  1. Do one of the following:

    • Open Tableau Prep Builder and click the Add connection button.
    • Open Tableau Server or Tableau Online. From the Explore menu, click New > Flow.
  2. From the list of connectors, select Spatial file.

    Spatial fields are assigned spatial data type and cannot be changed. If the fields come from a spatial file, the field is assigned a default field name of "Geometry". If the fields come from a spatial database, the database field names are shown. If Tableau can't determine the type of data, the field shows as "Null".

Connect using ODBC (Tableau Prep Builder version 2019.2.2 and later)

Note: This connector type is not yet supported for web authoring.

If you need to connect to data sources that aren't listed in the Connections pane, you can connect to any data source using the Other Databases (ODBC) connector that supports the SQL standard and implements the ODBC API. Connecting to data using the Other Databases (ODBC) connector works similarly to how you might use it in Tableau Desktop, however there are a few differences:

  • You can only connect using the DSN (data source name) option.

  • To publish and run your flow in Tableau Server, the server must be configured using a matching DSN.

    Note: Running flows from the command line that include the Other Databases (ODBC) connector isn't currently supported.

  • There is a single connection experience for both Windows and MacOS. Prompting for connection attributes for ODBC drivers (Windows) isn't supported.

  • Only 64-bit drivers are supported by Tableau Prep Builder.

Before you connect

To connect to your data using the Other Databases (ODBC) connector, you must install the database driver and set up and configure your DSN (data source name). To publish and run flows to Tableau Server, the server must also be configured with a matching DSN.

Important: Tableau Prep Builder only supports 64-bit drivers. If you have a 32-bit driver already set up and configured, you may need to uninstall it and then install the 64-bit version if the driver doesn't allow both versions to be installed at the same time.

  1. Create a DSN using either the ODBC Data Source Administrator (64-bit) (Windows) or the using an ODBC Manager utility (MacOS).

    If you don't have the utility installed on your Mac, you can download one from (www.odbcmanager.net(Link opens in a new window) for example) or you can manually edit the odbc.ini file.

  2. In the ODBC Data Source Administrator (64-bit) (Windows) or the ODBC Manager utility (MacOS), add a new data source then select the driver for the data source then click Finish.

  3. In the ODBC Driver Setup dialog, enter the configuration information such as server name, port, user name and password. Click Test (if your dialog has that option) to verify that your connection is set up correctly, then save your configuration.

    Note: Tableau Prep Builder doesn't support prompting for connection attributes so you must set this information when configuring the DNS.

    This example shows the configuration dialog for a MySQL Connector.

Connect using Other Databases (ODBC)

  1. Open Tableau Prep Builder and click the Add connection button.

  2. From the list of connectors, select Other Databases (ODBC).

  3. In the Other Databases (ODBC) dialog, select a DSN from the drop-down list and enter the user name and password. Then click Sign In.

  4. From the Connections pane, select your database from the drop-down list.

Use connector plugins to connect to ODBC- and JDBC-based data (version 2020.4.1 and later)

When Tableau Prep doesn't provide a built-in connector for your data, you can connect to ODBC- and JDBC-based data using connector plugins created with the Tableau Connector SDK. Connector plugins are supported when connecting to data in Tableau Prep Builder and on the web in Tableau Server.

Note: Some connectors require the installation of an additional driver. If prompted during the connection process, follow the prompts to download and install the required driver. Connector plugins currently cannot be used with Tableau Online.

To use connector plugins, download the connector .taco file, and add it to the following directories:

  • Tableau Prep Builder:
    • Windows: C:\Users\<Windows User>\Documents\My Tableau Prep Repository\Connectors
    • MacOS: /Users/<user>/Documents/My Tableau Prep Repository/Connectors
  • Tableau Server (Flow web authoring): <Tableau_Server_Installation_Directory>/data/tabsvc/flowqueryservice/Connectors
  • Tableau Server (Tableau Prep Conductor): <Tableau_Server_Installation_Directory>/data/tabsvc/flowprocessor/Connectors

For general information about using connector plugins, see Connector Plugins Built with the Tableau Connector SDK(Link opens in a new window) in the Tableau Desktop help.

Use Custom SQL to connect to data

If you know exactly the information you need from a database and understand how to write SQL queries, you can use custom SQL queries to connect to data just like you can in Tableau Desktop. You can use custom SQL to union your data across tables, recast fields to perform cross-database joins, restructure or reduce the size of your data for analysis, and so on.

  1. Connect to your data source, and in the Connections pane, in the Database field, select a database.

  2. Click the Custom SQL link to open the Custom SQL tab.

  3. Type or paste the query into the text box and then click Run to run your query.

  4. Add a clean step in the flow pane to see that only relevant fields from the custom SQL query are added to your flow.

Use Initial SQL to query your connections

In Tableau Prep Builder (version 2019.2.2 and later) and on the web, You can specify an Initial SQL command that will run when a connection is made to a database that supports it. For example, when connecting to Amazon Redshift, you can enter a SQL statement to apply a filter when connecting to the database just like adding filters in the Input step. The SQL command will apply before data is sampled and loaded into Tableau Prep.

Starting in Tableau Prep Builder (version 2020.1.3) and on the web, you can also include parameters to pass application name, version and flow name data to include tracking data when you query your data source.

Run Initial SQL

To refresh your data and run the Initial SQL command do one of the following:

  • Change the Initial SQL command and refresh the Input step by re-establishing the connection.
  • Run the flow. The Initial SQL command is run before processing all of the data.
  • Run the flow on Tableau Server or Tableau Online. The Initial SQL is run every time that the flow is run as part of the data loading experience

Note: The Data Management Add-on(Link opens in a new window) is required to run your flow on a schedule on Tableau Server or Tableau Online. For more information about the Data Management Add-on, see Use the Data Management Add-on(Link opens in a new window).

  1. In the Connections pane, select a connector in the list that supports Initial SQL.
  2. Click the Show Initial SQL link to expand the dialog and enter your SQL statements.

Include parameters in your Initial SQL statement

In Tableau Prep Builder (version 2020.1.3 and later) and on the web, you can pass the following parameters to your data source to add additional detail about your Tableau Prep application, version and flow name. The TableauServerUser and TableauServerUserFull parameters are not currently supported.

Parameter Description Returned value
TableauApp The application being used to access your data source.

Prep Builder

Prep Conductor

TableauVersion The application version number.

Tableau Prep Builder: Returns the exact version. For example 2020.4.1

Tableau Prep Conductor: Returns the major server version where Tableau Prep Conductor is enabled. For example 2020.4

FlowName The name of the .tfl file in Tableau Prep Builder Example: Entertainment Data_Cleaned

Use Data Interpreter to clean your files

When working with Microsoft Excel files, you can use Data Interpreter to detect sub-tables in your data as well as remove extraneous information to help prepare your data for analysis. When you turn on Data Interpreter, it detects these sub-tables and lists them as new tables in the Tables section of the Connections pane. You can then drag them into the Flow pane.

If you turn Data Interpreter off, these tables are removed from the Connectionspane. If these tables are already used in the flow, this will result in flow errors from the missing data.

Note: Currently, Data Interpreter only detects sub-tables in your Excel spreadsheets and doesn't support specifying the starting row for text files and spreadsheets. Also, tables that Data Interpreter detected are not included in the Wildcard Union search results.

The example below shows the results of using Data Interpreter on an Excel spreadsheet in the Connections pane. Data Interpreter detected two additional sub-tables.

Before Data Interpreter After Data Interpreter

To use Data Interpreter, complete the following steps:

  1. Select Connect to Data then select Microsoft Excel.

  2. Select your file and click Open.

  3. Tick the Use Data Interpreter box.

  4. Drag the new table to the Flow pane to include it in your flow. To remove the old table, right-click the Input step for the old table and select Remove.

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