Data densification could take place in the source if you chose to fill in missing data with certain joins, unions, or custom queries. But here, we are focused on data densification that takes place in Tableau after aggregate data is returned from the source. Specifically, under certain circumstances that we'll now consider, Tableau fills in missing values in the aggregate data in the cache, as seen in this diagram:
You'll recognize this diagram as very similar to the diagram we examined when we started the discussion on Table Calculations. In fact, data densification happens at more or less the same time as Table Calculations, and can sometimes even be triggered by Table Calculations. Here are some examples of times when data densification is enabled:
- When the Show Missing Values option is enabled for dates or bins used as headers on Rows or Columns. Here, Tableau will show headers for dates or bin values (between the minimum or maximum dates/bin values), even if they don't occur in the data (or are eliminated by a filter). You can easily turn this densification on or off by selecting the desired option.
- With Show Missing Values enabled, certain Table Calculations used in the view will additionally add marks in the view for the missing headers. We'll see an example of this later in this section.
- Enabling the Show Empty Rows/Columns option (to do this, from the menu, select Analysis | Table Layout | Show Empty Rows/Columns.) This causes Tableau to show all row/column headers, even if particular values wouldn't normally be shown based on filter selections. This option is context-specific, so the domain of values shown is either for the entire dataset, or for the context defined by context filters. Observe the difference between the Categories shown with and without the option checked:
- Using certain Table Calculations with discrete dimensions on Rows and Columns will cause Tableau to turn on data densification.
Let's take a look at an example and how to optionally turn off the resulting densification. Observe the difference between the two views, as follows:
This view has 14 marks (you can see the count in the status bar), indicating that there are fourteen valid intersections of Container and Ship Modes. Some combinations simply don't occur in the data (for example, a Jumbo Drum is never sent by Express Air).
But adding a Table Calculation such as Index() to the Detail causes Tableau to fill in the missing intersections, like this:
Tableau has filled in the combinations of Container and Ship Mode and there are now 21 marks. Sometimes, this behavior might be useful (and we'll see such an example next), but many times, you may want to avoid the densification. How can you turn it off?
With an understanding that Tableau has enabled the densification because of the discrete dimensions on Rows and Columns, you can rearrange the view so that only one dimension remains on Rows or Columns. This view, for example, keeps Ship Mode on Detail as part of the view level of detail, but uses the special aggregation ATTR on Columns, as shown in the following screenshot:
The result is a view without data densification showing only 14 marks.