Best practices for tidy data using Tableau Prep

Who is doing the analysis?

Consider the end users of your final data set. For example, are you the sole user that will access and understand all parts of the data for thorough analysis? Or will the data set be used by someone in a different role, like a marketing manager who needs to understand how a particular campaign is performing based on select, identified metrics? If it’s the latter, then you’ll likely want to trim down the data set to only those measures.

Or maybe there is a product code in one data table, but the marketing manager needs to know the product name. In this case, you would join the data and fact tables to get the information. Audience is critical when you're preparing data, similar to when you’re creating a dashboard.

What type of questions need to be asked or answered?

In the data prep process, it is important to understand how people will use the final data set—for complex analysis or for a quick summary. This detail influences the data preparation process significantly, determining both the amount of effort and detail.

You can typically predict the most common questions that people will ask of the data based on your understanding of strategic business priorities, but there will likely be unanticipated questions that pop up. When you're preparing a data set, there’s a balance between serving the immediate questions and allowing for further exploration. For example, someone may see a sales trend during the last six months, but digging into a spike during a particular week requires deeper analysis and a daily granularity of the data.

Know what you’re looking at

Before you bring data into a data prep tool, it’s important to understand what you’re working with; you need to know whether you’re looking at the entire data set or only a subset. You may also need to do some data exploration before you start cleaning.

Adjust your sample size

When you connect to a large data set, you’ll probably want to limit it to a sample of the data to increase the speed of your data prep process and to optimize performance. There may be times where you want to see the full data set and Tableau Prep lets you do either. If a sample doesn’t help you solve your data prep task, here are some things you can try:

• Increase the sample size of your data. Go back to the input step and adjust the number of rows for the sample. You can increase the number of rows or include all the data, but be aware this could slow down the performance. Another caveat is that using a fixed number of rows will return what the underlying database uses as criteria for the fastest way to return the rows requested (i.e. this does not necessarily mean the top 1,000 rows in the database).
• Use random sampling. By default, Tableau Prep will calculate the optimal number of rows to return depending on the total number of fields in the set and the data types of those fields. The random sampling happens on the database level, returning the number of rows requested. The database looks at every row and returns a sample. This option is not available for all data sources and may also impact performance.
• Add an input step filter. By adding a filter at the input step, you ensure that the data that is pulled into your data set is relevant to your analysis. This gives you a more representative sample, while helping with performance.

Explore your data

First, you’ll likely want to see the unique number of values in a given field. In the example below, a quick glance at the top of the column header reveals the amount of states represented in the data set. You’ll also want to know how different values relate so that you can spot data outliers or issues. In Tableau Prep, you can use highlighting to discover relationships across fields. When you click on a value in the profile pane, it narrows the data grid view to show the records that have those values in the indicated field. Tableau Prep highlights the values across fields and turns the related values blue.

Remove unnecessary data

To optimize the overall performance of your data prep process, limit the fields you bring into Tableau Prep to the ones you'll need for your analysis.

Let’s say you’re preparing a data set that represents your company’s sales and product data. You know that you’ll later bring this data set into Tableau to analyze sales performance for the year. In this case, you may not need to include details around the ship date for every product because it won’t tell you much about the sale or why a customer bought the product. It’s only an indication of when the product left the warehouse—so you can likely remove it from the data source. If at any point during your preparation, there is a field you realize you no longer need, you can simply remove the field during your flow.

Hint: While you’re going through your preparation process, you may also start splitting fields, breaking them into multiple columns. You probably don’t need the original column you just split, so go ahead and delete it.

Filtering your data also saves time in the process and ensures you’re doing the right analysis. For example, if you know you only need to look at sales data from the last two years, filter your date field to that time frame with the range or relative date filter. There may be irrelevant or incorrect data that you want to remove. You can simply click on a value in the data pane and exclude it. This can be done at any point in your flow.

Review and clean

In Tableau, your analysis will be impacted by data types—and it is important to appropriately identify each field before diving in. While you can edit aliases, change data types, split fields, and write calculations in Tableau, it’s a lot easier to perform these actions upfront, especially when creating the data set for someone else.

It is essential to understand the data quality for every field. For example, telephone numbers collected from a survey may have various formats—especially if it was from a global pool of people. Manually inspecting thousands to millions of unique values for the sake of consistency is often tedious and error-prone. Identifying patterns and updating the data in bulk has major implications for a cleaner data set. And using Tableau Prep’s built-in functions—like quick clean steps that allow removal of punctuation, numbers, letters, or trim spaces—give you the ability to make these changes, saving you time and sanity.

For instance, if your state field has “California” and “CA” when the rest of the values have the full state names you can change the values directly and voila, the “California” records now include instances of “CA.”

You may also notice your data is mostly right with an occasional exception. Tableau Prep is smart. It helps standardize data values by leveraging algorithms to do some of the heavy lifting. Perhaps you have a column of data where customers input the name of the city they’re from. After quickly scrolling through the column, you notice there are several misspellings for the city of Albuquerque. Rather than update each one manually, Tableau Prep has built-in functionality to group and replace by common characters or pronunciation. These options use algorithms to simplify the cleaning process so you don’t have to. Or, if you anticipate a missing value, you can manually add it for incorporation when running the entire data set through the flow. If you know a field needs to be cleaned or filtered, but requires something beyond what’s in the user interface, you can use a calculation.

Prepare the way you think

Data preparation has a lot of different components, from restructuring to reformatting to cleaning, and you should not be constrained by a specific order. Tableau Prep lets you make changes and updates to your data in a way that fits how you think. While some people may start with pivoting the data first, others may start with cleaning up misspellings or missing data.