How Data Blending in Tableau Replaces Multiple Reports with a Single Interactive Dashboard
Posted October 08, 2017 by Ana Biswas
Could you benefit from replacing your traditional reports with automated dashboards? The data analytics tool Tableau automates and combines multiple reports into a single interactive dashboard.
With some tweaking, reports developed for different purposes and in different formats can be combined. The following case study will walk you through how the Volanno data analytics team used Tableau’s data blending functionality to consolidate data from traditional reports at different levels of detail into one comprehensive dashboard that replicates traditional reports while also providing additional data analytics capabilities.
Volanno initiated a Planning phase and Data Discovery phase to analyze each individual report and the data source of each individual metric. In this particular case, we had more than ten reports, twenty critical derived metrics, and at least five different data sources.
After the Data Discovery phase, we moved into the Data Preparation phase, where we looked for missing data, data quality issues, and duplicate data. We greatly improved data quality by changing the primary data.
After these initial phases, we were faced with combining multiple data sources into a single dashboard. We discussed how to combine data of various levels of granularity, such as daily and hourly data. We could have joined tables in SQL or Tableau, or taken advantage of Tableau’s data blending functionality. Another option was creating a new data warehouse with custom ETL code. However, our client favored a rapid prototyping approach to facilitate customer collaboration throughout the dashboard development lifecycle. Therefore, we chose to use Tableau’s data blending functionality.
Tableau’s data blending functionality queries each data source used by the worksheet separately. The results of the queries are then combined within Tableau. Blending requires a primary and a secondary source, so Tableau uses the rows from the primary data source (identified by a blue check mark, see Figure 1) and the aggregated rows from the secondary data source (identified by an orange check mark, see Figure 1). Aggregation occurs using the aggregate function. Data blending is a variation on a left join, but this join only takes place after aggregation, not at a row level.
It is important to remember that data blending will not increase granularity. You’re ready to use the data blending feature in Tableau after you have aggregated and queried the data and assigned aliases. If necessary, use Tableau’s densification feature to fill in any missing values or records.
Here is an example of a data blend.
Table 1 displays the differences between a traditional left join and data blending within Tableau.
Figure 2 compares a join with a data blend. In this example, three different tables had differing levels of granularity: the first showed data for a specific period of time; the second displayed daily data; and the third displayed hourly data. Figure 2 demonstrates that from the join we are getting larger numbers, whereas from blending for the same day we are getting the desired results.
The join has combined the rows from both data sources into one dataset, returning a result that is not aggregated in the chart. The data are not wrong; they are just no longer summarized to the appropriate level. The blend has retained aggregated measure from the primary source and added the unique data from the secondary source summarized by the same dimension. By aggregating the data by the granularity level of the primary data source, we reduced the query time, simplified the data set, and achieved the desired results more efficiently. Thanks to this technology, in a short time we were able to deliver to our client a single, interactive dashboard that consolidated multiple traditional reports in one usable interface.