Readers:

In Part 2 of this series on Improving Performance with Data Extracts, we will connect to the SQL Server AdventureWorks database, hide unused data source fields, and aggregate data for the visible dimensions.

If you have not read Part 1 of this series, you can do so by clicking here.

Again, I hope you enjoy this series about data extracts in Tableau.

Best regards,

Michael

Creating a Tableau Data Extract

Much of the content, datasets,  and Tableau Workbooks I am using for this blog post series comes from Tim Messar’s video book, Mastering Tableau 10. I cite Tim as the primary source for this blog post in the “sources” section at the end of this post (see video book cover, right).

Now that we have a better understanding of what a Tableau data extract is, let’s create one.

I will be using the SQL Server AdventureWorks database to demonstrate creating Tableau data extracts. Also, I am using Tableau Desktop v10.4 to create the extract and associated screenshots for this example.

Step 1: Open Tableau desktop and connect to Microsoft SQL Server. I select the AdventureWorks database.

Step 2: I drag in the SalesOrderHeader table and the SalesOrderDetail table. I will use an Inner Join on the SalesOrderId. The Inner Join selects all rows from both participating tables as long as there is a match between the SalesOrderId.

Step 3: Open a new Worksheet (you will see the default Sheet1 highlighted with the tooltip Go to Worksheet at the bottom left of the page (see screenshot below).

Step 4: Right-click on the SalesOrderHeader data source that we want to create an extract for, then select Extract Data…

Step 5: For this step, we will just accept the defaults and press the Extract button. This will create the Tableau data extract file for us using the name of the data source and appending a .TDE (Tableau Data Extract) extension on the filename.

We will discuss these settings in more detail in Part 3 of this series.

Typically, your extract data files will be created in the following directory: C:\Users\username\Documents\My Tableau Repository\Datasources\10.4\en_US-US. In my example, I deliberately am writing the extracts to a specific separate folder for this example.

TIP

Notice in the screenshot below that the icon has changed to an Extract icon.

Step 6: Let’s take a look at the .TDE file we created in my sub-folder. Notice it is 13,238 KB (kilobytes or 13.24 MB) in size. Let’s make a copy of the original extract (remember we used the default settings) and rename it to “AS IS.”

Step 7: Now, let’s remove the original extract file so we can recreate it.

Step 8: Now, I am going to use our extract to create a basic report. NOTE: This report is created to illustrate the next step in our process and is not a meaningful report example.

Step 9: Now, I want to remove all fields I am not using on the report. This will help us make the extract smaller when we recreate it in the next step. Click on the filled in down arrow (∇) in the Dimensions section and select Hide All Unused Fields. This will hide all fields not included in my report.

Step 10: Notice all unused fields are now hidden (see screenshot below). Now, let’s right-click on the extract, select Extract Data…, and then select Aggregate data for visible dimensions check box. This will create a new .TDE file which includes only the visible dimensions.

Step 11: Let’s take a look at the new .TDE file we created, without the hidden fields, in my sub-folder (see screenshot below). Notice it is 1,732 KB (kilobytes or 1.73 MB) in size. The smaller the size of the extract file, the better our performance will be.

Let’s make a copy of this extract file and rename it to “Hide Fields – Aggregation.”

[1] I relied heavily on the fantastic Tableau video book written by Tim Messar titled Mastering Tableau 10 (see cover image below). Click here to purchase your own copy of his book.

[2] Tableau Software, Extract Your Data, Tableau Software, Online Help, All Tableau Help > Tableau Help > Connect to and Prepare Data > Set Up Data Sources > Extract Your Data, http://onlinehelp.tableau.com/current/pro/desktop/en-us/extracting_data.html.

 

[3] Sandberg, Michael, Tableau Boot Camp Additional Reference Material, City of Glendale, Arizona, September 28th, 2017.