Video |

Tableau | Data prep | Data visualisation

Tableau Prep Explained in under 10 mins!

How do you make data prep visual? Here's how Tableau Prep tackles it in under 10 minutes.

Tim Ngwena •16 October 2020 •5 min read •10 min watch •218K

Tableau Prep treats data prep as a journey from inputs to outputs, with cleansing, joins, unions, pivots and aggregations codified as visual steps
Compared to VLOOKUPs in Excel or SQL joins, Prep keeps everything visual and shows matched versus unmatched rows with colour coding at each step
A typical flow connects files, adds clean steps to check data, drags one step onto another to create a join, builds calculated fields, removes unneeded columns and outputs to CSV, database or Tableau Server
You can name and add descriptions to steps so flows are well documented for others, and fuzzy matching makes grouping a one-click operation
From 2020.3 you can write Prep output to databases, and with the Data Management add-on and Prep Conductor you can schedule and govern flows on Tableau Server or Online

0:00Hey, it's Tableau Tim and I'm back with

0:02another sketch note, this time explaining

0:04Tableau Prep Builder.

0:06Yes that's right, this will be the first of

0:08many sketch notes explaining products in

0:10Tableau's platform and I couldn't think of

0:12a better place to start than Tableau's

0:14newest product, Tableau Prep.

0:16If you haven't had a chance yet, check out

0:18my 10 minute explainer about the whole

0:20Tableau

0:20platform.

0:21We briefly touched on Tableau Prep and more

0:23importantly, we talked about how Tableau

0:25Prep

0:26fits into the entire Tableau platform.

0:29So it's great context for this video, check

0:31it out in the pop up on the top right of

0:32the

0:32screen right now.

0:34In that video, we talked about the core

0:36innovation that applies across all of Table

0:38au's products,

0:39visual analysis.

0:40And whilst that might seem obvious when we

0:42talk about data visualization, data prep on

0:44the other hand is a bigger challenge.

0:47How do you make data prep more visual?

0:49Okay, buckle up while I walk you through

0:51Tableau Prep and the fundamental concepts

0:53behind how

0:54it tackles data prep in a visual way and em

0:57powers everyday business users to cleanse

0:59and transform

1:00their own data.

1:01The first thing we should do is step back

1:04and explain what we mean by data prep.

1:06If you think back to the analytical cycle

1:08in my Tableau explainer, businesses store

1:10data in lots of different places and as a

1:13result, when it comes to retrieving it, you

1:15might encounter a range of problems.

1:17You might have some static data sources

1:19such as files on your desktop that you need

1:21to

1:21bring together with data from your database

1:23.

1:23In other cases, the data might all come

1:25from the database but they're stored at

1:27different

1:28levels of aggregation, making it hard to

1:30make the accurate comparisons you need for

1:32the

1:33decision making process.

1:34In other cases, you might just need to do

1:36some grouping to create a new product or a

1:38business category in your organization.

1:41Generally speaking, common issues like the

1:43ones I just mentioned are documented and

1:45even

1:45taken care of in your data warehouse.

1:48In other cases, well, you just don't notice

1:50the mistakes until you start visualizing it

1:53in Tableau.

1:54I can't tell you how many times desktop

1:56users have been the bearers of bad news

1:58when

1:58they start visualizing data that's not

2:00traditionally been visualized before.

2:03Add to this the explosion of data from

2:05digital systems and IAT and it's pretty

2:07clear that

2:07businesses can't rely solely on data ware

2:10housing teams to handle data cleansing and

2:12transformation

2:13challenges especially when the analytical

2:16needs for that work is actually generated

2:18by the business users.

2:20Tableau is not the first to recognize this

2:22problem.

2:22There are plenty of well established tools

2:24in the market.

2:25SQL which is used by nearly all traditional

2:27databases was specifically designed to take

2:30on this challenge of standardizing how we

2:33store and manage data in databases.

2:36Visual tools such as Alteryx already exist

2:38and you can do pretty advanced statistical

2:40computing in languages such as R and for

2:43programmers Python.

2:44So what problem is Tableau Prep actually

2:46solving?

2:47Well instead of telling you, let me show

2:50you.

2:50Let's take a simple example.

2:52Let's say I want to compare two lists and

2:54find the matching items.

2:56In Excel you'd start with your two datasets

2:59in two sheets.

3:01At this point even though you've done VLOOK

3:03UPs lots of times you're probably heading to

3:05YouTube to remind yourself how the function

3:08is named and used.

3:09Yes I did the same to make this video.

3:12Some lookup values, table arrays and column

3:15indexes later you have a formula that looks

3:17something like this.

3:19Not too bad right?

3:21In SQL well at least you just write

3:23something simple like this.

3:25Simple right?

3:26For some yes this is simple but for others

3:29this looks like a foreign language.

3:31And how many data prep problems are

3:33actually this simple?

3:35More complexity and in Excel you'd be

3:37calling your VBA or Macro Guru and in SQL

3:39well you're

3:40probably sending off a support ticket to

3:42your DBA and data warehousing team who

3:44might as

3:45well be called hieroglyphists at this point

3:47because have you seen the complex stuff

3:49they

3:49write to solve basic problems?

3:51Now you're probably thinking hey Tim quit

3:53stalling get to the point, how is Tableau

3:55Prep different?

3:56Okay okay I hear you.

3:58Tableau Prep takes the process of cleansing

4:00and transforming data and makes it simple

4:02and easy to use for everyday business users

4:05.

4:05It imagines data prep as a journey with

4:08data inputs as the start and outputs at the

4:11end.

4:11Along the way you carry out a series of

4:14steps.

4:14You might want to do cleansing, aggregation

4:17, pivoting, joins, unions and so much more.

4:20All of these are codified using visual

4:22indicators and when you work with them they

4:24stay visual

4:25all the way through.

4:26Tableau Prep is always showing you your

4:28data in a clean and summarized interface

4:30and a

4:30summary explaining how the operation you're

4:33doing affects your data.

4:35So to summarize in prep you'd take your two

4:37inputs connect them in prep you'd then use

4:40a join operation and prep would show you

4:42how many matched and how many don't

4:45alongside

4:45a table view showing you the result of your

4:48join.

4:49You can then of course save the output to a

4:52file.

4:52Simple okay instead of sketching it let me

4:55just actually show you in Tableau Prep.

4:58Okay so we grab our files and we drop them

5:00onto the canvas and we bring our two lists

5:03onto Tableau Prep.

5:08Now that we've done that we add two clean

5:10steps just to check our data is correct and

5:12you can see here I get a summary of the

5:15lists in the view below.

5:18Now I'm ready to join the files by just

5:20dragging one of these steps onto the flow

5:22above and

5:23we automatically get a join.

5:26Notice how it shows us which type of join

5:27we're doing and it shows us the result here

5:30on the bottom left along with a summary on

5:32the right showing us the unmatched rows and

5:34the matched rows very very clearly with

5:36color coding to tell us which side of the

5:38data they're

5:39coming from.

5:40To finish off we'll add a few clean steps

5:43just to do some basic data prep.

5:45In this example I'm going to create a new

5:48total combining amount one and the amount

5:50field from both lists to create a total

5:58amount.

5:58And then now that I'm done I'm going to

6:00bring that column over to the right hand

6:02side then

6:02I'm going to remove the three columns I no

6:06longer need just to clean this data set up.

6:11And perfect now we're ready to output this

6:17and we have our data set ready to go.

6:21I'm just going to name some steps here so

6:22it's easy for someone else to follow when

6:24they open up this file.

6:25I can even add descriptions to some of the

6:28steps in case the name isn't enough to

6:30describe

6:30what's going on.

6:32In this case I'm going to describe the

6:34total calculation that I did and the column

6:36cleanup

6:36that I did.

6:42Great now we have a very clean flow it's

6:44well documented and we're ready to export

6:46this.

6:48Notice that we have several file types here

6:50I can publish to the Tableau server or I

6:52can

6:52publish to a database but for static files

6:55we're just going to export this to a CSV.

6:57All the way through Tableau prep focuses on

6:59always being visual giving you a clear

7:02instant

7:02picture of your data at every step.

7:05It builds in smart tools to help you fix

7:06complex problems such as fuzzy matching to

7:08make grouping

7:09a one click operation whilst keeping true

7:12to its Tableau heritage and using a

7:14familiar

7:14interface to make a relatively young tool

7:17feel similar to established Tableau users.

7:20New data sources you can connect to are

7:22continuously being added and whilst we're

7:24on the topic of

7:24data sources you can of course share your

7:27prep data sources to your Tableau server as

7:29files or fresh in 2020.3 you can write to

7:32databases allowing you to stay in the flow

7:35of your analysis and share your insights

7:38across the enterprise.

7:39If that's not enough with the data

7:41management add-on and Tableau prep

7:43conductor you can

7:44share your flows on Tableau server or Table

7:46au online and share your flow so they run in

7:48the background with all the same oversight

7:51security and governance you become used to

7:54in Tableau server and Tableau online.

7:56All this makes it easy for everyday

7:57business users to transform and prepare the

7:59data they

8:00need not only to answer questions but to

8:02keep up with the fast pace of asking and

8:04answering

8:05questions in a world where data combined

8:07with fast paced analytics is increasingly

8:09becoming

8:10a core part of decision making.

8:13As a consultant who's worked in the

8:14business intelligence base for the last 7

8:16years I personally

8:17really enjoy using Tableau prep.

8:19Yes it might be new and needs some pol

8:21ishing around the edges but it's constantly

8:23changing

8:23with features and patches nearly every

8:25month.

8:26It's got a truly innovative approach to

8:28some of the most common data challenges

8:30everyday

8:30users face and you can of course expect the

8:33same vibrant and helpful community you've

8:36come to know in the Tableau and now Sales

8:38force community.

8:39If you're just getting started be sure to

8:41head to the Tableau website and check out

8:42their getting started videos and their

8:44video explaining Tableau prep.

8:46Of course I have my own videos in the

8:48playlist on this channel for you to check

8:50out.

8:51Find new feature releases and updates as

8:53they get released every month.

8:54If you're looking for a bit more head to

8:56the Tableau forums and feel free to ask

8:58questions

8:59or ask for help if something is vexing you.

Introduction and visual data prep

This is the first of many sketch notes explaining the products in Tableau’s platform, and there’s no better place to start than Tableau’s newest product, Tableau Prep Builder. If you’ve seen my 10 minute explainer on the whole Tableau platform, you’ll remember the core innovation that runs across every product: visual analysis. That feels obvious when we talk about data visualisation, but data prep is a tougher challenge. How do you make data prep more visual? That’s exactly what Tableau Prep sets out to do, empowering everyday business users to cleanse and transform their own data.

What data prep actually means

Businesses store data in lots of different places, so retrieving it throws up a range of problems. You might have static files on your desktop that need to be brought together with database data. You might have data stored at different levels of aggregation, making accurate comparisons difficult. You might just need to do some grouping to create a new product or business category.

Many of these issues are documented and handled in the data warehouse, but plenty only surface when you start visualising the data in Tableau. Desktop users are often the bearers of bad news when they visualise data that has never been visualised before. Add the explosion of data from digital and IoT systems, and it’s clear businesses can’t rely solely on data warehousing teams to handle cleansing and transformation, especially when the analytical need is generated by the business users themselves.

Existing tools and their limits

Tableau is not the first to recognise this problem. SQL, used by nearly all traditional databases, was designed to standardise how we store and manage data. Visual tools such as Alteryx already exist, and you can do advanced statistical computing in R or Python. So what problem is Tableau Prep actually solving?

Comparing two lists: Excel and SQL

Take a simple example: comparing two lists to find matching items. In Excel you’d start with two sheets and reach for a VLOOKUP, probably heading to YouTube to remind yourself of the syntax. A few lookup values, table arrays and column indexes later you have your formula. In SQL you’d write something fairly short. Simple for some, but for others it reads like a foreign language, and how many data prep problems are genuinely that simple? Add complexity and in Excel you’re calling your VBA or macro guru, while in SQL you’re raising a support ticket to your DBA and data warehousing team.

How Tableau Prep is different

Tableau Prep takes cleansing and transforming data and makes it simple for everyday business users. It imagines data prep as a journey, with data inputs at the start and outputs at the end. Along the way you carry out a series of steps: cleansing, aggregation, pivoting, joins, unions and more. All of these are codified using visual indicators, and they stay visual the whole way through. Prep always shows you your data in a clean, summarised interface, along with a summary explaining how each operation affects your data. Take two inputs, connect them, apply a join, and Prep shows you how many rows matched and how many didn’t, alongside a table view of the result, ready to save to a file.

Live demo: building a flow

We grab our two lists and drop them onto the canvas. Then we add two clean steps to check the data is correct, with a summary appearing in the view below. To join the files, we drag one step onto the flow above and a join is created automatically. Prep shows which type of join we’re doing and the result on the bottom left, with a summary on the right colour-coding the matched and unmatched rows by which side of the data they came from.

We add a few more clean steps for some basic prep, creating a new total field combining the amount fields from both lists, then removing the three columns we no longer need to tidy the dataset up. We name the steps so others can follow the flow, and add descriptions where the name alone isn’t enough. With a clean, well-documented flow, we’re ready to export. There are several output options: publish to Tableau Server, write to a database, or for static files simply export to CSV.

All the way through, Prep focuses on always being visual, giving you a clear picture of your data at every step. It builds in smart tools like fuzzy matching to make grouping a one-click operation, while keeping a familiar Tableau interface. New data sources are continuously added, and you can share your prep data sources to Tableau Server as files or, from 2020.3, write directly to databases to stay in the flow of your analysis. With the Data Management add-on and Tableau Prep Conductor, you can schedule flows to run in the background on Tableau Server or Tableau Online, with the same oversight, security and governance you’re used to.

Closing thoughts and resources

As a consultant who’s worked in business intelligence for the last seven years, I genuinely enjoy using Tableau Prep. It’s new and needs some polishing around the edges, but it changes with features and patches nearly every month and takes a truly innovative approach to common data challenges. You can also expect the same vibrant, helpful community you’ve come to know in the Tableau and Salesforce communities. If you’re getting started, check out Tableau’s getting started videos, my own playlist on this channel for monthly feature updates, and the Tableau forums if something is vexing you.