Tableau Data Blending with Summarized Data

If one data connections is large and takes a long time to query, using a join to link this table with another table can increase query time dramatically. A better approach is to use date blending to blend only the summary of the larger table with the smaller table.

For example, you can set up data blending on the order year rather than the order date, or on the product category instead of the product name to reduce the size of data returned from the second data source.

Note: Be sure to set the smaller and faster table as the primary data source and use data blending to pull a summary from the larger table.

In this example, assume the SQL database is the larger and slower database.

Create relationships

Step 1

Open a new workbook and connect to Superstore.

Step 2

Select Data > Connect to Data and connect to a SQL data source.

Step 3

For Tableau Desktop 7.0 and later, in the Data window, select Data  > Edit Relationships.
For Tableau Desktop 6.1 and earlier, in the Data window, right-click Superstore and select Relationships.

Step 4

In the Relationships dialog box, select Custom and click Add.

Step 5

In the Add/Edit Field Mapping dialog box, in both lists, click the plus sign by Order Date, and then select YEAR(Order Date).

Step 6

Click OK.

Step 7

In the Relationships dialog box, click Add again.

Step 8

In the Add/Edit Field Mapping dialog box, in the Primary data source field list, select Product 1 - Category.

Step 9

In the Secondary data source field list, select Product Category, and click OK.

Create calculated fields

You need to create a calculated field for the Sales from the secondary data source, and one for the total of Sales from both data sources.

Step 1

In the Data window, select Superstore.

Step 2

Select Analysis > Create Calculated Field.

Step 3

In the Calculated Field dialog box, make the following selections to create this formula:

ZN(SUM([Orders$ (MyDatabase)].[Sales]) )

1. In the Name text box, type Sales SQL.
2. Click the Formula text box.
3. In the Functions list, double-click ZN.
4. In the Fields drop-down list, select Orders$ (MyDatabase).
5. In the Fields list, double-click Sales.

Step 4

When finished, click OK.

Now create a calculated field for the Total Sales.

Step 5

Select Analysis > Create Calculated Field.

Step 6

In the Calculated Field dialog box, make the following selections to create this formula:

IF ISNULL([Sales SQL]) THEN 0 ELSE [Sales SQL] END
+
IF ISNULL(SUM([Sales])) THEN 0 ELSE SUM([Sales]) END

1. In the Name text box, type Total Sales.
2. Click the Formula text box.
3. In the Functions list, double-click IF, then ISNULL.
4. In the Fields drop-down list, select Superstore.
5. In the Fields list, double-click Sales SQL.
6. Click outside the closing parenthesis, space, type THEN 0 ELSE, and space again.
7. In the Fields list, double-click Sales SQL.
8. Space, and type END.
9. Start a new line, and type a plus sign.
10. Start another new line, and in the Functions list, double-click IF, ISNULL, and then SUM.
11. In the Fields list, double-click Sales.
12. Click outside the closing parenthesis, space, type THEN 0 ELSE, and space again.
13. In the Functions list, double-click SUM.
14. In the Fields list, double-click Sales.
15. Space, and type END.

Create the view

Step 1

From the Dimensions pane, drag Order Date to the Columns shelf and Product 1 - Category to the Rows shelf.

Step 2

From the Dimensions pane, drag Measure Names to the Rows shelf.

Step 3

Right-click Measure Names and select Show Quick Filter.

Step 4

On the Measure Names quick filter, clear the All check box, and then select the Sales check box.

Step 5

From the Measures pane, drag Measure Values to the Text shelf on the Marks card.

Step 6

From the Measures pane, drag Sales SQL and Total Sales onto the Measure Values shelf.

The SQL Sales numbers are missing for 2010, so you may want to remove that column from the table.

Step 7

On the Columns shelf, right-click YEAR(Order Date) and select Show Quick Filter.

Step 8

On the YEAR(Order Date) quick filter, clear the 2010 check box.

The final result is a view that summarizes on order year and product category.