Combining Multiple Tableau Data Sources into One View

Beginning with Tableau 6.0, you can use multiple data sources for a single worksheet. One data source is primary; all others are secondary. For the primary resource, Tableau selects the data source of the first field you add to the view. Or, if one of your data sources is a cube, it must serve as the primary data source. The primary data source creates relationships with fields of the same name and data type that it finds in the other data sources.
To create a view from multiple data sources, you complete these tasks:
  • Connect to the data sources.
  • Create a master data source that combines the data sources.
  • Create calculated fields to distinguish and combine data.
  • Create relationships among the data sources.
  • Create the combined view.
The sections in this article step through these tasks using data from two fictional retail stores, each differing from the other in the types of product sold and the reporting data format used. All other data is the same.

Connect to the data sources

Step 1

Open a new workbook and connect to the Superstore sample.

Step 2

Select Data > Connect to Data, and connect to the Coffee Chain sample.

Create a master data source that combines the data sources

Step 1

In a blank Excel worksheet, create the following list of all dimensions and measures needed in the final view. Type each term on its own row, all in the same column.
  • Product
  • Paper
  • Bookcases
  • Office Machines
  • Coffee
  • Espresso
  • Herbal Tea
  • Tea
Note: If your data requires additional dimensions, you must repeat them for each value. For example, if you also wanted to display the years in the data, the product list would need to be repeated for each year.

Step 2

In the Excel worksheet, select the list you just created, and then copy it to the Clipboard.

Step 3

Select Data > Paste Data.
Note: In Tableau Desktop 6.1 and earlier, select Edit > Paste Data.

Step 4

From the Clipboard data connection, drag Product to the Columns shelf.
Because the Product field is the first to be placed in the view, a blue circle with a check mark identifies the Clipboard data as the primary data source. You can switch to another source by clicking its title bar in the Data window.

Create calculated fields to distinguish and combine sales data

In this section, you create a calculated field to represent Superstore sales, one for Coffee sales, and one that provides a way to add these values to a table of combined sales data.

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, complete the following steps.
  1. For Name, type Sales Superstore.
  2. In the Formula box, build the following formula:ZN(SUM([DATA-SOURCE-NAME].[Sales]))
    replacing DATA-SOURCE-NAME with the name of your Superstore sample. The one used to create this article is called Sample - Superstore Subset (Excel).
  3. Confirm that the status message indicates that the formula is valid, and then click OK.

Step 3

Create a second calculated field, naming it Sales Coffee, and using the following formula:
ZN(SUM([Sample - Coffee Chain (Access)].[Sales]))
Replace Sample - Coffee Chain (Access) with the name of your Coffee Chain sample if it differs.

Step 4

Create a third calculated field, naming it Sales Works, and using the following formula:
IF ISNULL([Sales Coffee]) THEN 0 ELSE [Sales Coffee] END +
IF ISNULL([Sales Superstore]) THEN 0 ELSE [Sales Superstore] END
This formula tells Tableau that if the value is null, put a zero value in the table; otherwise, insert the value itself. You can add as many lines to this calculation as you need for each data source used in the table.

Create relationships among the data sources

Remember that Tableau designated the Clipboard data as the primary data source, because the first field you placed into the view was from this data source. This section steps through linking Clipboard to the two secondary data sources. It does this through corresponding fields, in a manner similar to the way you set up joins.

Step 1

Select Data > Edit Relationships.
Note: In Tableau Destkop 6.1 and earlier, in the Data window, right-click Clipboard and select Relationships.

Step 2

In the Relationships dialog box, complete the following steps to set up the relationship among the data sources.
  1. In the Secondary data source list, select the coffee chain data source.
  2. Select Custom, and then click Add.
  3. In the Add/Edit Field Mapping dialog box, select Product on the left and Product Type on the right, and then clickOK.The Product field from the Clipboard data source is now linked to the Product Type field in the coffee chain data source.

    Note: If a default mapping shows Product mapped to Product, remove that mapping by clicking the red X that appears when you hover the mouse over the mapping.
  4. In the Secondary data source list, select the Superstore data source, and repeat the previous steps to map the Clipboard Product field to the Superstore Category field.
  5. Click OK in the Relationships dialog box.

Step 3

In the Data window, select each secondary data source to confirm that, in the Dimensions pane, a link icon appears as a visual indicator next to the linked fields.
In the Coffee Chain data source, the indicator appears next to Product Type.
In the Superstore data source, the indicator appears next to Category.

Create the combined view

Step 1

Select Worksheet > New Worksheet.
Note: In Tableau Desktop 6.1 and earlier, select Edit > New Worksheet.

Step 2

In the Data window, select the Clipboard data source, and from the Dimensions pane, drag Product to the Rows shelf.

Step 3

Select the Coffee Chain data source, and from the Dimensions pane, drag Product Type to Detail on the Marks card.

Step 4

Select the Superstore data source, and from the Dimensions pane, drag Category to Detail.
If an alert appears to indicate that you could use a join here, you can ignore the message for this scenario.

Step 5

In the Data window, select the Clipboard data source.

Step 6

In the Measures pane, double-click each of the calculations you created earlier:
Sales Coffee, Sales Superstore, and Sales Works.
The measures are added to the Measure Values shelf and the view.

Step 7

If the automatic view associates the values with color, you can change the view so that the values appear instead. On the Marks card, drag Measure Values to Label.

Step 8

Select Analysis > Totals > Show Column Grand Totals.
Note: In Tableau Desktop 6.1 and earlier, select Table > Column Grand Totals.
The table shows sales from the Coffee Chain and Superstore files, as well as their combined total.
Alternate Search Terms:primary, secondary, cube, data source, datasource, blending