Connect to Data

Connect to Data
Basic Connection
Supported Data Sources
How to Connect to a Data Source
Examples – Connecting to Data Sources
Microsoft Access Database
Microsoft Excel Workbook
OData
Text File
Connecting to a Custom SQL Query
Editing the Connection
Editing a Connection
Replacing Field References
Renaming the Connection
Duplicating the Connection
Refreshing the Data
Closing the Connection
Clipboard Data Sources
Working with Multiple Connections
Understanding Data Blending
Navigating Connections in the Data Window
Adding a Secondary Connection
Defining Relationships
Example – Data Blending on a Worksheet
Troubleshooting Data Blending
Warning: Cannot Blend Because No Linking Fields
Asterisks Show in the View
Joining Tables
Adding Tables
Connecting to Multiple Tables
Adding Tables to the Data window
Editing Tables
Managing Queries
Automatic Updates
Cancel Query
Abandoned Queries
Precision Warnings
Understanding Data Fields
Understanding the Data Window
Relational and Multidimensional Data
Hierarchies (For Relational Databases)
Creating a Hierarchy
Removing a Hierarchy
Measure Values and Measure Names
Number of Records
Latitude and Longitude (generated)
Data Window Features and Functions
Organize the Data Window
Find Fields
Rename Fields
Renaming a Field
Reverting to the Default Field Name
Hide or Unhide Fields
Add Fields to the Data Window
Editing Field Properties
Comments
Adding a Comment to a Field
Aliases
Aliases with a Relational Data Source
Aliases with a Multidimensional Data Source
Example – Editing Aliases
To edit the aliases:
Colors
Shapes
Formats
Sort
Aggregation
To specify a default aggregation:
Measure Names
Data Types and Roles
Data Types
Mixed Data Types for Excel and CSV Files
Data Roles
Data Roles; Dimension vs. Measure
Converting Measures to Dimensions
Data roles: Continuous vs. Discrete
Converting Discrete to Continuous Quantities

Connect to Data
This section explains how to create and manage both basic and advanced connections to all of the supported data sources. Follow a step-by-step tutorial for connecting to each data source. Also, learn how to paste data into Tableau, join new tables, create and manage extracts, and monitor queries.
• Basic Connection
• Clipboard Data Sources
• Working with Multiple Connections
• Joining Tables
• Managing Queries
• Understanding Data Fields
Basic Connection
To begin analyzing your data, first connect Tableau to one or more data sources. After connecting, the data fields become available in the Data window on the left side of the workbook. This section describes how to create and maintain a basic connection.
• Supported Data Sources
• How to Connect to a Data Source
• Examples – Connecting to Data Sources
• Connecting to a Custom SQL Query
• Editing the Connection
• Renaming the Connection
• Duplicating the Connection
• Refreshing the Data
• Closing the Connection
Supported Data Sources
Tableau supports a wide variety of data sources, including Microsoft Office files, SQL databases, comma delimited text files, and multi-dimensional databases.
The data sources supported by your copy of Tableau are determined by the version purchased. Refer to the Productspage of the Tableau Web site for more information on data source compatibility requirements
How to Connect to a Data Source
To build views of your data, you must first connect Tableau to a data source.
You can connect to any supported data source with the Connect to Data dialog box.
1. Select Data > Connect to Data or press Ctrl + D on your keyboard. You can also select the Open Dataoption on the start page.

2. In the Connect to Data dialog box, select the type of data you want to connect to.

3. A data source-specific dialog box opens that allows you to complete the connection process.
After the connection is established, the data source fields display on the left side of the workbook in the Data window.

You can display information about the connection by selecting Data > Data Connection > Properties. The properties of an example data source are shown below.
Examples – Connecting to Data Sources
This section contains examples that show you how to connect to the following specific data sources.
• Microsoft Access Database
• Microsoft Excel Workbook
• OData
• Text File
Microsoft Access Database
This example discusses how to connect Tableau to a Microsoft Access database. Tableau supports all Access data types except OLE Object and Hyperlink.
1. Select Data > Connect to Data to open the dialog box.
2. Select Microsoft Access.
3. Follow the steps in the Microsoft Access Connection dialog box to complete the connection.
a. Step 1 – Select a Microsoft Access database file by typing its name or by navigating to the file. Access database a have the .mdb file extension.
Select Use workgroup security when connecting when you are connecting to a password protected Access file or a file that is protected by workgroup security. When you select this option, a logon dialog box opens where you can enter your password or select Use Workgroup Security. If the file is protected by workgroup security, type the System Database, User, and Password into the corresponding text fields.
b. Step 2 – Select the table or query to analyze.
You can connect to a Single Table or query. Alternatively, you can connect to a set of relational tables that are related by join conditions. Select either Multiple Tables or Custom SQL when you are connecting to multiple tables. You can also add joins later.
A completed Connection dialog box for Microsoft Access is shown below.

Note:
If the Access file contains columns that are more than 254 characters wide, Tableau will not be able to sue these fields. Either remove the columns from the table or modify them to fit within 254 characters prior to connecting with Tableau.
Microsoft Excel Workbook
This example discusses how to connect Tableau to a Microsoft Excel workbook.
1. Select Data > Connect to Data to open the dialog box.
2. Select Microsoft Excel.
3. Follow the steps in the Microsoft Excel Connection dialog box to complete the connection.
a. Step 1 – Select an Excel workbook by typing its name or by navigating to the file. Access database a have the .xls or .xlsx file extensions.
b. Step 2 – Select the data range or worksheet to analyze.
You can connect to a single worksheet or a named range. Named ranges allow you to connect to just a specific portion of an Excel worksheet. You can create a named range in Excel by highlighting a range of cells and then selecting Define Name on the Formulas tab. Then give the range of cells a name. You can now connect to this named range in Tableau in the same way you can connect to a worksheet.
You can also connect to a set of relational tables that are related by join conditions. To do so, select the Multiple Tables option. You can also add joins later.
c. Step 3 – Select whether the data includes field names in the first row.
These names will become the names of the fields in Tableau. If column names are not included, they will be automatically generated by Tableau. You can rename the fields later.
A completed Connection dialog box for Microsoft Excel is shown below.

Note:
If the Excel workbook contains columns that are more than 254 characters wide, Tableau will not be able to sue these fields. Either remove the columns from the table or modify them to fit within 254 characters prior to connecting with Tableau.
OData
This example discusses how to connect Tableau to an OData data source.
1. Select Data > Connect to Data to open the dialog box.
2. Select OData.
3. Follow the steps in the OData Connection dialog box to complete the connection.
a. Step 1 – Select or enter a URL to the data you want to connect to. Click the link to find an OData data source on Windows Azure Marketplace DataMarket.
b. Step 2 – Enter authentication information.
If necessary, enter authentication information. You can authenticate using your Windows Azure Marketplace DataMarket account key or a username and password.
c. Step 3 – Connect.
If the connection is unsuccessful, verify that the URL and authentication information are correct. If the connection continues to fail, your computer is having trouble locating the server. Contact your network administrator or database administrator.
d. Step 4– Give the connection a name for use in Tableau.
Specify a unique name for the connection. The default name is automatically generated.
A completed Connection dialog box for OData is shown below.
Text File
This example discusses how to connect Tableau to a Text File. Tableau connects to delimited text files.
Parent topic: Examples – Connecting to Data Sources
Connecting to the Data Source
1. Select Data > Connect to Data to open the dialog box.
2. Select Text File.
3. Follow the steps in the Text File Connection dialog box to complete the connection.
a. Step 1 – Select the text file by typing the file path or by clicking Browse to navigate to the file.
b. Step 2 – Specify the options for the text file.
There are three options that you can specify to define the type of text file you are connecting to.
 Select whether the first row contains column names. This option is selected by default. Alternatively you can have Tableau generate names when you connect. These name can be changed later.
 Select the character that is used to separate the columns. Columns can be separated by a Comma, Tab, Space, Colon, Vertical Bar (also known as the pipe character), or another character as defined in a schema.ini file. Refer to Using Text Files with Alternate Delimiters to learn more about using other characters.
 Select a Character Set that describes the text file encoding. You can select ANSI, OEM, UTF-8, UTF-16, or Other. When you select Other you must specify the character set in the provided text field. This value will be verified when the connection is attempted.
c. Step 3 – Select the data range to analyze.
You can connect to a single file or a set of relational tables that are related by join conditions. To do so, select the Multiple Tables option. You can also add joins later
A completed Connection dialog box for Text is shown below.

Note:
If the text file contains columns that are more than 254 characters wide, Tableau will not be able to use these fields. Either remove the columns or modify them to fit within 254 characters prior to connecting in Tableau. Large text files often perform poorly as a data source, because the queries are slow. If Tableau determines the file is too big to perform well, you will be prompted to create an extract.
Using Alternate Delimiters
You can connect to text files that are delimited by commas, tabs, spaces, colons, or vertical bars using the Text File Connection dialog box. If your text file is delimited by an alternate character you must first create a schema.ini file that defines the delimiter. Then you can use the Other option when connecting in Tableau.
1. Create anew text file (using Notepad or another text editor) and type the following:[Your_Datasource.txt] Format=Delimited(delimiter character)
Substitute the name of your data file for “Your_Datasource.txt” and the character used to separate the columns for “delimited character.” For example, if you use the asterisk character as a delimiter in a file called Sales-Data.txt you would type the following into the schemi.ini file:
For example, if you use the asterisk character as a delimiter in a file called Sales-Data.txt you would type the following into the schemi.ini file:
[Sales-Data.txt]
Format=Delimited(*)
2. Save the file in the same directory as the data file and call it schema.ini
3. Return to Tableau and connect to the data file using the Text file instructions outlined above. Select Otheras the Field Separator.
Connecting to a Custom SQL Query
For most relational data sources you can connect to a specific query rather then the entire data source. Often this can be useful when you know exactly the information you need and you understand how to write SQL queries.
1. Select Custom SQL in the connection dialog box.
2. Type or paste the query into the text box. The button in the upper right corner of the text box opens a larger editing window for more complex queries.

When you finish the connection, only the relevant fields display in the Tableau Data window.
If your SQL query references duplicate columns, you may get errors when trying to use one of the columns in Tableau. This will happen even if the query is valid. For example, consider the following query:
SELECT * from authors, titleauthor where authors.au_id = titleauthor.au_id
The query is valid, but the au_id field is ambiguous because it exists in both the “authors” table and the “titleauthor” table. Tableau will connect to the query but you will get an error anytime you try to use the au_id field. That’s because Tableau doesn’t know which table you are referring to.
Editing the Connection
When a Tableau workbook is connected to a data source, you can edit the connection. You might want to edit the data source connection to:
• Specify a new location for the data source. Suppose the name or location of a data source you were using has changed and is no longer available using the previous connection information. In this case, you can direct the workbook to the correct location without losing your work.
• Apply analyses created using one data source to another data source.
Suppose you create a workbook containing an several views involving markets, products, sales, and profits and you want to apply the analyses to a new data source. Instead of recreating each view from scratch, you can edit the original data connection and specify a new data source.
• Editing a Connection
• Replacing Field References
Editing a Connection
1. Select Data > Data Connection > Edit.
2. Complete the data source-specific connection dialog box. For example, the Microsoft Access Connection dialog box is shown below. You can specify a new file, or you can select a different table to analyze.

Replacing Field References
When you successfully connect to a new data source, all worksheets that previously referred to the original data source now refer to the new data source. If the new data source does not have the same field names that are used in the original workbook, the fields become invalid and are marked with an exclamation mark . You can quickly resolve the problem by replacing the field’s references.
For example, say you have a workbook connected to a data source that contains a Product Category field. Then you edit the connection to point to a new data source that has all the same data but instead of Product Category, the field name has been changed to Product Type. The Product Category field remains in the Data window but is marked as invalid. To make the field valid, you can replace the references, which means you can map the invalid field to a valid field in new data source (e.g., Product Category corresponds to Product Type).
1. Right-click the invalid field in the Data window and select Replace References.
2. In the Replace References dialog box, select a field from the new data source that corresponds to the invalid field.
Renaming the Connection
When you connect to a data source you are given the option to give it a name for use in Tableau. You can change the name you specified by selecting Data > Data Connection > Rename. Naming a connection is useful when you have a single workbook connected to many data sources. The name you assign can help you keep track of the specifics of the connection. You can also review the connection properties by selecting Data > Data Connection > Properties.
Duplicating the Connection
Sometimes you’ll want to make changes to a data connection such as add more tables, hide and show fields, set field defaults, and so on. When you make these changes it affects all sheets that use the data connection. You can duplicate a connection so that you can make the changes without affecting the existing sheets. To duplicate a connection selectData > Data Connection > Duplicate. When you duplicate a connection the name has “(copy)” appended to the end.
Refreshing the Data
If you make modifications to a data source such as adding new fields or rows, changing data values or field names, or deleting data, Tableau will reflect those changes the next time you connect to the data source. However, because Tableau queries a data source and does not import the data, you can immediately update Tableau to reflect the data source modifications without disconnecting.
If you are connected to a data source that has been modified, you can immediately update Tableau with the changes by selecting Data > Refresh.
If you remove a field from a data source that is used in a Tableau worksheet, and then refresh the data source, a warning message displays indicating that the field will be removed from the view and the worksheet will not display correctly because of the missing field
Closing the Connection
You can close a data connection at any time. Doing so does not modify the data source. Instead, it disconnects Tableau from the data so that you can no longer query it. Additionally, the connection is cleared from the Data window and all open worksheets associated with the data are cleared. If you accidentally close a connection, use the Undo button to reconnect.
1. Select the data source at the top of the Data window.

2. Select Data > Data Connection > Close.

Clipboard Data Sources
Sometimes you want to pull in data from an outside source for some quick analysis. Rather than create a whole data source and then connect in Tableau, you can copy and paste the data directly into the application. Tableau automatically creates a data source that you can begin analyzing.
You can copy and paste data from a variety of office applications including Microsoft Excel and Word. You can also copy and paste html tables from webpages. Tables that are copied as comma separated values or tab delimited can be pasted into Tableau. Please be aware that not all applications use these formats when copying.
1. Select the data you want and copy it to the clipboard.

2. Open Tableau Desktop and select Edit > Paste Data.

Working with Multiple Connections
A workbook can contain multiple connections to multiple data sources. Each connection is listed at the top of the Data window. Each worksheet has a primary connection and can optinally have several secondary connections using data blending. The primary connection and the secondary connections are linked by specified relationships. Adding a secondary connection to a sheet can be useful when you have data in multiple data sources that you want to integrate into a single analysis.
• Understanding Data Blending
• Navigating Connections in the Data Window
• Adding a Secondary Connection
• Defining Relationships
• Example – Data Blending on a Worksheet
• Troubleshooting Data Blending
Understanding Data Blending
Data blending is when you blend data from multiple data sources on a single worksheet. The data is joined on common dimensions. Data Blending does not create row level joins and is not a way to add new dimensions or rows to your data. Refer to Joining Tables to learn how to create those types of joins. Instead, data blending should be used when you have related data in multiple data sources that you want to analyze together in a single view. For example, you may have Sales data collected in an Oracle database and Sales Goal data in an Excel spreadsheet. To compare actual sales to target sales, you can blend the data based on common dimensions to get access to the Sales Goal measure.
To integrate data, you must first add one of the common dimensions from the primay data source to the view. For example, when blending Acutal and Target sales data, the two data sources may have a Date field in common. The Date field must be used on the sheet. Then when you switch to the secondary data source in the Data window, Tableau automatically links fields that have the same name. If they don’t have the same name, you can define a custom relationship that creates the correct mapping between fields.
For each data source that is used on the sheet, a query is sent to the database and the results are processed. Then all the results are left joined on the common dimensions. The join is done on the member aliases of the common dimensions so if the underlying values aren’t an exact match, you can fix it up in Tableau.
In general, a good test to see whether data can be integrated smoothly is to drag the dimensions from the primary data source into a text table on one sheet. Then on another sheet, drag the same fields from the secondary data source into a text table. If the two tables match up then the data is most likely going to blend correctly.
Navigating Connections in the Data Window
The top of the Data window lists all of the connections in a given workbook. Simply select the data source you want to use and the Data window updates to show the corresponding fields.

You can resize the connection list area in the Data window to save space. When you resize to a limited vertical height, the list is converted to a drop-down list.

Each connection has an icon to indicate the type of connection. For example, the icon can indicate whether the data source is relational, a cube, or a data extract.

You can right-click the connections to access the commands that are on the Data > Connection menu. For example, you can right-click a connection and rename, export, or close it.
Adding a Secondary Connection
Sometimes you may have data in two separate data sources that you want to analyze together on a single worksheet. While you can analyze several data sources together on the same worksheet, each worksheet must have a primary data source.
The primary data source is the connection that you first use in the view. After you drag fields to the view, the primary connection is marked with a blue check mark.

If you switch to another connection, you’ll notice that the Data window is marked with an orange color to indicate that if you use fields from this connection, it will become the secondary connection.

To add a secondary connection:
1. Connect to the primary data source and build a view.
2. When you need additional data from a separate data source, select Data > Connect to Data.
3. Follow the steps in How to Connect to a Data Source to connect to the secondary data source.
4. On the worksheet where you need the secondary data, select the new connection. Notice that the Data window is colored orange to indicate that it is a secondary data source.
If your view uses any fields that exist in the secondary data source, you’ll see that Tableau automatically defined a relationship to link those two columns. Linked fields are marked with a link icon in the Data window. You can also define your own custom relationships to handle columns that don’t have matching names. You must have a linked field in the view to use data from the secondary connection. Refer to Defining Relationships to learn how to link fields.
5. Drag the fields from the secondary data source into the view.
The fields in the view that are from the secondary data source are marked with an orange check mark to indicate that they are from the secondary data source.

To remove a secondary connection:
1. Remove any secondary fields that are used in the view.
2. Right-click the secondary connection at the top of the Data window and select Close.
Defining Relationships
Tableau automatically recognizes when a field you are using the view exists in a secondary data source. These fields are marked with a link icon in the Data window. You must have a linked field in the view in order to use data from the secondary data source. For example, the workbook shown below has two connections: Superstore Sales and Sales Plan. These two connections have related information including the columns for Customer Segment and Customer State. The data from Sales Plan (the secondary data source) cannot be used until one of those common fields has been added to the view.
You can modify the automatic relationships or create new custom relationships by selecting Data > Relationships.
The Relationships dialog box lets you select a primary data source using the drop-down list at the top. Then you can select a secondary data source in a list on the left side of the dialog box. The right side of the dialog box lists any relationships that have been added.
To add and edit relationships:
1. Select Data > Relationships to open the Relationships dialog box.

2. Make sure that the primary data source is selected from the down list.

3. Select a secondary data source from the list on the left.

4. Select Custom at the top of the relationships list.
5. Click the Add button or select an existing relationship and click the Edit button.

6. Select a field in the primary data source and map it to matching field in the secondary data source.

7. When finished, click OK.
8. Add and Remove as many relationships as necessary, and when finished, click OK again.
The linked fields are marked with a link icon

in the Data window to show that they are related to a field in the primary data source.
The relationship matches values based on the member aliases. You can fix up fields that don’t match by editing the aliases. For example, when mapping a State Name field in the pimary data source to a State Abbreviation field in the secondary data source, “AK” will not map correctly to “Alaska”. You’ll have to modify the aliases in one of the data sources.
Example – Data Blending on a Worksheet
This example uses two data sources: Sample – Superstore Sales (Excel) that comes with the application and an auxilliary Excel file that contains forecasted sales infromation. An example of each of their columns are shown below:
Follow the steps below to use data from both data sources on a single worksheet.
1. Connect to Sample – Superstore Sales (Excel) and build a view that shows Sales by Customer Segment and Product Category.

2. Select Data > Connect to Data and connect to the Sales Plan spreadsheet.
3. Drag the Sales Plan measure to the Level of Detail shelf.

4. Right-click the Sales axis and select Add Reference Line.

5. In the Reference Line dialog box, add a reference line that shows Sales Plan per cell. When finished, click OK.

6. The Worksheet is now pulling data from the secondary data source (Sales Plan) to show how acutal sales compared to the forecasted sales.

Troubleshooting Data Blending
When you integrate data from multiple data sources, you may run into some of these common issues:
• Warning: Cannot Blend Because No Linking Fields
• Asterisks Show in the View
• Warning: Cannot Blend Because No Linking Fields
• When you drag a field from a secondary data source to the view, you may see a warning that says:
• Cannot blend aggregated data from the secondary data source with the data on this sheet becuase there are no linking fields used in the view.
•
•
•
• This warning occurs when you have not used a linking field in the view. For example, if you have two data sources with the related dimensions State and Date, you must be using one of those primay dimensions in the view before you can blend data from the secondary data source.
• The secondary data source may not have any relationships to the primary data source. Look in the Data window for the link icon. Tableau automatically links fields that have the same name. If your fields do not have the same name you’ll have to create a custom relationship. Refer to Defining Relationships.
• Asterisks Show in the View
• When you relate secondary data, make sure that there is only one matching member in the secondary dimension for each mark. If there are multiple matching members you will see an asterisk in the view. For example, say you have two data sources. The primary data source has a state field that contains state abbreviations. The secondary data source also contains a state field along with a customer segment field. Each state may have multiple customer segments (e.g., CA has Consumer and Corporate). When you relate the two data sources on state, you’ve created a relationship where state can have multiple customer segment values. When that happens you will see an asterisk in the view.
•
•
•
• All secondary fields are aggregated. Dimensions are aggregated as Attributes (ATTR), which means that if there’s only one member it will show the member value but if there are multiple members it will show an asterisk.
Joining Tables
Many relational data sources are made up of a collection of tables that are related by specific fields. For example, a data source for a publisher may have a table for authors that contains the first name, last name, phone number, etc. of clients. In addition, there may be another table for titles that contains the price, royalty, and title of published books. In order to analyze these two tables together, to answer questions like, how much was paid in royalties last year for a particular author, you would join the two tables using a common field such as Author ID. That way you can view and use the fields from both tables in your analysis. This section discusses the following topics:
• Adding Tables
• Editing Tables
Adding Tables
You can connect to multiple tables when you first connect to a data source using the connection dialog box. There you can add tables, specify joins, and modify the field aliases in the case you have similarly named fields in each of the tables. You can also add tables after you have already connected to the data source. This section describes how to connect to multiple tables as well as adding tables to the Data window.
• Connecting to Multiple Tables
• Adding Tables to the Data window
Connecting to Multiple Tables
1. In the Connection dialog box, complete the connection information according to the examples described inExamples – Connecting to Data Sources.
2. Select the table or view you want to start with (typically the fact table) and then select the Multiple Tablesoption.

3. Click the Add New Table button at the bottom of the dialog box.

4. In the Add Table dialog box select a table to add to the Data window.

5. Optionally change Field aliases.
In the bottom half of the Add Table dialog box, there is a list of fields with their aliases. Double-click the field alias to change how the field will be displayed in the Data window. This is often useful when you have duplicate field names across tables or your field names are not very understandable.
6. Add a join by selecting the Join tab.
Tableau will automatically create a join for you based on the structure of your data. Select the Joins tab to inspect the join clause to make sure it is how you want to connect the tables.
You can delete unwanted join clauses by selecting it in the list of join clauses and clicking Delete.
7. In the Join pane, add one or more join clauses by selecting a field from the original table, a field from the added table, and an operator. Then click Add to add it to the list of Join Clauses.
For example, in a data source that has a table of order information and another for users information, you could join the two tables based on the Region field that exists in both tables. Select Region in both the lists of fields, select the equal sign as the operator, and click Add.

3. Click the Add New Table button at the bottom of the dialog box.

4. In the Add Table dialog box select a table to add to the Data window.

8. Select the type of join from the Join Type drop-down list. You can select Inner, Left, or Right.
Please note, you cannot nest Inner joins within Left or Right joins. These joins will cause a join expression not supported error.
9. When finished, click OK.
The tables are listed in the Connection dialog box along with the foreign keys.
10. Complete the connection by giving it a name and clicking OK.
When you add joined tables, the Data window is automatically organized to use the Group by Table command. You can turn this feature off or change how the Data window is sorted using the Data window menu.
Editing Tables
You can modify the joined tables using the Tables command. You may want to edit a table to add or remove fields from the Data window, modify the join clause, or add more join clauses to further define how the table is connected to the original table.
1. Select Data > Tables.
2. In the Tables dialog box, select the joined table you want to modify and click Edit.
3. In the subsequent dialog box, you can change the table and field aliases as well as add and remove fields from the Data window. Select the Join tab to edit the join clauses.
4. When finished click OK twice to close the Tables dialog boxes.
Managing Queries
Queries are automatically generated every time you add a field to a shelf and interact with the view. Tableau offers several ways you can manage these queries once they are sent to the underlying data source. The following are some topics to understand when managing queries:
• Automatic Updates
• Cancel Query
• Abandoned Queries
• Precision Warnings
• Automatic Updates
• When you place a field on a shelf, Tableau generates the view by querying the data source. If you are creating a dense data view that involves many fields, the queries might be time consuming and significantly degrade system performance. In this case, you can instruct Tableau to turn off automatic updates.
• By default, automatic updates are turned on and the toolbar button is highlighted . However, it is sometimes more efficient for Tableau to execute the queries you need for your final view, rather than for every intermediate step required to compose that view. You can turn off updates by pressing F10 or the Automatic Updates toolbar button .
• While automatic updates are turned off, you can still update the view at any time by clicking F9 or the Run Update on the toolbar. This way, you can update your data view at an intermediate step. It is possible to enter an invalid state when automatic updates are turned off. When this happens, the view is desaturated and invalid commands are disabled. The view and commands become available again when you click Run Update on the toolbar.
• For example, the view below has automatic updates turned off. When the aggregation for Inventory is changed from a summation to an averageWhen the level of detail is changed on the Gen2,Product Category field, the view is desaturated to let you know that you have made a change to the view that has made the current view invalid.
•
•
Cancel Query
This command is used any time you want to stop a query that is in process. You may want to cancel a query that is taking a long time to complete due to the size of the data source. When a query is taking a long time to complete, a progress dialog box opens. You can cancel a requested query by clicking the Cancel button on the Processing Request dialog box.
To cancel a query:
1. Click Cancel in the Processing Request dialog box.

2. After canceling a query the view becomes invalid because it is in an in-between state. The result is a blank view although all your fields are still on the shelves. To resume working with Tableau, alter the view in anyway and allow the query to complete.
Note:
Canceling a large number of queries can result in performance degradation in the underlying database. Although the query has been abandoned by Tableau, it is still executing on the database.
Abandoned Queries
When you cancel a query in Tableau, the database is told to stop processing the query. However, some databases do not support cancel (MS Excel and MS Access). If you cancel a query using one of these types of data sources, the query is abandoned by Tableau but is still running in the background and using resources. When you have abandoned queries, an indicator appears in the bottom right corner of the workbook showing the number of queries still running . As queries in the background complete, the number will go down. It is important to monitor the number of queries running and not let the number get too high, otherwise you will see performance degradation of both Tableau and the underlying database.
Note:
Text, Microsoft Excel, and Microsoft Access data sources may be temporarily unavailable after cancelling a query because of a lock performed internally. You may have to wait until the abandoned query has completed before re-connecting.
Precision Warnings
When you add a field to a view that contains values with more precision than Tableau can model, a warning icon is displayed in the bottom right corner of the status bar. For example, a value in the database may have 22 decimal places but Tableau only supports up to 15. When you add that field to the view, you get a precision warning. If you click on the warning, you can read more details including the number of decimal places that have been truncated in the view.
Remember that the precision of the data displayed in Tableau will always first be dependent on the data in your database. If the values in your database exceed 15 decimal places, when you add them to the view, the value is truncated and a precision warning appears.
Understanding Data Fields
The data in all data sources are categorized into fields such as Customer, Sales, Profit, Temperature, etc. These fields are made from the columns in your data source. When you connect to a data source with Tableau, the fields are displayed along the left side of the workbook in the Data window. The fields are what you will use to build views of your data. Each field is automatically assigned a data type (such as integer, string, and date) and a pair of data roles.
• Understanding the Data Window
• Data Window Features and Functions
• Editing Field Properties
• Data Types and Roles
Understanding the Data Window
All data sources contain fields. In Tableau, these fields appear in the Data window. For relational data sources, the fields are determined by the columns of a table or view. Each field contains a unique attribute of the data such as customer name, sales total, product type, and so on. For example, some of the fields of an Excel worksheet are shown below.

After you connect to a data source with Tableau, the data source fields appear on the left side of the workbook in the Data window.

The Data window organizes fields into three areas:
• Dimensions – Fields that typically hold discrete qualitative data. Examples of dimensions include dates, customer names, and customer segments.
• Measures – Fields that typically hold numerical data that can be aggregated. Examples of measures include sales, profit, number of employees, temperature, frequency, and pressure.
• Sets – An additional area that stores custom fields based on existing dimensions and criteria that you specify. Named sets from an MS Analysis Services server also appear in Tableau in this area of the Data window. You can interact with these named sets in the same way you interact with other custom sets in Tableau
• Parameters – An additional area that stores parameters that you have created. Parameters are dynamic variables that can be used as placeholders in formulas.
By default, fields containing text, date or boolean values are dimensions, while fields containing numerical values are measures.
The Data window for an Excel worksheet (a relational database) is shown below. The Discount and Order Quantityfields contain numbers and appear as measures in the Data window. The Order Priority field contains text and theOrder Date field contains dates. These fields appear as dimensions in the Data window.

Note:
By default the field names defined in the data source are displayed in the Data window. You can rename fields as well as member names.
• Relational and Multidimensional Data
• Hierarchies (For Relational Databases)
• Measure Values and Measure Names
• Number of Records
• Latitude and Longitude (generated)
• Relational and Multidimensional Data
• The Data window for a relational and multidimensional data source are shown below. Note that the windows look essentially the same for both data sources in that the fields are organized into dimensions and measures. However, the multidimensional data source contains hierarchies for dimensions. For example, notice that the Product dimension in the multidimensional Data window contains hierarchical members such as Product Family, Product Department, and so on.
•
•
•
• You can expand or collapse the various areas or hierarchies in a multidimensional Data window by clicking the plus button. You can hide the Data window all together by selecting View > Data Window.
Hierarchies (For Relational Databases)
Unlike multidimensional data sources, relational data sources don’t have built in hierarchies However, often relational data sources have related dimensions that have an inherent hierarchy. For example, a data source may have fields for Country, State, and City. These fields could be grouped into a hierarchy called Location.
You can assemble into these hierarchies in the Data window. Hierarchies support single click navigation up and down the levels. When you use the fields in the view, a plus button displays on the field so you can drill down and up in the hierarchy.
• Creating a Hierarchy
• Removing a Hierarchy
Creating a Hierarchy
1. Do one of the following:
o Right-click one or more selected fields in the Data window and select Create Hierarchy
o In the Data window, drag and drop a field directly on top of another field.

2. In the subsequent dialog box, specify a name for the hierarchy.

The hierarchy can be expanded and collapsed in the Data window. You can also drag an drop to add and remove the fields in the hierarchy.
Removing a Hierarchy
Right-click the name of the hierarchy in the Data window and select Remove Hierarchy.

Measure Values and Measure Names
The Data window contains a few fields that are not part of your data source, two of which are Measure Names andMeasure Values. The Measure Values field always appears at the bottom of the Measures area of the Data window and contains all the measures of your data source collected into one field. The Measure Names field always appears at the bottom of the Dimensions area of the Data window and contains all the names of the measures collected into a single dimension.
Tableau automatically creates these fields so that you can build certain types of data views that involved multiple measures. IN particular, use these fields if you want to display multiple measures in the same pane simultaneously. As shown below, creating a view with Measure Names and Measure Values is one way to display all the data in your data source.
Number of Records
In addition to the Measure Names and Measure Values fields, the Data window contains a Number of Records field that is also not part of the underlying data source. This field represents the number of rows in the data source. It is useful when you are working with a data source that is primarily categorical resulting in very few measures.
Latitude and Longitude (generated)
If you have defined any fields to be geographic fields, that is they can be used with maps, Tableau automatically geocodes your data and includes Latitude (generated) and Longitude (generated) fields. You can use these fields to overlay your data on live maps.
Data Window Features and Functions
The Data window has many features and functions to help you organize your data fields, find specific fields, and hide others.
• Organize the Data Window
• Find Fields
• Rename Fields
• Hide or Unhide Fields
• Add Fields to the Data Window
Organize the Data Window
You can reorganize the Data window from its default layout by selecting from a variety of sorting options. These Sort by options are located in the Data window menu.

You can sort by one of the following options:
• Name – lists the dimensions and measures in alphabetical order according to their field aliases.
• Data source order – lists the dimensions and measures in the order they are listed in the underlying data source.
You can also select to Group by Table, which is a command that toggles on and off. When you select this option, the dimensions and measures are grouped according to the database table they below to. This is especially useful when you have several joined tables.
Find Fields
You can search for fields in the Data window. If there are many fields in your data source it can be difficult to find a specific one like “Date” or “Customer” or “Profit.” To search for a field, click the Find Field icon at the top of the Data window (Ctrl + F) and type the name of the field you want to search for. Valid field names that fit the description appear in a drop-down list. Select the field you want and press enter on your keyboard to highlight the field in the Data window.
Rename Fields
You can assign an alternate name for a field that displays in the Data window as well as in the view. For instance, a field called Customer Segment in the data source could be aliased to appear as Business Segment in Tableau. You can rename both dimensions and measures. Renaming a field does not change the name of the field in the underlying data source, rather it is given a special name that only appears Tableau workbooks. The changed field name is saved with the workbook .
• Renaming a Field
• Reverting to the Default Field Name
Renaming a Field
1. Right-click the field name in the Data window you want to rename and select Rename.
2. Type the new name in the subsequent dialog box and click OK.
The field displays with the new name in the Data window.
Reverting to the Default Field Name
1. Right-click the field and select Rename.
2. In the Rename dialog box, click Reset and then click OK.
Hide or Unhide Fields
You can selectively hide or show fields in the Data window. To hide a field, right-click the field you want to hide and select Hide.

When you want to change your fields from hidden to visible, select Show Hidden Fields on the Data window menu.

The hidden fields are shown in gray in the Data window. You can then select one or more hidden fields, right-click and select Unhide.

Select Hide All Unused Fields on the Data window menu to quickly hide all of the fields that are not being used in the workbook.

Add Fields to the Data Window
You can create calculated fields that appear in the Data window. These new computed fields can be used like any other field. Select Create Calculated Field on the Data window menu. Alternatively, select Analysis > Create Calculated Field.
Editing Field Properties
When you drag fields to shelves, the data is represented as marks in the view. You can specify settings for how the marks from each field will be displayed by setting mark properties. For example, when you place a dimension on the color shelf the marks will be colored by the values within that dimension. You can set the Color property so that anytime you use that dimension on the color shelf your chosen colors are used. Using field properties you can set the aliases, colors, and shapes, default aggregation, and so on.
• Comments
• Aliases
• Colors
• Shapes
• Formats
• Sort
• Aggregation
• Measure Names
Comments
Fields can have comments that describe them. The comments display in a tooltip in the Data window and in the Calculated fields dialog box. Field comments are a good way to give more context to the data in your data source. Comments are especially useful when you are building a workbook for others to use.
• Adding a Comment to a Field
Adding a Comment to a Field
1. Right-click a field in the Data window and select Field Properties > Comment.

2. Write a comment in the subsequent dialog box. Comments support rich text formatting that will be represented in the tooltip.

3. When finished, click OK.

Aliases
Aliases are alternate names for specific values within a dimension. For example, you may want to assign aliases for the values of the “Customer Segment” dimension. Perhaps you want the “Consumer” members of this field to display as “Home Consumer” in all views.
Aliases can be created for the members of most dimensions in the Data window. You cannot, however, define aliases for continuous dimensions and dates and they do not apply to measures. The method for creating aliases depends on the type of data source you are using.
• Aliases with a Relational Data Source
• Aliases with a Multidimensional Data Source
• Aliases with a Relational Data Source
• To create an alias for a relational data source, right-click a field name and select Field Properties > Aliases
•
•
• A dialog box opens allowing you to define aliases for each value within the selected dimension. You can reset the member names back to their original names by clicking the Clear Aliases button in the bottom right corner of the Edit Aliases dialog box.
Aliases with a Multidimensional Data Source
Aliases for multidimensional databases are created on the server by the server administrator and can be activated in Tableau using the Alias File option on the Data menu. Please talk to your database administrator to find out whether your database has aliases available. Aliases are not support by Microsoft Analysis Services databases.
By default the alias for every member of every dimension is initially defined to be the original member name. For example, the figure below shows a bar chart built from an Essbase database. By default, the original member names are displayed (example on the left). As you can see, these names are not very intuitive. By selecting Data > Aliases File and selecting an appropriate alias file set up by the database administrator, meaningful names are displayed in the headers.

• Example – Editing Aliases
Example – Editing Aliases
The Superstore Sales Excel sample data source contains a measure called Discount, which contains discount values from 0 to 0.25 or 25%. Suppose you want to analyze these data by categories: low discount, medium discount, and high discount.
To create the categories, you could first bin the measure so that when added to the view it create discrete headers instead of a continuous axis. In this example, define a bin size equal to 0.10, which produces three bins. The first bin contains the values 0 to 9%, the second bin contains the values 10% to 19%, and the third bin contains the values 20% and greater. The Create Bins dialog box for this field is shown below.

The new binned field is named Discount (bin) and appears in the Dimensions area of the Data window.

When you place Discount (bin) on the Rows or Columns shelf, the default aliases for the bins are given by the lower limit of the bin’s numerical range

To improve the readability of the bins when they are displayed in Tableau, you can define aliases such as “Low discount”, “Medium discount”, and “High discount.”
• To edit the aliases:
To edit the aliases:
1. Right-click the name of the dimension in the Data window and select Field Properties > Aliases.

2. Assigning an alias to every member of the Discount (bin) fields. For example, the member originally labeled as 0.0% is now labeled as “Low Discount.”

You can change aliases at any time using the Edit Aliases dialog box. To do so, click on the alias you want to change and specify the new name. Use the Tab key to advance from one value to the next. To restore the original aliases, click the Clear Aliases button in the bottom right corner of the dialog box. You can also sort the members or their aliases by clicking the appropriate column header. After completing the Edit Aliases dialog box, Tableau automatically displays the aliases in the view.
Colors
When you use a dimension to color encode the view, default colors are assigned to the field’s values. Color encodings are shared across multiple worksheets that use the same data source to help you create consistent displays of your data. For example, if you define the Western region to be green, it will automatically be green in all other views in the workbook. You can set the default color encodings for a field by right-clicking the field in the Data window and selectingField Properties > Color.
Shapes
When you use a dimension to shape encode the view, default shapes are assigned to the field’s values. Shape encodings are shared across multiple worksheets that use the same data source to help you create consistent displays of your data. For example, if you define the Furniture products are represented with a square mark, it will automatically be changed to a square mark in all other views in the workbook. You can set the default shape encodings for a field by right-clicking the field in the Data window and selecting Field Properties > Shape.
Formats
You can set the default text format for date and number fields. For example, you may want to always show the Sales values as currency using the U.S. dollar sign and two decimal points. On the other hand, you may want to always show Discount as a percentage. You can set the default formats by right-clicking a date or numeric field and selecting and option on the Field Properties menu. A dialog box opens where you can specify a default format.
Sort
You can set a default sort order for the values within a categorical field so that every time you use the field in the view, they values will be sorted correctly. For example, let’s say you have an Order Priority field that contains the values High, Medium, and Low. When you place these in the view, by default they will be listed as High, Low, Medium because they are shown in alphabetical order. You can set a default sort so that these values are always listed correctly. To set the default sort order right-click a dimension and select Field Properties > Sort. Then use the sort dialog box to specify a sort order.
Note:
The default sort order also controls how the field values are listed in a quick filter.
Aggregation
You can also specify a default aggregation for any measure. The default aggregation will be used automatically when the measure is first totaled in the view.
• To specify a default aggregation:
To specify a default aggregation:
1. Right-click any measure in the Data window and select Field Properties > Aggregation.
2. On the Aggregation list, select an aggregation.

Whether you are specifying the aggregation for a field on a shelf or the default aggregation in the Data window, you can select from the following options:
Option Description
Default For Essbase data sources, this option computes the total using the default aggregation determined by the data type (typically SUM).
SUM Displays the sum of all shown values.
Average Displays the average of all shown values.
Minimum Displays the smallest shown value.
Maximum Displays the largest shown value.
Server Computes the aggregation on the server.
Measure Names
There are times that you will want to show multiple measures in a view and so you will use the Measure Values and the Measure Names fields. When you use Measure Names all of the measure names appear as row or column headers in the view. However, the headers include both the measure name and the aggregation label. So if you are showing the summation of profit the header displays as SUM(Profit). You can change the names so that they do not include the aggregation label by editing the member aliases of the Measure Names field. This feature becomes particularly useful when you are working with a text table that shows multiple measures. For example, suppose you have a text table containing the aggregated profit of each product category by region.

Now suppose you want to show both the Profit and the Sales for each product category and region. When you add the Sales measure to the text table, the measures are combined and the Measure Values field is placed on the Text shelf. Additionally, the Measure Names field is added to the Rows shelf.

Notice how the header names include the aggregation label. Those headers can be annoying if you are putting this view into a presentation. To change the measure names, right-click the Measure Names field on the Rows shelf and select Edit Aliases. Make the changes and click OK.
Data Types and Roles
In Tableau, there are several data types that are supported. For example, you may have text values, date values, numerical values, and more. Each of the data types can take on different roles that dictate their behavior in the view.
• Data Types
• Data Roles
Data Types
All fields in a data source have a data type. The data type reflects the kind of information stored in that field, for example integers (410), dates (1/23/2005) and strings (“Wisconsin”). The data type of a field is identified in the Data window by one of the icons shown below.
Icon Description
Text values
Date values
Date & Time values
Numerical values
Boolean values (relational only)
Geographic values (used with maps)
Sometimes Tableau may identify a field with a data type that is incorrect. For example, a field that contains dates may be identified as an integer rather than a date. You can change the data type in Tableau by right-clicking the field in the Data window, selecting Change Data Type, and then selecting the appropriate data type.

Note:
Sometimes the data in your database is more precise then Tableau can model. When you add these values to the view a precision warning will appear in the right corner of the status bar.
• Mixed Data Types for Excel and CSV Files
Mixed Data Types for Excel and CSV Files
Most columns in an Excel or CSV (comma separated value) file contain values of the same data type (dates, numbers, text). When you connect to the file, Tableau creates a field in the appropriate area of the Data window for each column. Dates and text values are dimensions, and numbers are measures.
However, a column might have a mixture of data types such as numbers and text, or numbers and dates. When you connect to the file, the mixed-value column is mapped to a field with a single data type in Tableau. Therefore, a column that contains numbers and dates might be mapped as a measure or it might be mapped as a date dimension. The mapping is determined by the data types of the first 16 rows in the data source. For example, if most of the first 16 rows are text values, then the entire column is mapped as text.
Note:
Empty cells also create mixed-value columns because their formatting is different from text, dates, or numbers.
Depending on the data type Tableau determines for each field, the field might contain Null values for the other (non matching) records ad described in the table below.
Mapped Data Type Treatment of Other Data Types in the Field
Text Dates and numbers are treated at text. Nulls are not created.
Dates Text is treated as Null. A number is treated as the day in numeric order from 1/1/1900. You can identify these values by creating a row or column header with the field.
Numbers Text is treated as Null. A date is treated as the number of days since 1/1/1900. You can identify these values by converting the measure to a dimension, and then creating row or column headers with the field.
If using fields that are based on mixed-value columns introduces difficulties when analyzing your data, you can:
• Format empty cells in your underlying data source so they match the data type of the column.
• Create a new column in Excel that does not contain mixed values.
Data Roles
In addition to a data type, every field in Tableau is characterized by two important additional settings that determine the role and behavior of the field when it is placed on a shelf.
To expose the full functionality of Tableau it is useful to control whether a field is a dimension or measure, and continuous or discrete.
• Data Roles; Dimension vs. Measure
• Converting Measures to Dimensions
• Data roles: Continuous vs. Discrete
• Converting Discrete to Continuous Quantities
Data Roles; Dimension vs. Measure
Dimensions
Dimensions typically produce headers when added to the rows or columns shelves in the view. By default, Tableau treats any field containing qualitative, categorical information as a dimension. This includes, for instance, any field with text or dates values. However, in relational data sources, the actual definition of a dimension is slightly more complex. A dimension is a field that can be considered an independent variable.
This means that a measure can be aggregated for each value of the dimension. For instance, you might calculate the Sum of “Sales” for every “State”. In this case the State field is acting as a dimension because you want to aggregate sales for each state. The values of Sales are dependent on the State, so State is an independent field and Sales is a dependent field.
Such aggregation could also be computed for numeric fields that are treated as dimensions. For instance, you might want to calculate the SUM of Sales for each “Discount Rate” offered to customers. In this case the Discount Rate field acts as an independent field and the Sales field is dependent even though both fields are numeric. You can use a numeric field as the independent field by first converting the Discount Rate measure to a dimension.
Measures
Measures typically produce axes when added to the rows or columns shelves. By default, Tableau treats any field containing numeric (quantitative) information as a measure. However, in relational data sources, the actual definition of a measure is slightly more complex. A measure is a field that is a dependent variable; that is, its value is a function of one or more dimensions.
This means that a measure is a function of other dimensions placed on the worksheet. For instance, you might calculate the Sum of “Sales” for every “State”. In this case, the Sales field is acting as a measure because you want to aggregate the field for each state. But measures could also result in a non-numeric result. For instance, you might create a calculated measure called “Sales Rating” that results in the word “Good” if sales are good and “Bad” otherwise. In this case the “Sales Rating” field acts as a measure even though it produces a non-numeric result. It is considered a measure because it is a function of the dimensions in the view.
Converting Measures to Dimensions
By default, Tableau treats all relational fields containing numbers as measures. However, you might decide that some of these fields should be treated as dimensions. For example, a field containing ages may be categorized as a measure by default in Tableau because it contains numeric data. However, if you want to look at each individual age rather than an axis you can convert the Age field to a dimension.
• Click and drag the field from the measures area of the Data window and drop it into the dimensions area.

• Right-click the measure in the Data window nad select Convert to Dimension.
The Order Quantity field is now displayed in the Dimensions area of the Data window and is a discrete quantity as indicated by the blue number icon.

If you place the converted field on a shelve, it products headers instead of an axis.
Data roles: Continuous vs. Discrete
In addition to dimensions and measures, each field is categorized as either discrete or continuous. Below are example graphs illustrating the difference between these two data roles. Both examples show the Sum of Margin as a function of Inventory level. It is the same information presented in two different ways.
Discrete

Each inventory value is drawn as a header. The Inventory field is colored blue on the Column shelf. Continuous

Each inventory value is drawn along a continuous axis. The Inventory field is colored green on the Column shelf.
Whether a field is continuous or discrete is reflected in the color of the field’s data type icon. In the Data window, blue icons indicate discrete and green icons indicate continuous fields.
Discrete fields always result in headers being drawn whenever they are placed on the row or columns shelves. Continuous fields always result in axes when you add them to the view. These roles are important because you may want to display your data continuously or discretely depending on what you are trying see and the data itself. You can switch between continuous and discrete data roles.
Converting Discrete to Continuous Quantities
When you are using a relational data source you can convert any numeric or date field into a continuous field.
Select Convert to Continuous on the field’s right-click context menu.

The Order Date field is still displayed in the Dimensions area of the Data window, but now uses a green icon, which indicates it is a continuous quantity.

Placing the field on a shelf produces an axis. However, the field is not a measure because you cannot aggregate it using the usual set of aggregation functions such as SUM and AVERAGE.
You can also convert fields to continuous or discrete directly when they are on a shelf using the field’s menu. This converts the field while it is on the shelf but does not change its role in the Data window. That way you can use the field as continuous for a specific analysis and discrete elsewhere in the view.

Easy Tableau

How to explore tableau and it's different ways of usage for our needs

Connect to Data in Tableau