Tableau Cookbook - Recipes for Data Visualization

Before we get started, we need to make sure that Tableau Desktop Professional Edition Version 10.1 is downloaded and installed on our machines.

If you are an existing licensed user of Tableau Desktop, then make sure that you are using the latest version. If the version is anything below 10.1, then it is recommended that you upgrade it to the latest version, as there are significant changes in terms of the User Interface as well as features. Nonetheless, even if we decide to continue with the earlier versions, the majority of the fundamental concepts and features outlined in this book would remain the same, although their implementation may differ.

If you do not have a license, the 14-day full-feature trial of Tableau Desktop Professional Version 10.1 can be installed and downloaded from Tableau's website (http://www.tableau.com/).

Tableau, being a plug and play software, simply needs to be downloaded and installed just like any .exe or .dmg installable.

The Start Page of Tableau is divided into three parts, namely Connect, Open, and Discover; refer to the numbers 1, 2, and 3 respectively in the preceding screenshot.

The top-left corner, which says Connect (number 1), consists of three sections called To a File, To a Server, and Saved Data Sources. In each of these sections, we will see the list of data sources that Tableau can connect to.

The To a File section consists of flat file data sources such as Excel files, Access files, Text files, JSON files, and statistical files, including SAS, SPSS, and R. Clicking on the More… option under this section allows us to even connect to Tableau's data extract files, which we will understand in detail in the later chapters.

The To a Server section consists of data sources such as Microsoft SQL Server, MySQL, Oracle, and so on. To see a more detailed list of data sources that Tableau can connect to, we need to click on the More… option and expand the section by clicking on the arrow. Refer to the following image that illustrates this:

Just below the To a Server section, we will see the Saved Data Sources section. This section essentially points to those data sources that have been previously worked on and then saved for later purposes. Currently, we will not use this option and we will start by connecting to the raw data and not use any of the existing saved data sources.

Adjacent to the Connect header is an empty section that is covered under the header Open (number 2). This is the section where we will see the thumbnails of all our recently opened workbooks. This section is blank to begin with; however, as we create and save new workbooks, this section will display the thumbnails of the nine most recently opened workbooks.

This section also gives us access to some sample workbooks that are provided by Tableau for our reference. We can open these workbooks in Tableau Desktop to see how certain functionalities are used or how a particular visualization is created in Tableau.

The next section is the Discover section (number 3). This section basically provides links to resources that are available on the Tableau website. Apart from the training videos, this section also shows the blogs, forums, latest news about Tableau, as well as the views that are selected as the Viz of the week on Tableau Public.

Tableau provides some sample datasets with the Desktop edition. In this book, we will frequently use the sample datasets that have been provided by Tableau. We can find these datasets in the Datasources folder within the My Tableau Repository folder, which gets created in the Documents folder when Tableau Desktop is installed on the machine. We can look for these data sources in the repository or quickly download them from https://1drv.ms/f/s!Av5QCoyLTBpnhj06IKTNX0S9hK48. Once you do this, you save them in a new folder called Tableau Cookbook data that you'll find by navigating to Documents\My Tableau Repository\Datasources.

There are three files that have been uploaded, and these are the ones that we will primarily use throughout the book. They are as follows:

In the following section, we will see how to connect to the sample data source. We will be connecting to the Excel data called Sample - Superstore.xls.

This Excel file contains transactional data for a retail store. There are three worksheets in this Excel workbook. The first sheet, called the Orders sheet, contains the transaction details, The Returns sheet contains the status of returned orders. And the People sheet contains the region names and the names of the managers associated with those regions. Refer to the following image to get a glimpse of how the Excel data is structured:

Now that we have looked at the Excel data, let's see how to connect to this data in the following recipe. To begin with, we will work on the Orders sheet of the Sample - Superstore.xls data. This worksheet contains the order details in terms of the products purchased, the name of the customer, sales, profits, discounts offered, day of purchase, and the order shipment date, among many other transactional details.

Before we connect to any data, we need to make sure that our data is clean and in the right format. The Excel file that we connected to was stored in a tabular format where the first row of the sheet contained all the column headers and every other row is basically a single transaction in the data. This is the ideal data structure for making the best use of Tableau. Typically, when we connect to databases, we get a columnar/tabular type of data. However, flat files, such as Excel, can have data even in cross-tab formats. Although Tableau can read cross-tab data, we may end up facing some limitations in terms of creating certain chart types, thereby aggregating and slicing and dicing our data in Tableau.

Having said that, there may be situations where we have to deal with such cross-tab or preformatted Excel files. These files will essentially need cleaning up before being pulled into Tableau. Refer to http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis to understand more about how we can clean up these files and make them Tableau-ready. Refer to the following article to quickly understand how we can quickly pivot the data in Excel. http://kb.tableau.com/articles/knowledgebase/addin-reshaping-data-excel.

If it is a cross-tab file, then we will have to pivot it into normalized columns either at the data level or at the Tableau level on the fly. We can do so by selecting multiple columns that we wish to pivot and then selecting the Pivot option from the drop-down menu that appears when we hover over any of the columns. Refer to the following image:

Further, if the format of the data in our Excel file is not suitable for analysis in Tableau, then we can turn on the Data Interpreter option, which becomes available when Tableau detects any unique formatting or any extra information in our Excel file. For example, the Excel data may include some empty rows and columns or extra headers and footers. Refer to the following image:

Data Interpreter can remove that extra bit of information to help prepare our Tableau data source for analysis. Refer to the following image:

When we enable Data Interpreter, the preceding view will change to what is shown in the following image:

This is how Data Interpreter works in Tableau.

Now, many a times, there may also be situations where our data fields are compounded or clubbed in a single column. Refer to the following image:

In the preceding image, the highlighted column is basically a concatenated field that has the Country, City, and State fields. For our analysis, we may want to break these and analyze each geographic level separately. To do so, we simply need to use the Split or Custom Split option in Tableau. Refer to the following image:

Once we do this, our view would be as shown in the following image:

Further, when preparing some data for analysis, at times a list of fields may be easy to consume as against the current preview of our data. The Metadata grid in Tableau allows us to do the same along with many other quick functions such as renaming fields, hiding columns, changing data types, changing aliases, creating calculations, splitting fields, merging fields, and pivoting the data. Refer to the following image:

After having established the initial connectivity by pointing to the right data source, we need to specify how we wish to maintain that connectivity. We can choose between the Live option and the Extract option.

The Live option helps us connect to our data directly and maintains a live connection with the data source. Using this option allows Tableau to leverage the capabilities of our data source, and in this case, the speed of our data source will determine the performance of our analysis.

The Extract option, on the other hand, helps us import the entire data source into Tableau's fast data engine as an extract. This option basically creates a .tde file, which stands for Tableau Data Extract. In case you wish to extract only a subset of your data, then you can select the Edit option, as highlighted in following image. The Add link in the right-hand side corner helps us add filters while fetching the data into Tableau:

One point to remember about Extract is that it is a snapshot of our data stored in a Tableau proprietary format, and as opposed to a Live connection, the changes in the original data won't be reflected in the dashboard unless and until the Extract is updated.

We will continue from where we left in the previous recipe where we are still connected live to the Orders sheets of Sample - Superstore.xls. Let's quickly build our first view and then understand the workspace of Tableau.

After having established the connection to the data and having read the data in Tableau by clicking on the Go to Worksheet option and then the Sheet 1 tab, we get the view where we will create our visualizations. Refer to the following image:

The preceding image gives us a quick glimpse into the Tableau workspace. Let's understand this workspace in more detail:

The Data pane, as the name suggests, shows the data to begin with, which is populated from the data source that we have connected to. The top section of the Data pane displays the data source that we have connected to. In the case of multiple data sources, we will see a list of data sources.

Further, just below the data source section are the fields available in that data source. Refer to the following image:

The Data pane is primarily divided into two sections: Dimensions and Measures.

Dimensions are all the descriptive fields in our data. These are fields that hold categorical/discrete qualitative data. In short, any field that contains text or date values will be identified as Dimensions, for example, customer name, purchase date, customer state, and so on.

On the other hand, Measures are all the fields that contain numeric values. These are fields that hold quantitative data, for example, sales, profit, budget, and so on. Typically, Measures are the fields that will be aggregated while analyzing. For example, we may want to see the sum of sales across regions, the average sales for a particular category, and so on.

Basically, Dimensions and Measures are Tableau's way of classifying our data fields. At a little later point in time, you will understand how Tableau is capable of doing this classification, and in case we wish to change it, then how to go about it.

Further, the Data pane may additionally accommodate sections for Sets and Parameters, functionalities that we will explore later.

The Analytics pane, on the other hand, provides quick and easy access to features such as reference lines, box plots, trend lines, forecasts, and so on. We will explore these options at a later stage. To begin with, the Analytics pane may be disabled/grayed out. However, once we start creating our visualizations, then depending on the visualization, some of the options from this pane will be enabled/disabled for us. Refer to the following image:

We shall explore these various shelves in more detail as we progress with our understanding of Tableau.

In the earlier section, we read about Dimensions and Measures. As a quick recap, Dimensions are basically fields that contain categorical/descriptive/qualitative data, such as text and dates, whereas, Measures are fields that contain numeric/quantitative data that can be aggregated.

Dimensions and Measures are the fundamental building blocks of our analysis, and it is very important that they are identified correctly. As mentioned earlier, Tableau is capable of classifying our data fields as either a Dimension or a Measure, and it does so based on the data type of the fields.

So, what data types are supported by Tableau and how does Tableau identify them? When we connect to a database, the data types are already defined in the database and that is what Tableau would read. However, when we connect to typical flat file formats, such as Excel, Access, or CSVs, Tableau will read the data types of the first 10,000 rows of an Excel data source or the first 1,024 rows of a text file data source. Once it does this, it will accordingly decide the data type of that field. So, in an Excel file, if most of the first 10,000 rows are numeric values, the entire column is mapped as Integer.

Each field under the Dimensions and Measures section will have a prefixed icon before it. These are the data types supported by Tableau and are identified in the Data pane by one of the following icons:

Is it possible to change the datatype of the fields in Tableau? Yes! Ideally, it is recommended that we handle all the data-related changes at the data source level. However, if we feel that Tableau has incorrectly identified the data type of a certain field, then we can right-click on the field in the Data pane, select Change Data Type, and then select the appropriate data type. Refer to the following image:

Once Tableau identifies the data type, it simply classifies all String and Date / Date & Time data types as Dimensions and all Integer data types as Measures.

Why is Row ID a Dimension even though its datatype is Integer? Even though Row ID is a numeric field in this case, taking a sum or average of Row ID won't be useful. It is actually a descriptive field that gives us information about the transaction. Since the header/field name contains the word ID, Tableau assumes that it is an identifier and hence places it in the Dimensions shelf.

Is it possible to convert a Dimension to a Measure and vice versa? Yes! It is easily possible to convert Dimension to Measure and vice versa. To do so, we can simply right-click on the Dimension field and select the option of Convert to Measure. If we right-click on the Measure field, then we get an option of Convert to Dimension. Refer to the following image:

We can also drag Dimension fields and then drop them into the Measures pane and vice versa to convert them from Dimensions to Measures. The same method can be used to convert Measures to Dimensions as well.

When we connect to the data, apart from reading the data fields and classifying them as Dimensions or Measures, Tableau also autogenerates certain new fields in both the Dimensions and Measures pane. They are as follows:

In the coming chapters, we will learn how to create various charts in Tableau. However, before we go there, let's understand how we can save the file that we are currently working on: