Tableau Prep Use Cases

Tips & Tricks

The CASE statement or CASE function in Tableau is a part of logical functions. It is similar to the IF statement in a way that it goes on checking the condition one by one. However, the difference between the two lies in the fact that a CASE statement considers conditions as cases and hence the name. Use Tableau Prep Builder to combine, shape, and clean your data for analysis in Tableau. Note: Tableau Prep version 2019.1.2 had changed its name to Tableau Prep Builder and refers to the Desktop application. Starting in version 2020.4.1, you can now create and edit flows on the web. Tableau Prep on the web refers to creating or editing flows.

Have you ever needed to do a little more in the way of data prep than what’s provided in the Tableau Data Source tab? If you are not a SQL expert, how do you do the prep required to make your data Tableau friendly? In the past, have you resorted to dumping data to a CSV file or Excel and using Excel for “cleaning” before bringing data into Tableau? What happens if you have multiple sources? Sure, some things can be done in Tableau Desktop using features such as cross-database joins and pivots, but it can be hard to generate repeatable steps that can be used to transform your data. And manual steps can be difficult to document and share with others. If these scenarios are painfully familiar to you, then Tableau Prep may be just what the (Tableau) doctor ordered.

Tableau Prep was announced under the name Project Maestro at the 2017 Tableau Conference and launched in April 2018. Under the new April 2018 licensing model, it comes with the Tableau Creator license (see Tableau Pricing). According to Tableau, “Tableau Prep will make it possible for more people, from IT to business users, to easily prep their data with a direct and visual approach.”

I recently had the chance to take part in the beta program for Tableau Prep/Project Maestro. While maybe not as fully featured as some ETL (extract, transformation and load) tools like Informatica and Alteryx, Tableau Prep has some nice features that should make life easier for relatively simple ETL scenarios.

Here’s a list of my 10 favorite features.

Tableau Data Prep

  1. Joining disparate data sources. For quite some time, one of Tableau’s strengths has been its ability to join multiple data sources. Tableau Desktop lets you combine sources by “joining” or “blending.” Similarly, Tableau Prep lets you combine an Oracle Table, a SQL Server table and a Microsoft Excel worksheet into one data source with just a couple of clicks.

    While some data prep can be done in Tableau Desktop’s data source tab, there are limitations to what can be done. The main differences between Tableau’s new Tableau Prep tool and data prep from within Tableau Desktopare in the presentation and in the number of options available.

    One such difference is that you can connect to 70 different data sources in Tableau Desktop and in the first production release of Tableau Prep, you can connect to 28 data sources. See below:

    Within Tableau Prep, you add connections to these sources. If the connection is a database, such as SQL Server, you specify the schema, the tables and which columns you want to bring in. Once you have your sources set up, you draw a line between them and add a join.

    This functionality is similar to the way other ETL tools do joins. It should be pointed out that doing these joins is often easier said than done. When not dealing with simple data like Tableau’s Sample Superstore, joins can be tricky. This is often due to differences in the level of detail, mismatched data, etc. But if you have a relatively simple scenario, joining disparate sources can be accomplished in Tableau Prep.

    NOTE: Currently, the initial selection of schemas and tables is not quite as intuitive as with some data sources such as Oracle. As with Tableau Desktop, I expect every subsequent release will see improvement in this area.

  2. Join and union results. With Tableau Desktop you can join data and union data. When doing a join or union in Tableau Desktop, the bottom of the Data Source tab will show your result. But often you want to see the results of what joined (given your join condition) AND what didn’t. In Tableau Prep, you get a Summary of Join Results that shows unmatched values. This feature can be useful for evaluating/debugging join conditions and for validating data.

    See the bottom right corner for an example of Summary Join Results.

  1. Preview in Tableau Desktop. After you have done a “step” to transform your data, you can use Preview in Tableau Desktop to look at the data in Tableau Desktop. This feature provides a quick way to validate the data produced by the step.
  2. Aggregate data. Tableau Desktop offers multiple ways to aggregate, or summarize, data. For example, you can create a Tableau extract and select Aggregate data for visible dimensions, or you can use sets or groups to aggregate data and summarizing into something like Total Sales by Region. But, if you just want a quick aggregation of your data to store in a data source or Tableau data extract (TDE), you can do this easily with Tableau Prep. You can add a step to aggregate and simply drag and drop your grouped fields and your aggregated fields. In the example below Discount, Profit, Quantity and Sales is grouped by Year of Sales, Region and City.
  3. Wildcard union. Sometimes you want to combine similar files into one data source (i.e. monthly sales files). In Tableau Desktop you can do a Union to accomplish this. Tableau Prep takes this to the next level by providing Wildcard Unions. With Wildcard Unions you specify a file path or directory and union all files that are in that location (irrelevant files can be excluded). You can then merge these files into one. The resulting merged file contains a File Paths column that contains the file path of the original source. When doing unions, you can also merge similar fields that have different names. See below for an example of a Wildcard Union:
  4. Pivot for database tables. In Tableau Desktop, you can pivot data in Excel or CSV files. If you want to re-structure, or pivot, data stored in a database such as SQL Server or Oracle, you have to create a new table or use custom SQL. In Tableau Prep, “pivot” appears to be a valid option for database tables.

    Below are examples of pivots using SQL Server and Oracle tables as data sources:

  5. Edit and clean data.This is big and has lots of use cases. Often when you bring data into Tableau, data integrity issues become apparent. Wouldn’t it be nice to quickly clean your data bringing it into Tableau? Let’s say most of your states were put in using a two-character capital abbreviation like AZ, but some states were fully spelled out. Ideally, you would fix data integrity issues at the source. But sometimes you just need a quick fix. With Tableau Prep you can now clean your data and edit values. See below for an example of edit values:
    In Tableau Prep, you can create one step that does multiple “cleaning” functions like filter, change data types, rename and remove fields. See below for a “Fix Dates” step that performs multiple functions:
  1. Group and replace. Let’s say you have the following values “USA,” “United States” and “U.S.A.” You know these are all USA and should be grouped together. Currently, you can group these in Tableau Desktop or fix your data at the source. With Tableau Prep you also have an option to Group andReplace, saving your new grouping as part of your data source.

    See below for an example (notice you even can do it by Pronunciation!):

  1. Data profile. In Tableau Desktop’s Data Source pane, row level data is displayed at the bottom of the screen, but you can’t see how the data is distributed. With Tableau Prep, the screen is divided into three panes: the top pane has a data flow or a graphical representation of the work flow, the middle pane has a data summary or profile and the bottom pane displays the row level data. The profile section in the middle has histograms to depict the frequency of values within columns, making it very easy to see how data is distributed.

    See below:

  1. Graphical depiction of steps taken to transform data. As you can see above, steps taken to transform the data are graphically depicted and put into one self-documenting flow. You can also click on any of the steps to see what the data looks like at any given stage of transformation. This is what more complex ETL tools have been doing for years and it is really useful. These steps can then be shared and run as a “flow,” which can be published as a Hyper, TDE or saved to a file.
CONCLUSION

Tableau Prep has some really good, time saving features that will allow you produce Tableau friendly data. It will be a good alternative to manually scrubbing data, using steps that often are not documented or repeatable. For simple transformation logic, Tableau Prep should do everything that is required. Even though Tableau Data Prep is good at creating Tableau Data Extracts and text-based files, often it can be better to build a database repository or data warehouse that can be leveraged by multiple reporting tools. For this type of work there are other ETL (extraction, transformation and load) tools that might better suit your needs. Tools such as Alteryx and Informatica have more data output options (i.e. database tables) and more capabilities when it comes to predictive modeling, statistical analysis, geospatial manipulation, mapping and valuable built-in demographic data for enhancing a dataset. At Senturus, we believe there is no one size fits all tool for data preparation. There is a 'right tool for the job' and we can help you determine what tool might best fit your needs.

It is a well known fact that data preparation is often 80% of the work when building out business analytics frameworks. For more complex data work, expert advice is often needed to make sense of the underlying data sources so they can be joined into a cohesive, well-designed data model that can be used by multiple reporting tools. At Senturus, we have been doing just that for nearly two decades. We make sense of what is complex by designing and building intuitive data structures that can be easily leveraged by tools such as Tableau.

This blog was submitted by our own Monica Van Loon. A frequent contributor to our blog, Monica is a Tableau certified consultant and teaches many of our Tableau workshops.

Senturus is a nationwide business analytics consulting firm and a Tableau partner. We were in no part solicited or paid for this review. The views and opinions expressed in this article are those of the author and do not necessarily reflect those of any other related party.

Data Prep

Related Pages

Introduction:

Tableau is a rapid-fire business intelligence software that allows you to make decisions at a very fast pace. Tableau is also a leading new generation Business Intelligence (BI) application which is also dubbed as a “self-service” data discovery tool as it can be achieved with almost no support from IT staff. This product shines as a graphical tool or a visualization tool, where you would have relied on various other tools and takes longer durations to represent the bar charts or the pie charts. Tableau is built to perform and it provides very quick visual analytics.

Tableau provides you with a wonderful feature of performing logical checks and based on the outcome to do something.

Tableau

Want to become a Master in Tableau Course? Then visit to learn Tableau Certification Training at Mindmajix

Overview of Case Statements in Tableau:

Usage of the CASE statement from programming languages as like C to C++ or even to Java doesn’t differ much. To that point, the CASE statements can be used on RDBMS servers like Oracle, SQL Server and the like. The feature is no different in Tableau Desktop as well, it enables an individual to make logical decision making and then based on the outcome of the case, a specific action can be written to be performed. In plain English, CASE scenario1 THEN perform action1. To be precise, CASE is simpler to use than a complicated and nested IF and ELSE statements.

[Related Article: What Are The Common Use Cases For Tabcmd In Tableau?]

Tableau

CASE function evaluates the expression and matches the outcome to the values presented as scenarios and then returns the matched value. If there is no match found, then the default return expression will be used. Typically, a CASE statement is used to search for a match to an expression, which is always concise than an implementation using IF and ELSE statements. Now with this basic introduction into the concept of CASE statements, let us take some general examples and see how this can be put to use in Tableau Desktop to achieve what is expected out of the software.

Let us consider a few examples to understand the concepts that we have discussed until now:

CASE [ Runs ] WHEN ‘Six’ THEN 6

WHEN ‘Four’ THEN 4

WHEN ‘Triple’ THEN 3

Tableau Prep Use Cases

WHEN ‘Double’ THEN 2

WHEN ‘Single’ THEN 1

Let us take a look at another example to see the usage of the CASE statement:

CASE [Direction] WHEN ‘EAST’ THEN 1

WHEN ‘WEST’ THEN 2

WHEN ‘NORTH’ THEN 3

WHEN ‘SOUTH’ THEN 4

If you want to create CASE statements that could be used in the evaluation of Parameters or Dashboards or Reports, you can create and save them on Tableau Desktop. Let us see how we can create them on Tableau Desktop in the steps provided below:

Tableau Prep Use Cases Example

  • On the Analysis tab, click on the option ‘Create Calculated Field’

  • This opens up a new tabular pane for recording your calculation formula

Tableau

CASE [Region] WHEN 'Shanghai' THEN 'APAC'

WHEN 'Hong Kong' THEN 'APAC'

WHEN 'London' THEN 'EMEA'

WHEN 'Brazil' THEN 'SA'

WHEN 'Texas' THEN 'NA'

END

A sample screenshot on how this can be saved on Tableau Desktop is shown below:

Once the formula as like that has been shown above is entered, click Ok and save it for further usage.

[Related Article: How To Configure Tableau Server For The First Time?]

Conclusion:

In this article, we have tried to give you a brief about what Tableau as software can provide you or your organization with. Alongside those details, we have provided necessary information about CASE statements and also have seen the performance gain that we get over the usage of IF and ELSE statements.

Hope this article is informative enough to provide all the necessary details at one place for your quicker references.

Related Pages:

List Of Tableau Courses:

Tableau Prep Vs Tableau Desktop

Tableau Advanced
Tableau Server
Data Visualization and Dashboarding Fundamentals