Thoughts on Tableau - Data Modeling and EAV

Note about this post. There is no one-size fits all approach to Tableau. Everything is based on context, and what the ultimate end goal is. What I am about to discuss works well for me in a lot of my use cases, but not all of them (and the fact that I prefer calculations over manipulations). This approach might not be the best for you, so please consider your individual situation. This article is sharing of my personal ideas, and is only meant to serve as an additional resource.

Tableau is an incredibly powerful data visualization tool. And one that far exceeds anything else on the market. There is nothing that let's you fail faster and discover more. And for the most part, it's very simple to use.

But, like most things, it can be difficult at times.

Working with Tableau everyday, and being very active in the community, I have come across quite a few challenges that required the implementation of more advanced techniques, such as Scaffolding, Data Densification, Conditional Formatting, etc  (If you interesting in learning more about this, look no further than Joe Mako's blog, and his incredible videos http://www.joemako.com/ ). I Consider these techniques to Manipulations of Tableau (This isn't a bad thing, it just can be complex to workout). Most of the time (But not all) these techniques had to be used because the Data simply wasn't modeled in a way that allowed us to create the intended viz.

I'm sure most of you have heard this before, "Tableau prefers Long Tables, over Wide Tables". Well what exactly does that mean? To start, we have to realize that Tableau is a Database driven mathematical drawing tool, i.e. it is not excel. It does not think in terms of Cells, but rather Rows/Columns and Aggregation. Essentially, Column names are Entities, and Rows/Records are treated as Values/Attributes of those entities. But Tableau takes this a step further by splitting Columns into Dimensions and Measures, based on the interpreted Data Types (There is more to this when discussing Continuous vs Discrete Measures and Dates, but I'll save that for a later discussion).

Rows/Records that are treated as Dimensions, act as attributes of an Entity. For example, for Column [Customer], [Customer] is the entity, and the Rows/Records containing Customer Names are the attributes. Rows/Records that are treated as Measures, are values that correspond to those Attributes to a given entity.

We can do a lot in Tableau with Data in this model. Where we have a single Main Entity (Customer) and several Sub-Entities (The Measures), and there is a single unique Row, for each Main Entity. Since Tableau is a Database Driven Tool, we can Aggregate Sales, Commission and Profit, we can easily create row level calculations of the measures, we can choose to Filter by Customer, etc. But you can run into problems with this model, depending on what your end goal is.

For example, lets say you wanted to do some conditional formatting on the Measure Names/Values. At this moment, you can't do this very easily in Tableau, mainly because we cannot use Measure Names/Values in calculations. So we have to do workarounds and manipulate Tableau. (Details can be found here, http://drawingwithnumbers.artisart.org/older-but-still-useful-conditional-formatting/. 

Though this isn't too complex, it makes us have to think outside of the box. How can I trick Tableau into doing this. And though this type of process helps us gain a better understanding of Tableau and we simply need to do this for some edge cases, it's not always the best approach.

Now this is just one example, but it's one that comes up a lot. The other issue usually revolves around Dates, where you have a Start and End Date in two distinct Columns. This is a problem because, as discussed before, Tableau recognizes Columns as Entities (Each Column is an Entity). So we can't plot both Columns as a Single Entity. If you want to learn more about this, please refer to http://redheadedstepdata.io/lookup-vs-transactional/ by Keith Helfrich. Note* there are a lot of parallels to his article and mine, and I highly recommend you read it, if you haven't already (He does an amazing job at explaining this concept).

So what can we do? 

Before we ever get into Tableau, we can transform our data using an EAV (Entity-Attribute-Value) model. Googling EAV Model, might lead to some confusing results, because EAV is generally used in terms of Database Design, but we are more focused on a single Table aspect. Wikipedia describes EAV Model as "...a data model to describe entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest." (https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) This goes back to the old Tableau sang, "Tableau prefers Long Tables, over Wide Tables". 

With an EAV Model, we are eliminating columns, and instead are creating additional Row/Records. And in this case, an additional Dimension.

To be basic, EAV approach results in a Table Similar to this.

Where we Pivot our Data, so that we have one single Entity (Customer), an Attribute (Metric) an a Value (The Measure Values). You can think of this in terms of a Hierarchy. Where there is a One to Many relationship between and Entity and Attributes, but there is a One to One relationship between Attributes and their Values

Since we pivoted the data into an EAV Model, we have a new Dimension (Metric) and a Value that corresponds to each Attribute of that Dimension. This allows us to easily create conditionally formatted tables, without too much work.

The thing to remember is that Tableau is driven on Aggregation, and Dimensions/Discrete Measures, are treated Attributes. So if this were SQL, these would be what is included in your Group By Clause. With that in mind, we have to remember to Aggregate our Calcs. So to create Conditional Colors, we have to use Aggregated calculations.

IF MIN([Metric]) = 'Profit' AND SUM([Value]) < 0 THEN 'Red' ELSE 'Black' END

Anther place where using an EAV approach is useful, is swapping measures in a viz. With an EAV approach, we just need a Drop Down filter of Metric. Thus eliminating the need of a Parameter.

Now you might be saying to yourself, "Well what if I want to find the difference from Sales and Discount, how would I do that?". It is something that can be easily done using a traditional model, it's just a row level calc. This gets into why I prefer EAV over other models in Tableau (For a lot of personal use cases). In Tableau, we generally cannot create new Rows/Records. We can simulate it, and we can manipulate, but in general terms, we just can't. This is where we run into a lot of problems for certain cases, we simply cannot combine Columns, and create new Rows. We can, however, create new columns very easily. So by using EAV, we get the best of both worlds.

Given the example above, I can create a new Columns/Measures for each of Metric, i.e

Sales = IF [Metric] = 'Sales' THEN [Value] END
Profit = IF [Metric] = 'Profit' THEN [Value] END
Discount = IF [Metric] = 'Discount' THEN [Value] END

And once I have this, I can create new Columns from them, or use them individually.

Sales (Minus Discount) = SUM([Sales]) - SUM([Discount])

Since we have the ability to create LoD calculations, we can even take this a step further, and set the level of aggregation we want this to happen on. By using this model, we can create everything that the standard model can through Calculations, instead of Manipulations. Plus, we get the benefit of having a Dimension that describes our Values, allowing us to easily create otherwise difficult visualizations.

And, we can still be creative, we can still flex our Out-of-the-box thinking to manipulate Tableau, if we need to. For example, creating a Bar Chart and a Crosstab in a single viz.
https://community.tableau.com/thread/196232

Final Note/Thoughts*

Like I've said before, I prefer this approach for a lot of my use cases, mainly because I feel it gives me more flexibility. I like thinking through Tableau in terms of Calculations and Aggregation. What can be considered a downside to this approach, is the fact that you have to force yourself to think in terms how Tableau aggregates your result set in the viz. You have to force yourself to see Tableau as drawing graphs based on the results of Aggregated Queries. It can be more difficult to create/use "Row Level Calcs", since you might have to enforce aggregation on the Calcs or use LoDs. The other potential downside to this is Size. Using this method will create a lot of additional records, and will increase the overall size of the dataset. So if you are working with very large datasets, that would be made even larger, this might not be the best approach.

So, back to the beginning...Is this the best model of all use cases? NO, there is no one model (Whether it be Data or Mental) that works for every situation. However, it is a method that I tend to use a lot, and it works well for me. My hope is that this will help others think about Tableau differently, and maybe, makes things a little easier.

If you have any thoughts on how this post can be improved, or if you simply disagree with me, please, please, PLEASE, share your feedback. I love learning from my mistakes, and any feedback/discussions will certainly help myself, and others who come across this post.

Best regards,
Rody Zakovich