Triple Crown Framework for Data Visualization: Data

“You can’t have data visualization without data.”

Ryan shares tips around this critical pillar of data analytics and visualization. Hear thoughts on preparing data before you begin using it in Tableau and how adding filters to context can improve the efficiency of your workbooks.

Hi, this is Ryan with Playfair Data TV. And in this video, we’re going to be continuing our discussion about the Triple Crown Framework for Data Visualization. And specifically, we’re going to be talking about the second major pillar that I always have in the back of my mind when I’m designing anything in Tableau.

And that pillar is for data. Obviously can’t have data visualization without data. So I’m going to give you a few tips around how to use data and use it effectively so you can get the most out of Tableau software. My first major tip for you, and this is not a coincidence that this is by far number one.

My first data tip is to think strategically about the data that you need. And you should only be bringing those data rows back that you’re actually going to use and try to analyze. Let me give you an example. I cannot tell you how many times I’ve gone through the discovery process with a new engagement and through that discovery we have decided that we are going to do a series of dashboards that do a year over year analysis.

So we will be looking at two years of data. But then when I connect to the reporting layer that our data partner might tell us to look at, to analyze, I see that that reporting layer has more than two years of data. Let’s say it has 10 years of data.

Well, if I only need to do a year over year analysis, the very first thing that I’m going to do is filter out those other eight years of data. It’s going to significantly reduce the number of rows.

It’s also going to make it easier for me to manage. It’s less for me to keep track of when I’m only bringing back only the data that I need. So it has an efficiency benefit as well as a kind of mental sanity benefit that’s going to help me manage the data source moving forward.

My second tip for you is to prepare data before you open it in Tableau as much as possible. At a very basic level, this involves the shape of that data. I explain why this is such a pitfall in another video here.

It’s actually on the Fundamentals learning path if you want to hear the background story on this. But essentially instead of a horizontal layout like you might see on a typical Excel report, you almost always want to transpose that so that it’s in a tidy vertical format with a field name in each column header.

And this is such a common scenario that I’ve got to shortened URL here that I’ll share in the related content below this video that shows you how to do this type of pivoting in Tableau Prep. But again even if in Tableau Prep, we were doing that preparation and that different layout of the data source prior to opening it in Tableau, Your life’s going to be much easier versus trying to have to write a bunch of calculated fields to pivot or unpivot each of the measures in your view.

That’s the basic level. But this is also true of certain chart types. Once you start to get more advanced with Tableau, you try to create some of those kind of trendy chart types like a Sankey diagram or a funnel chart. But even some more not so trendy but very effective chart types like a unit chart.

In all of those examples the data has to be in a specific format in order to create those charts in Tableau. So point is, if you are trying to push the envelope and branch out a little bit and you’re struggling to create some of those different chart types, it could very well be related to the actual layout of your data.

So before you get too frustrated and give up on it you might kind of want to go back to the drawing board, do some research on how the data should be laid out to create those charts. Let me give you another example from my own portfolio. These stadium maps, these custom polygon maps which I became pretty well known for a couple of years ago. It’s not like I just connected to these datasets, clicked Show Me, and I made this really cool stadium map of Kauffman Stadium in Kansas City and Citi Field in New York.

No, there was a lot of behind the scenes happening to get the data in a format that allowed me to create these stadium maps. So at a basic level prepare the data before it gets to Tableau. Transpose it so that it’s in a vertical orientation instead of a horizontal orientation. And then the other point is, if you find yourself trying to build a more advanced chart type and you just can’t quite figure it out, it probably has to do with the shape of the data.

And you want to prepare that data before you connect to it and attempt to make those charts in Tableau. So we’ve covered thinking strategically about the data that we need, preparing data before it gets to Tableau. One other note on this, I’m also talking about preparing any type of calculation involving qualitative fields or text.

Text is a very heavy type of data for Tableau to process. So if you’re doing any type of segmentation and you’re using a calculated field to do that, there are better tools for the job than Tableau, frankly. Yes, Tableau can do it.

It’s a fantastic prototyping tool. So if you want to kind of test out your ideas on how to segment things by making a calculated field in Tableau that’s great. I highly encourage you to do that.

But once you know that that segment is working for you and you want to use it over and over again, I would try to move that formula outside of Tableau. And do it either in Tableau Prep. If you’re using a more enterprise tool like Alteryx do it there. But even a custom SQL workbench, you could just do it there.

I would do those calculations prior to opening it and using it in Tableau. One gigantic caveat with this tip though is I am specifically talking about preparing dimensions. So qualitative, text based calculations. I try to move out of Tableau prior to opening that data source in Tableau.

Measures on the other end, so quantitative fields, I almost never pre-aggregate outside of Tableau. That’s really Tableau’s– one of their largest strengths. You’re able to slice and dice the numbers in different ways. You can change the aggregations of things on the fly from SUM, to average, to MIN, to MAX.

You can use level of detail calculations if you need to be more precise. And have it be more predictable. But if you were to pre-aggregate all those numbers prior to doing the calculations in Tableau, you take away all that flexibility to do the analysis as you want to.

So to wrap up this tip, I’m encouraging you to prepare dimensions prior to Tableau, but I almost always leave the measures alone. I want those numbers to be as raw as possible. Then I will do my calculations and aggregations once I’m analyzing it on the Tableau side.

Then my last tip for you during this pillar during the Triple Crown Framework is to use Context filters. This is just kind of an under-known feature. I’ve kind of observed that. Not everybody knows about Context filters.

But any filter in Tableau can be what’s called added to context by just right-clicking on it on the Filters Shelf and choosing Add to Context. You will see that filter get a special gray color-coding that’s indicating it has been added to context. But what it does is it moves that filter up in the order of operations so that it happens before all of the following dimension filters and measure filters.

This is a handy guide that I will share in the related content to this video. But this is Tableau’s order of operations. And on this next screen I’m going to highlight all the different types of filters.

So on that first row, we see Extract filters and Data Source filters. Those are the two types of filters that happen before you get to the Authoring interface. So if you chose to connect to a data source you’ll land on a screen that allows you to select the database and the tables that you want to bring in to the Authoring interface to start analyzing.

If you were to click Filter in the top right corner, if you’re using an extract it would create an Extract filter. If you’re using a live data source it would create a Data Source filter. But both of those happen before you even get into the Authoring interface.

That would be related to our first tip, actually, thinking strategically about the data you need. Those first two filters would happen before we even start analyzing it. So it would improve the efficiency if you went ahead and added the filter there. Because later once you started to slice and dice things in the Authoring interface the data would already be filtered down to only the rows that you need.

But the point of showing you this now and why we’re talking about Context filters is notice on the very next line in the order of operations we’ve got Context filters. So this is the highest-level filter, the most powerful filter, that you can do once you are in the Authoring interface.

You then see Dimension filters. That’s the next strongest filter. And then you see Measure filters. It’s a little bit further down there in the order of operations.

But I have seen gigantic wins by simply adding a filter to context. One of our partners is a large retail company that called us in with almost the exclusive purpose of making their workbooks process more efficiently. They were changing a filter and they’d have to sit there and wait 10 minutes to get a response from Tableau.

We added a filter to context. It moved it up in the order of operations. And we got that load time down to about 10 seconds. What was happening is, yes, they had 65 million records. But they were very on top of their data, this particular client of ours.

And they were very action-oriented. They would look at their data day in, day out. And they would actually do stuff based on the insights that they were finding. That’s what we’re all trying to do.

But point is, instead of 65 million records they were only ever looking at two or three days of data at a time because they were so on top of it. So we added the date filter to context.

That moved it up in the order of operations. It limited our number of rows from 65 million to just the rows that were relevant for those two or three days that we were looking at. Then all of the subsequent Dimension and Measure filters happened only on that smaller subset of data.

So I think you’ll see big wins if you want to add filters to context. Of course one big tip is, you might be thinking, well, why don’t I just add every filter to context? Well if you add every filter to context they get moved up to that same row in the order operations.

And they can actually start to compete with each other and slow things down. So you can’t just add every filter to context. But I do think using them every once in a while for the most important dimensions that you’re filtering on will add some efficiency gains to your Tableau workbooks.

This has been Ryan with Playfair Data TV – thanks for watching!