Blend on Summary Data

Data blending is a method for combining data. Data blending works by supplementing the data in the primary data source with the data in the secondary data source.

When one of the data sources that you're working with is large, you can reduce query time by using data blending to combine data in the primary data source with summary data from the secondary (large data source).

For example, suppose you have two data sources: Store - North and Store - South. Store - North is the primary data source and Store - South is the large secondary data source. To reduce query times when working with a large data source like Store - South, you can use data blending to blend only on the summary data of the large secondary data source (Store - South) with the data in the smaller and presumably faster primary data source (Store - North).

To blend on summary data, you can do the following:

  1. Create a relationship between data sources.
  2. Bring summary data from the secondary data source into the primary data source.
  3. Compare primary data source data to summary data in the secondary data source.

Step 1: Create a relationship between the primary and secondary data sources

  1. Download and open the following from Tableau Public.
    1. Go to https://public.tableau.com/profile/tableau.docs.team#!/vizhome/BlendonSummaryData/Sheet1.

    2. Click Download Workbook in the upper-right corner.

    In the workbook, the Order Date dimension is already on the Columns shelf, making it the first field in the view and establishing Store - North as the primary data source for the sheet. The Store - South data source is also included in the workbook and functions as the large secondary data source.

  2. Select Data > Edit Relationships.

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

  4. In the Add/Edit Field Mapping dialog box, in both lists, click the arrow next to Order Date, select Year(Order Data), and then click OK.

  5. In the Relationships dialog box, click Add again.

  6. In the Add/Edit Field Mapping dialog box, in both lists, click Prod Type 1, and then click OK.

Step 2: Bring summary data from the secondary data source into the primary data source

  1. In the Data pane, select the Store - North data source.

  2. Select Analysis > Create Calculated Field.

  3. In the calculation dialog box, do the following:

    1. In the name text box, enter Sales for Store - South.

    2. In the formula text box, type the ZN function, the SUM function, and the Sales Totals field from the Store - South data source.

      Your formula should look like the following: ZN(SUM([Store - South].[Sales Total]))

      This formula pulls in just the sales data from the large secondary data source.

  4. Select Analysis > Create Calculated Field.

  5. In the calculation dialog box, do the following:

    1. In the name text box, enter Total Sales (North and South).

    2. In the formula text box, type the following:

      IF ISNULL([Sales for Store - South]) THEN 0 ELSE [Sales for Store - South]END

      +

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

      This formula adds the sales data from the large secondary data source to the sales data in the primary data source to get combined sales for Store - South and Store - North.

Step 3: Compare primary data source data to summary data of secondary data source

  1. From the Data pane, drag Order Date to the Columns shelf and Prod 1 to Rows shelf.

  2. Drag Measure Names to the Rows shelf, and then to the Filters shelf.

  3. In the Filter dialog box, click None, and then select Sales Total, Sales for Store - South, Total Sales (North and South) check boxes.

  4. From the Data pane, drag Measure Values to Text.

The final result is a view that summarizes primary data source and secondary data source data on the Order Year and product category.

Note: The example sales data in both Store - North and Store - South are identical and therefore yield same results for both Sales Total and Sales for Store - South fields.

 

 

 

Thanks for your feedback!Your feedback has been successfully submitted. Thank you!