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.