If you’ve been using Tableau Desktop for a while you probably know that you can join, union and pivot data in the product. When you hear that Tableau Prep helps you “prepare your data” you might wonder what it can do that Desktop can’t. We’ll look at three scenarios in this post.
● Pre-Aggregate Data for Analysis
● Perform Multiple Pivots
● Union Tables from Different Data Sources
Pre-Aggregate Data for Analysis
Years ago I was working with a restaurant chain that wanted to create some Same Store Sales reports. They wanted to be able to answer questions like, “If today is July 1st, how did we perform and how did that compare to the comparable day last year?” Let’s say they had 200 locations, 365 days worth of data, and 1500 transactions a day. If you want to keep 2 years worth of data you’re looking at over 200 million rows of data. Yikes. At a high level, we could do this analysis with just one row of data per store per day.
Tableau Prep can take a detailed table (this sample is ~30,000 rows)...
and aggregate it to significantly reduce the number of rows of data.
Perform Multiple Pivots
Imagine you have multiple tables of data and want to join them together. However, you’ve run into a roadblock. They aren’t formatted to be able to be joined right now and both need to be pivoted. Tableau Desktop can’t help with this but Tableau Prep can.
Here’s the first file, enrollment by year and state:
The other is revenue by state and year:
You can use Tableau Prep to pivot them both independently and join them to get a single table.
Union Tables from Different Data Sources
A couple years ago I worked with a business services company that manages printers for their clients. They have one database that tracks information on printers the client leases from them. Another database tracks information on printers owned by the client. They wanted to get the data into a single place to be able to manage the whole fleet at once and make system-wide recommendations.
The tables had similar structures but lived in different databases. Tableau Desktop can only union two tables when they come from the same database. Tableau Prep doesn’t have that limitation.
Here’s the table of data for leased printers:
Here’s the table of data for client-owned devices:
By doing some manipulation and then applying a union the two tables can be combined into one:
Want to see these scenarios in action? Check out this webinar recording.
Want to master Tableau Prep? Sign up to view a recording of our workshop Tableau Prep Foundations and spend an hour working with us on your own data.