Tableau Workbooks - Tableau Public
Overview about Tableau Software
.
Components of Tableau Software
1) Tableau Desktop
2) Tableau Server
3) Tableau Reader
Tableau Desktop:
Tableau Desktop is a data visualization application that lets you analyze virtually any type of structured data and produce highly interactive, beautiful graphs, dashboards, and reports in just minutes. After a quick installation, you can connect to virtually any data source from spreadsheets to data warehouses.
Tableau Server
Tableau Server is a business intelligence solution that provides browser-based visual analytics anyone can use at just a fraction of the cost of typical BI software. With just a few clicks, you can publish or embed live, interactive graphs, dashboards and reports with current data automatically customized to the needs of everyone across your organization. It deploys in minutes and users can produce thousands of reports without the need of IT services — all within your IT infrastructure.
Tableau Reader
Tableau Reader is a free viewing application that lets anyone read and interact with packaged workbooks created by Tableau Desktop.
Users:
Small, Medium & Large size companies are using Tableau as a visualization & Reporting tool.
Customers include GE, Novartis, Bank of America, Google, Cleveland Clinic, GM, CNBC, Microsoft, Wells Fargo, the District of Columbia, Allstate, Cornell and Harvard.
Opening and Closing the Application
The first thing to understand is how to open and close the application.
Open Tableau
Once we install the Tableau S/W, one Tableau icon has been added in the desktop,
There are many ways to open Tableau from your desktop computer. Open the application by doing one of the following:
• Double-click the Tableau icon on your desktop.
• Select Start > All Programs > Tableau.
• Double-click a Tableau workbook or bookmark file. Tableau files are typically stored in the My Tableau Repository folder of your My Documents folder.
• Drag a data source such as an Excel or Access file onto the Tableau icon or the application window. Tableau automatically makes a connection to the data source.
Close Tableau:
When you are done working in Tableau you should save your work and close the application. Close the application by doing one of the following:
• Click the Close icon located in the right corner of the application title bar.
Workbooks and Sheets:
Tableau uses a workbook and worksheet file structure, like Microsoft Excel Workbook & Excel sheet.
• Workbooks
• Sheets
Workbooks:
Tableau workbook files are much like Microsoft Excel workbooks. They contain one or more worksheets or dashboards or stories and hold all of our work. They allow us to organize, save, and share our results. When weopen Tableau, a blank workbook is automatically created.
We can also create a new workbook by selecting File > New or by pressing Ctrl + N on your keyboard.
We can open an existing workbook by doing one of the following:
Double-click the thumbnail image of the workbook on the start page. The start page shows workbooks that you’ve recently used.
Select File > Open and navigate to the location of your workbook using the Open dialog box. Tableau workbooks have the .twb or .twbx file extensions.
Double-click on any workbook file.
Drag any workbook file onto the Tableau desktop icon or onto the running application.
The workbook name is displayed in Tableau’s title bar.
The Tableau Workspace
The Tableau workspace consists of menus, a toolbar, the Data window, cards that contain shelves and legends, and one or more sheets. Sheets can be worksheets or dashboards.
Worksheets contain shelves, which are where you drag data fields to build views. You can change the default layout of the shelves and cards to suit your needs, including resizing, moving, and hiding them.
Dashboards contain views, legends, and quick filters. When you first create a dashboard, the Dashboard is empty and all of the worksheets in the workbook are shown in the Dashboard window.
Dimensions – Fields that typically hold discrete qualitative data. Examples of dimensions include dates, customer names, and customer segments.
Dimensions are key objects of Business which are used to describe the Business, Example of Dimensions are Product, Customer, Geography,………
Measures – Fields that typically hold numerical data that can be aggregated. Examples of measures include sales, profit, and number of employees, temperature, frequency, and pressure. Measures are key figures which are used to evaluate the Business. Which are used to analyze the business.
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 or from a Teradata OLAP connector 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.
Cards and Shelves
Every worksheet contains a variety of different cards that you can show or hide. Cards are containers for shelves, legends, and other controls. For example, the Marks card contains the mark selector, the size slider, the mark transparency control, and the shape, text, color, size, angle, and level of detail shelves.
Cards can be shown and hidden as well as rearranged around the worksheet.
The following list describes each card and its contents.
Columns Shelf – we use Columns Shelf to drag fields to add columns to present the view.
Rows Shelf - we use Columns Shelf to drag fields to add rows to present the view.
Pages Shelf– We use Pages shelf where we can create several different pages with respect to the members in a dimension or the values in a measure.
Filters Shelf– It contains the Filters, use this shelf to specify the values to include in the view.
Measure Values Shelf – contains the Measure Values shelf; use this shelf to use multiple measures along a single axis. This shelf is only available when there is a blended axis in the view.
Color Legend – contains the legend for the color encodings in the view and is only available when there is a field on the Color shelf.
Shape Legend – contains the legend for the shape encodings in the view and is only available when there is a field on the Shape shelf.
Size Legend – contains the legend for the size encodings in the view and is only available when there is a field on the Size shelf.
Map Legend - contains the legend for the symbols and patterns on a map. The map legend is not available for all map providers.
Quick Filters – a separate quick filter card is available for every field in the view. Use these cards to easily include and exclude values from the view without having to open the Filter dialog box.
Parameters – a separate parameter card is available for every parameter in the workbook. Use these cards to modify parameter values.
Marks – contains a mark selector where you can specify the mark type as well as the Path, Shape, Text, Color, Size, Angle, and Level of Detail shelves. The availability of these shelves are dependent on the fields in the view.
Title – contains the title for the view. Double-click this card to modify the title.
Caption – contains a caption that describes the view. Double-click this card to modify the caption.
Summary – contains a summary of each of the measures in the view including the Min, Max, Sum, and Average.
Map Options - allows you to modify the various labels and boundaries shown in the online maps. Also you can use this card to overlay metro statistical area information.
Current Page – contains the playback controls for the Pages shelf and indicates the current page that is displayed. This card is only available when there is a field on the Pages shelf.
Each card has a menu that contains common controls that apply to the contents of the card. For example you can use the card menu to show and hide the card. Access the card menu by clicking on the arrow in the upper right corner of the card.
Files and Folders:
You can save your work using several different Tableau specific file types: workbooks, bookmarks, packaged data files, data extracts, and data connection files. Each of these file types are described below.
Workbooks (.twb) – Tableau workbook files have the .twb file extension and are marked with the workbook icon. Workbooks hold one or more worksheets and dashboards.
Bookmarks(.tbm) – Tableau bookmark files have the .tbm file extension and are marked with the bookmark icon. Bookmarks contain a single worksheet and are an easy way to quickly share your work.
Packaged Workbooks (.twbx) – Tableau packaged workbooks have the .twbx file extension and are marked with the packaged workbook icon. Packaged workbooks contain a workbook along with any supporting local file data sources and background images. This format is the best way to package your work for sharing with others who don’t have access to the data.
Data Extract Files (.tde) – Tableau data extract files have the .tde file extension and are marked with the extract icon. Extract files are a local copy of a subset or entire data source that you can use to share data, work offline, and improve database performance.
Data Connection Files(.tds) – Tableau data connection files have the .tds file extension and are marked with the data connection icon. Data connection files are shortcuts for quickly connecting to data sources that you use often.
We can build different kinds of List, Crosstab, Graphs and Maps Reports using Tableau Software. Our data visualization tool of choice is Tableau, because it connects to pretty much any type of data, offers a very wide range of visualization types and can handle huge volumes of data remarkably quickly when used well. But up until now we have always treated Tableau as a standalone tool sitting alongside whichever performance testing or metrics collections tools we are using on a performance assignment. That works fine – but it does mean that the analysis and visualization doesn’t form an integral part of our workflow in these other tools. There are lots of opportunities to streamline the workflow, allowing interactive exploration of test results data – drilling down from high-level summaries showing the impact to low-level detail giving strong clues about the cause of issues. If only we could carry context from the performance testing tool to the visualization tool.
1) Simple List Report – Single Field
Open Tableau Software & connect to the required Database. (Sample _Superstore_Updated_1.xlsx)
Select Region field from Dimension shelf & hold Control & Select Sales from Measures shelf.
Click on Show Me, select “Text Tables”.
2) Simple List Report – Multiple Fields
Open Tableau Software & connect to the required Database. (Sample _Superstore_Updated_1.xlsx)
Select Region, Category fields from Dimension shelf & hold Control & Select Sales, Profit fields from Measures shelf.
Click on Show Me, select “Text Tables”
3) Simple List Report – Combination of Rows & Columns
Open Tableau Software & connect to the required Database. (Sample _Superstore_Updated_1.xlsx)
Select Region, Order Date Updated fields from Dimension shelf & hold Control & Select Sales, Profit fields from Measures shelf.
Click on “+” symbol of Order Date Updated field on Row shelf then we get the Quarters, & click on Quarters “+” symbol then we get the Months data.
Apply the Formatting
4) Applying filter – Measures
Open Tableau Software & connect to the required Database. (Sample _Superstore_Updated_1.xlsx)
Select Region, Order Date Updated fields from Dimension shelf & hold Control & Select Sales, Profit fields from Measures shelf.
Click on “+” symbol of Order Date Updated field on Row shelf then we get the Quarters, & click on Quarters “+” symbol then we get the Months data.
Drag Sales field from Measures shelf to Filters shelf and select “Sum”, define the range which you want to show on the report. In this example we are defining ranges between 90000 and 150000.
5) Applying color formatting
Open Tableau Software & connect to the required Database. (Sample _Superstore_Updated_1.xlsx)
Select Region, Order Date Updated fields from Dimension shelf & hold Control & Select Sales, Profit fields from Measures shelf.
Click on “+” symbol of Order Date Updated field on Row shelf then we get the Quarters, & click on Quarters “+” symbol then we get the Months data.
Drag Region Field from Dimension shelf to Colour Marks shelf, it assigns the colors automatically.
If we want to change the colors, double click on “South” on the color mark then assign the required colors.
The following example illustrates color formatting is applied based on Region .
Initially we need to connect to a database to analyze the business.
It is a crucial phase since we need to fetch the data & define relations among the tables. If we connect to multiple databases, we need to define proper relationships between databases.
Tableau supports different kinds of data sources including Relational, Multi-Dimensional, MS-Excel and Text files.
Tableau Data Extract File
Microsoft Access Database
Microsoft Excel Workbook
Text File
Tableau Server
Aster Data nCluster
Cloudera Hadoop Hive
Firebird Database
Greenplum Database
IBM DB2 Database
InterSystems Caché Database
Microsoft Analysis Services Database
Microsoft PowerPivot
Microsoft SQL Server Database
MySQL Database
Netezza Database
OData
Oracle Database
Oracle Essbase Database
ParAccel Database
PostgreSQL Database
Progress OpenEdge Database
Sybase IQ Database
Teradata Database
Connect to a Data Source
Open Tableau Software, Click on Microsoft Excel.
Provide a path where the file is located.
Drag required tables into the right panel like the below figure. ( In this Example Sheet 1 is selected for reporting)
Connecting to secondary Data source.
Right now Tableau is connected to XYZA file, now we connect to the secondary data source in this section.
Click on the “Connect to Data” icon on the toolbar.
Define the path of the second data source file path. In this Example we have taken “ABC” as the second data source.
Drag required tables into the right panel like the below figure. ( In this Example Sheet 1 is selected for reporting)
Click on the goto worksheet.
Click on the data menu bar, click on “Edit Relationships”, Define relationship between data sources.
Now we can use two data sources for reporting.
Note: Here Primary Data Sources are highlighted with Blue color, Secondary with Orange.
Defining relationships among the tables and applying filters.
Open the Tableau Software by double clicking on Tableau Software Icon.
Connect to the Database,
Drag required tables into the right panel like the below figure. In this Example Fact Table, Product, Location tables are selected for reporting.
Tableau Engine defines the Relationships automatically. Although we can also define our own relationships.
And we can apply the filter before we connect to the work area.
Click on Filters which is located on top right side
Click on add, in the following example We are applying filters on the field “ Market” & select “Central” & “West”.
Central” & “West” markets are only available to the report since we applied Data source filters.
After applying the filter, click on go to the work sheet.
Drag Market, Product fields onto row shelf, sales onto column shelf.
Modifying Data source filters:
Right click on Data source.
Now we can modify the filter here as we want. In this example we are adding one more filter on the “Product Line” column.
Generally we use the filters to reduce the number of records present on the report or to show only required data on the report & to improve the performance.
Tableau have the following different kind of filters
•Regular Filters
Regular Filters – Dimensions
Regular Filters – Measures
Regular Filters – Date
Quick Filters
Context Filters
Regular Filters - Dimensions:
All fields that are filtered show on the Filters shelf.
We can apply the Regular flirters either by dragging fields onto Filers shelf or in the view right click then select either hide or exclude.
Right click on Field from the row shelf.
-> Here we can filter the data in 4 different ways
• General
• Wildcard
• Condition
• Top
First Create a simple Report with Region, SubCategory & Sales fields.
Drag Region filed onto Filters shelf.
General: select the required data which should only be showed on the report.( Ex: Select required Region by dragging Region filed onto Filters Pane)
Select the East region then apply.
Wildcard: We can filter the data by applying Wildcard. ( Ex: Select required Region by dragging Region filed onto Filters Pane, then click on wildcard, then use the options to get the required data on the work area, here )
Condition:
1) Create a simple report with Region, Sub_Category & Sales fields.
2) Drag Region field onto Filters Shelf, then select 'Condition'.
3) Select the Condition, Select 'ByField', Select 'Sales', 'Sum'.
define condition >= 500000
South Sales is less than 5L, so it is filtered.
Top: We can filter the data by defining top n records. ( EX: Top 6 Cities sales wise, which is showed in the below picture)
Regular Filters - Measures: (Quantitative Filters)
We can apply filters on measure fields by applying aggregation filters.
In this example, We select the Region wise Category whose sales between 1200000 & 230000.
1) Create a simple report with Region, Sub_Category & Sales fields
2) Drag Sales field onto Filters Shelf, then select 'Sum'.
3) Define range between 1200000 & 230000.
Date Filters:
We use this kind of filter to filter the Date Data. By using this kind of filter we can show Required Years, or Months or Quarters Data, (Jan-2015 or Yr-2015, Q1-2013.......)
Or We can show data for Specific range. (EX: From 1-Jan-2011 to 12-Sep-2013,.........)
EX 1: List the customers who made Transactions in Year - 2011
Steps:
In this example, We select the Year 2022,2021 Years data.
1) Create a simple report with Region, Order Date Updated & Sales fields
2) Drag Order Date Updated field onto Filters Shelf, then select 'Year'.
3) Select required Years, 2022,2021.
Tableau let's filter the Date Data in 2 Different ways.
1) Relative Date Filters.
2) Range Date Filters.
Relative Date Filters.
Relative date filters are dynamic, here values are changed as system date gets changed.(Operating System)
EX: Relative - Current Year
Today Date #09-Mar-2023#
Description: Filters_Dim_Regular_Relative Dates:
In this example, We select the 2022 Data (Previous Year).
1) Create a simple report with Region, Order Date Updated & Sales fields
2) Drag Order Date Updated field onto Filters Shelf.
3) Select Relative Dates, then select 'Year', then select 'Previous Year'.
Years, Quarter, Months,........
EX: Today Date #09-Jul-2015#
Show me the Current Year Sales.
Today is #09-Jul-2015#, so it will show 2015 Data (including Future data also if database contains)
1) Drag [Region], [Order Date], [Customer Name] fields into the Rows shelf.
2) Drag one of the Measure fields from the Measures window into the Column Shelf.
3) Drag the [Order Date] into the Filters shelf, then it will be the list that contains Relative & Range Filters, here select Relative then click on Years, Then Click on "This Year" radio button.
4) As of now this report shows 2015 Data.
5) If you run the same report after 1 year, it will show 2016 Data. ( DB should have 2016 data rather than it will show an empty report).
EX: I want to show the current Month, Quarter, Last Quarter this Year, Last month this Year, .........
I want to see the last 3 Years of data on the Report.
*********************
Range Date Filters.
These kinds of filters allow us to define the Static range dates.
And here we can't change date data, it means it is fixed Date filters.
EX: Today we have created a report for the year - 2012. If you run the same report next year, it will show YR-2011 data only, now we can say these filter values are fixed. ( Don't consider Parameter, Quick Filters in this scenario.)
EX:
1) Drag [Region], [Order Date], [Customer Name] fields into the Rows shelf.
2) Drag one of the Measure fields from the Measures window into the Column Shelf.
3) Drag the [Order Date] into the Filters shelf, then it will be the list that contains Range Filters, here select Range then Define Starting & Ending Dates.
4) As of now this report shows 2015 Data.
5) If you run the same report after 1 year, it will show 2016 Data. ( DB should have 2016 data rather than it will show an empty report).
*********************
Tableau offers filtering the data in different ways also, we can call these static filters.
1) Year wise, Month wise, Quarter wise, Week wise,............
2) Starting Date to Till end of the Date as per data in the DB.
3) Up to a certain Ending Date from the starting Date.
*********************
Removing Nulls Data from the Report.
My data has nulls in Date Columns for Some Dates, i want to remove from the Report.
EX:
1) Drag [Region], [Order Date], [Customer Name] fields into the Rows shelf.
2) Drag one of the Measure fields from the Measures window into the Column Shelf.
3) Drag the [Order Date] into Filters shelf, then it will show the list that contains Range Filters, here select Relative then click on "Special", then click on "Non-null dates" option.
4) Now Report don't have the null Dates.
================================
EX:
1) I Want to see data for Current month only.
2) I want to see Sales Information from 1-Jan-2015 to Today.
3) I want to see data for Year 2011 & 13 & 15
4) What is the Orders Amount for the Current Quarter?
5) I want to see This year's March Month Data. ( without using Range Filter, Use alternative approach)
6) I want to See Previous Year Data.
My data has nulls in Date Columns for Some Dates, I want to remove from the Report.
===================================================
Filters - Measures
We can filter the data on measures also by dragging the measure column & drop into filters shelf then click on "SUM" or any other Aggregate function then click on OK, then report will show filtered data.
And
We can filter the Measures range specific or filtering Nulls, or showing only nulls..........
====================================
Quick Filters:
-> We use the Quick Filters to include or exclude the members from the report.
-> It offers more interaction with the report.It means End user can select the required data instantly.
If users don't want to see the data on the report, then they can deselect that specific member from Quick Filter.
EX:
1) Create a simple report with Region, Order Date Updated & Sales fields
2) Right click on the Sub-Category field then click on ‘Show Filter’.
3) Select required Years, 2022,2021.
Changing the UI:
1) Single Value (List): This option offers to select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
2) Single Value Drop Down List:
This option also offers to select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
3) Single Value Slide Bar:
This option also offers to select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
4) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" checkbox.
5) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" checkbox.
Here List of the values shown in the drop down list.
6) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" checkbox.
Here List of the values shown in the drop down list.
7) Custom Value List:
This option also offers to select either at 1 or multiple members a time or select all members by typing the characters.
It will search the members which are related to your typed word.
If type ST, it will show the data EAST, WEST then select whichever members want to show on the report.
It is quite similar to Contains filter.
*****************
Similarly we can apply the Quick filters for Measures.
Quick Filters
Quick Filters are Prompts, which allows end users to select the required data, it increases user interactivity with reports.
Example:
Right click on the Date field on the data window, click on “Show Quick Filter”.
Now we get the Prompt, and we can select whichever we want to see on the report.
For an Example, Select 2011, 2014. Rest of the year will be filtered.
Context Filters:
By default, all filters that you set in Tableau are computed independently. That is, each filter accesses all rows in your data source without regard to other filters. However, you can set one or more categorical filters as context filters for the view. You can think of a context filter as being an independent filter. Any other filters that you set are defined as dependent filters because they process only the data that passes through the context filter.
You may create a context filter to:
• Improve performance – If you set a lot of filters or have a large data source, the queries can be slow. You can set one or more context filters to improve performance.
• Create a dependent numerical or top N filter – You can set a context filter to include only the data of interest, and then set a numerical or a top N filter.
For example, suppose you’re in charge of breakfast products for a large grocery chain. Your task is to find the top 10 breakfast products by profitability for all stores. If the data source is very large, you can set a context filter to include only breakfast products. Then you can create a top 10 filter by profit as a dependent filter, which would process only the data that passes through the context filter.
Context filters are particularly useful for relational data sources because a temporary table is created. This table is automatically generated by Tableau when you set the context, and acts as a separate (smaller) data source that results in increased performance when you build data views.
Example of creating Context Filters:
Drag Field into Row shelf, Date onto Column shelf.
Drag Date field from data window to filters, and click on years, Select 2012, 2014.
Right click on the date field in the filter, click on “Add to Context”.
Once we convert Normal filters as Context filters, Filters color is changed from blue to black.
---------------- --------------
Conditional Calculations:
We create calculations when If any calculation you want to use in the report
which is not there in the database, then we create that calculation with right syntax at tableau level.
or
And we use the Calculations for customization purposes.
EX:
Sales less than 10 K is "Worst Sales",
>10 K and < 15 K is "Average" ,
>15 K and < 20 K is "Above Average" ,
>20 K and < 50 K is "Meet Target" ,
>50 K and < 75 K is "Excellent Sales."
SYNTAX:
1) If condition with 1 Possibility without Else part, we get nulls wherever condition does not match.
IF [Condition Matches] then [Matched Value] end
EX: If sum([Sales])>15000 then "Good" end
Description: In the above example, If sales are more than 15000 then those will be represented as "Good" (If condition matches then "Good" if Condition does not match then we get empty, i.e. null)
If Sales does not exceed 15000 then those values are represented by empty since we did not define the else part. Next example will explain how output will affect if we define the else part.
2) If condition with 2 Possibilities including Else part
Syntax:
IF [Condition Matches] then [Matched Value] else [Non Matched Value] end
EX: If sum([Sales])>15000 then "Good" else "Average" end
Description:
In the above example, If sales are more than 15000 then those will be represented as "Good" (If condition matches then "Good" if Condition does not match then empty, i.e. null)
If Sales does not exceed 15000 then those values are represented by "Average" since we defined the else part.
3) If then else - more than 2 Possibilities
if [Condition 1 Match] then [Value1]
elseif [Condition 2 Match] then [Value 2]
elseif [Condition 3 Match] then [Value 3]
elseif [Condition 4 Match] then [Value 4]
.....................
Else [Value N] end
EX:
If sum([Sales]) >=15000 then "Good"
elseif sum([Sales]) >=10000 and sum([Sales]) <15000 then "Average"
elseif sum([Sales]) >=5000 and sum([Sales]) <10000 then "Below Average"
else "Bad" end
=============================
Case <EXP>
when <Matches 1> then [Value 1]
when <Matches 2> then [Value 2]
when <Matches 3> then [Value 3]
Else [Value 4] end
Case [Region]
when "Central" then "Territory - 1"
when "East" then "Territory - 1"
else "Territory - 2" end
EX:
1) I want to Apply Different colors to Regions as per Sales Value.
<10 K - Color 1
>=10 K And < 15 K then Color 2
>=15 K And < 25 K then Color 3
or Color 4
2) Difference between And , Or Operators in the Calculations.
Create few reports with BUZ Requirement.
3) I want to see 2013, 2014 data in 1 color, 2011, 2015 in another color.
(Don't use the Editing Color Legend option for applying colors. Use Calculations to define colors)
4) Categorize the City's into different groups by using your own business situation.
5) I want to apply Different shapes as per Sales Values.
Use Question -1 as a situation & define the shapes.
Note:
As of now Tableau does not have "IN" operator in the Calculations but there is an alternative approach that is "OR"
Case [Region]
when "Central" then "Territory - 1"
when "East" then "Territory - 1"
else "Territory - 2" end
Or
If [Region] = "Central" or [Region] ="East" then "Territory - 1"
else "Territory - 2" end
====================Day - 8============
Hierarchies:
We use Hierarchies to organize the data
from Top Level information to Bottom level Information.
Top Level Information holds the Summary data, Bottom Level holds detail level data.
Sum Up the bottom level data to get next level data.
Creating Hierarchies: Geographical
Before start creating Hierarchies, define no. of level &
Which level data holds in which column.
The following are sequence of levels
Region -> State -> City
Right click on Region, click on "Create Hierarchy".
Define the Name of the Hierarchy as "Location", then Add the Region to this Hierarchy.
Then right click on "State" then click on "add to Hierarchy" then click on "Location".
Add the rest of the levels to the hierarchy.
****************************************
Changing Default Properties:
-> We can change the Default properties like Color, Shape, Aggregations,
Comments, Aliases, Sort, Number Format.
EX:
1) Drag the [Region], [Sales] onto the Work Area.
2) [Sales] takes the Sum as default aggregate function.
3) Right click on [Sales] in measures window, click on Default Properties,
click on "Aggregation", select "AVG"
4) Again drag the [Sales], it takes "AVG" as default aggregate.
****************************************
Defining Starting Month of Year(Defining Fiscal Year)
Generally Calendar Years starts with Jan Month,
Indian Fiscal year starts with Mar Month.
-> Right click on "database", click on Date Properties, click on "Fiscal Year Start"
then define the starting month of the year.
****************************************
Adding external Images to the Report
1) Basically Images are stored at Documents\My Tableau Repository\Shapes
2) We can add external images in this area.
3) Create a folder, add images into this folder.
4) Come to the report, Drag any Dimension into the Work Area, Then select "Shapes" as the mark.
5) Then edit the Shapes , then Reload the Shapes in the view.
6) Then Select the New folder which was created in previous steps.
7) Then you can assign the Shapes accordingly.
Parameters – Parameters are dynamic variables that
can be used as placeholders in formulas.
Replacing the Static values by Dynamic Values.
EX: Dynamically selecting Columns (Fields), Dynamically
selecting Top 10,
Bottom N
EX: Dynamically Select Measure Field.
1) Right click in the dimension window, click on Create Parameter.
2) Define the Type: String
3) Select List option since we are defining manually.
Sales
Profit
4) Create a calculated field as below
if [Parameter] = "Sales" then [Sales] else [Profit] end
5) Drag the Region onto the Row shelf, drag the above calculation into the Columns shelf.
6) Right click on Parameter, click on "Show Parameter Control".
7) Now we can Select Either [Sales] or [Profit].
Note: As of now in Tableau we can select 1 Value max at a time.
1) Dynamically select the Colour.
Sol:
Create a Parameter “Select - Colors - Dynamically” with the Following values like the below screenshots. Green, Blue, Red, Orange
Drag Region onto Row Shelf, Sales onto Column Shelves.
Create a Calculated field like the below
Name: Dynamic Colour
Syntax:
case [Color]
when "Green" then "Green"
when "Blue" then "Blue"
when "Red" then "Red"
when "Orange" then "Orange"
end
Here [Color] is the Parameter which was created in the 1st step.
4) Drag “Dynamic Colour” onto Colors mark.
5) Right click on “Color” parameter, click on “Show Parameter Control”.
6) Now select colors 1 by 1 from “Parameter” then assign the right colors.
Parameter - Options:
1) If you to show parameter values from any specific column, Select List Radio Button
from Allowable Options then select Add from Field, then select the Field.
2) If you want to create a Parameter on specific column, right click on that,
click on "Create Parameter".
Updating Values in Quick Filters, Parameters:
If you are creating Quick Filter on any specific field, it will show all possible values as per current data availability. (Distinct Values).
Once you update the database, the Quick filter won't update directly, we have to use one option on the Menu Bar, select "Auto updates Quick filters".
Updating Parameter Values:
Values of Parameter will not be changed once Database has been updated, So manually we have to open the Parameter, then Reload the values from that Specific field.
*********************************************************
Group -
A group is a combination of dimension members that make higher level categories.
EX:
We are having data in columns Country, State, City, we want to create
Region it is between State & Country.
We create Regions by using group mechanisms on States.
Create a group of Customer Name Field,
All customers whose names are starts with "A", Define name "A"
Similarly All customers whose names are starting with "B", Define name "B" ........
New Column will be added in the Dimensions pane.
We can edit the group and add members manually.
***********************************************************
Sets -
Sets are custom fields that define a subset of data based on some conditions. Computed sets update as data is being changed.
-> Alternatively, a set can be based on a specific data point in your view.
Sets are subset of data, which are derived from either only Dimension or combination
of Dimension & Measures.
We use sets to apply set functions on the Data, like Union, Minus, Intersection
Union: Combine data from both sets.
Minus: Data which present in one set but not present in another set.
Intersection: Common data between two data sets.
EX:
1) Right click on Region, click on "set".
2) Click on condition "By Field", Select the measure, define condition.(Sales>=10000).
3) Click on "OK".
4) Set has been created, it shows the list of customers whose sales are more than 10K.
EX: Combine sets
We can use the sets for set analysis.
Set 1 = Top 5 Customers
Set 2 = Bottom 5 Customers
Use Case:
Combination of Top 5, Bottom 5
Set 1 Union Set2
***********************************************************
Bins:
Measures are columns that typically contain numeric data,
such are used to evaluate the BUS or to Analyze the BUZZ.
For Histograms we use Bins. We use it to know the distribution of numerical data.
EX: Gaussian Distribution, Left Skew Distribution, Right Skew Distribution.
EX: Sales , Profit.
We use the bins to categorize the measures into different groups by
using measure figures.
Axes: Axes are created when you place a measure on the Rows or Columns shelf.
By default, the values of the measure field are displayed along
a continuous axis.
1) ASCII:
It returns first letter of String's ASCII
ASCII(“Tableau”) = 84
2) Char:
This function converts numerical numbers into characters using given input number then mapping to the ASCII values list.
EX:
char(65)= A
3) Contains:
By using this function, we can find whether any specific word (substring)is contained in another column (String). It gives output as BOOLEAN, so we can filter "True".
This example is checking whether customer names contain the letter "A" irrespective of the position of that "A".
EX:
Region = South
contains([Region], "st") = False
contains([Region], "k") = False
contains([Region], "th") = True
contains([Region], "SO") = False
contains('abcd', 'ab') = True
contains('abcd', 'xy') = False
4) ENDWITH:
It will check whether the substring is endswith or not in the main string.
It means it gives the Outputs as True/False
EX:ENDSWITH( [Region], 'st' )
Region = South
endwith([Region], "th") = True
endwith([Region], "k") = False
5) Find:
It will check the position of the substring in the main string.
It means it gives the Outputs as Numerical Numbers.
Region = South
Find ([Region], "th") = 3
Find ([Region], "k") = 0
6) Findnth:
This function verifies the string & gives specified substring position
for nth occurrence.
Findnth(“developer”,1,’e’)
Findnth(“developer”,’e’,1) = 2
Findnth(“developer”,’e’,2) = 4
Findnth(“developer”,’pe’,2) = 7
7) Isdate:
This FUNCTION is used to test the given string data, if the given data is date then returns “True” else false.
EX: isdate(‘[Order_Date]’) = Flase
Isdate(’12-Feb-2011’) = True
Isdate(’29-Feb-2011’) = Flase # Lear Year
Isdate(’29-ABC-2011’) = Flase
Isdate(’2011-Dec’) = True
8) LEFT:
We use this function to fetch substring from starting point to
specific point in the given Main String.
EX: Left([Customer Name],3)
Region = South
Left([Region],2) = So
Left([Region],4) = Sout
9) LEN:
This function counts the number of characters including nulls & returns Numerical numbers.
10) MAX:
Test & returns Maximum as output., It takes the first character of a given string then finds the ASCII value with another string’s first character then returns the maximum string as the output.
Max(“A”, “B”)= B
Max(“AAAA”, “D”)= D
Max(“ACA”, “aZ”)= ‘aZ’
11) Min:
This function checks it gives minimum number as output as per
first character’s ASCII value.
EX: Min ('A', 'B') = A
12) MID:
This function is used to extract the substring from specific point
to a specific point.
EX: Region = South
Mid( [Region], 2,4) = outh
MID(MAIN STRING, STARTING POSITION, NO.CHAR WANT)
13) Replace:
This function is used to replace the substring by another substring.
EX: replace([Region], “th”, “th Carolina”) = South Carolina
14) Right:
This function fetch the data up to specified number of positions
from the right side.
right('Tableau', 3) = 'eau'
15) RTRIM:
This function removes empty space after the word, which means the right side is empty space.
16) Space:
This function is used to add the space in the word &
defined number of times. we can add extra space either at the starting position of the string i.e. as prefix or at the end of the string i.e. as suffix.
EX:
space(2) +”ABCD” = “ ABCD”
”ABCD” + space(2) = “ABCD ”
17) Stars with:
This function tests whether string starts with specified substring &
returns Boolean If it is true.
EX:
startswith('Tableau', 'asd') = False
startswith('Tableau', 'Tab') = True
startswith('Tableau', 'T') = True
startswith('Tableau', 'able') = False
18) Trim:
This function removes extra space of string for left & right edges of the given string.
EX:
trim(‘ ASDF ‘) = ‘ASDF’
trim(‘ ASDF‘) = ‘ASDF’
trim(‘ASDF ‘) = ‘ASDF’
19) Upper:
It converts the given string into Upper Case.
=============== Day- 10 ================
In today’s session we will discuss Date Calculation, How to create calculations using Date Dimension.
Tableau supports the following Date formats.
Tableau Supported the following parts of the Date.
DATE_PART VALUES
'year' Four-digit year
'quarter' 1-4
'month' 1-12 or "January", "February", and so on
'dayofyear' Day of the year; Jan 1 is 1, Feb 1 is 32, and so on
'day' 1-31
'weekday' 1-7 or "Sunday", "Monday", and so on
'week' 1-52
'hour' 0-23
'minute' 0-59
'second' 0-60
Dateadd:
We use this function to perform Date Calculations
Like addition or subtraction at different levels.
Levels: Year, Quarter, Month,.........
EX: dateadd('month', 2, #12-Jan-2014#)
O/P: 12-Mar-2014
This View: Scenario:
Generally once Place the Order, we deliver after 2 Months,
So I want to see Delivery Date for Each Order.
Process:
1) Right click on [Order Date], click on "Create Calculated Field"
2) Define the Name Syntax as below.
Name: Deliver Date
Syntax: dateadd('month', 2, [Order Date])
3) Then add the [Order Date], [Deliver Date] to report.
4) Now the report will show the Data at Year level. If you want to see in terms of Full date, Right click on [Order Date], click on "More" option then click on "Custom" then select "Month/Day/Year" format from the Drop down List.
*************
Datediff:
We use this function to find the difference between two DATES in terms of specified date parts like Year, Month, Date and it gives the O/P in integers.
Once created the Calculated field, drag that into the Dimension window.
EX:
syntax: DATEDIFF('day',[Order Date],[Ship Date] )
This View: Scenario:
I want to see How many days does take to deliver each Order ( Using Sample Database)
1) Right click on [Order Date], click on "Create Calculated Field".
2) Define the Name Syntax as below.
Name: Days taken for Deliver
Syntax: DATEDIFF('day',[Order Date],[Ship Date] )
3) Then add the [Order Date], [Days taken for Deliver] to report.
4) Now you can find the [Order Date], [Days taken for Deliver] on the report . Days taken for Deliver shows Numerical Number.
***************************
Datename
We use this function to extract specific parts of the Date, It gives O/P as string.
EX:
1) datename('year', #12-Jan-2014#)
O/P: 2014
2) datename('month', #12-Jan-2014#)
O/P: Jan
3) datename('weekday', #12-Jan-2014#)
O/P: Sunday
**************
Datepart
We use this function to extract specific part of the Date,
It gives O/P as Numerical Data.
EX:
1) datepart('year', #12-Jan-2014#)
O/P: 2014
2) datepart('month', #12-Jan-2014#)
O/P: 1
3) datepart('weekday', #12-Jan-2014#)
O/P: 1
****
Converts a string to a datetime in the specified format.
EX:
DATEPARSE ("dd.MMMM.yyyy", "15.April.2014") = #April 15, 2014#
DATEPARSE ("h'h' m'm' s's'", "10h 5m 3s") = #10:05:03#
***********
Datetrunc
We use this function to get the First Date of a given Date at a Defined Level. (Opening Date)
EX:
1) Datetrunc('year', #12- Feb-2014#)
O/P: 1-Jan-2014
2) Datetrunc('month', #12- Feb-2014#)
O/P: 1-Feb-2014
Week starts with SUNDAY.
This View: Scenario:
I want to see what is the Day of the Year for Each Transaction ( Days are 365 or 364)
1) Create a Calculated field like below to get the First Day of the Year for Each Order.
Name: Datetrunc - Year
Syntax: DATETRUNC('year',[Order Date])
2) Create another Calculated Field
Name: Day of the Year
Syntax: datediff('day', [Datetrunc - Year],[Order Date])
3) Drag the Fields [Order Date], [Day of the Year], [Datetrunc - Year] onto the Work Area.
***************
Day, Month, Year
-> We use these functions to extract the Specific part from the Date. It means Year, Day, Month. And it gives Integer as Output.
-> Once you create a calculated field using any of these functions, that will be moved into the Measures window since this function gives O/P in Number Data type.
EX:
1) day(#12-Jan-2014#)
O/P: 12
2) month(#12-Jan-2014#)
O/P:1
3) year(#12-Jan-2014#)
O/P:2014
5) Now you can find the [Order Date], [Deliver Date] on the report with "Month/Day/Year" format.
Table Calculations:
Table calculations are computations that are applied to the values in the table.
These Computations are unique in that they use data from multiple rows in the database to calculate a value. To create a table calculation, you need to define both what values you want to compute and what values to compute along. These are defined in the Table Calculation dialog box using the Calculation Type and Calculate Along drop-down menus.
Note:
Table Calculations are applied to 1 Measure at a time, if we want to apply on multiple measures we need to apply multiple times.
Key Notes & Definitions:
Addressing & Partitions are two parts in the Table Calculations.
Addressing:
The dimensions that define the part of the table you are applying the calculation to are called addressing fields.
The fields which are used for Calculation purposes.
Partitions:
The dimensions which are used to perform the groupings are called “Partition”.
The fields which are used for Grouping purposes.
Table Down:
Calculation will take place from top to bottom (Vertically).
Table Across:
Calculation will take place from Left to Right (Horizontally).
Here we can perform different kinds of Calculations.
-------------------- Classifying Fields - Discrete vs Continuous ----------------------
Discrete vs Continuous:
Discrete: Dimension - Blue Color
Continuous:Measure - Green Color
Date Filed Discrete - as Dimension & Bar Graphs
Date Filed Continuous - as Measure & Line chart
Granularity - Level detail of data
Date: Year(Low Granularity) -> Quarter -> Month -> Week -> Day(High Granularity)
Date: Country(Low Granularity) -> Region -> State -> City(High Granularity)
-------------------- Classifying Fields - Discrete vs Continuous --------------------
Data Blending
We use the Data Blending process when data is located into multiple databases. It is horizontal merging; it means Databases have different columns apart from common columns for defining the relationship.
Example:
The following example illustrates how to use the two data sources to build the Report.
1) Connect the Tableau with "ABC" database, Then connect to Database "XYZA" by clicking on "Connect to database"
2) Once we connect to database 2, we have to define the relationship between 2 databases. Here Column "A" is common to define the Relationship.
3) Click on Data on the menu bar, Click on "Edit Relationship", Then Select Primary & secondary databases then define the relationship between those 2 databases.
Now we can use 2 databases to build the reports. Once we define the Relationship between 2 databases, Primary Database will be highlighted in Blue, Secondary in Orange color.
If the relationship will be removed, then we get the nulls when use columns.
from 2 databases.
======================================
How can show the Data when data is located 3 different sheets in the single XL Workbook
Procedure:
Below are the XL sheets, which have data for 3 Countries in 3 Sheets.
1) All 3 Sheets are located in a single EXCEL Workbook.
2) Connect XL workbook (Sheet 1) to Tableau.
3) Then Extract the Data.
4) Connect to the Same XL workbook Sheet2.
5) Define the Relationship Between DB1 & DB2. Then Right click first DB Extract, click on "Append data from data source". Then refresh Extract
6) Now we are having data from 3 Countries (Which are located in 3 XL sheets in 1 Workbook.)
======================================================================
Data Blending: connect to Multiple DB (Columns Level Mapping, It is Column level Merging)
How to connect to Multiple DB
Either Homogeneous: Same kind of DB like Sales (Oracle), Orders (Oracle)
or Heterogeneous : Different Kind of Databases Like Sales (Oracle), Orders (XL)......
)
1) Sales to Profits
2) Sales to Orders
Blue - Primary
Orange - Secondary
DB1
CNT Sales
DB2
CNT Orders
Data Blending:(Column Level Merging)
CNT, Sales, Orders
DB3
CNT Profits
1) CNT Sales, Orders Profits (Horizontal Merging, It is Right Approach) : Data Blending
2) CNT Sales ( It is not Right Approach)
Orders
Profits
Data Appending: (Record Level Merging)
DB1
CNT State Sales
A A1 100
A A2 200
A A3 300
DB2
CNT State Sales
B B1 100
B B2 200
B B3 300
Data Appending: (Principle: Metadata should be same in all Databases, It is Record level Merging)
CNT State Sales
A A1 100
A A2 200
A A3 300
B B1 100
B B2 200
B B3 300
DB1
CNT State Sales
A A1 100
A A2 200
A A3 300
DB2
CNT State Sales
B B1 100
B B2 200
B B3 300
==============================
Data Appending is not Possible since Sequences of the Columns are not the same in 2 Databases.
CNT State Sales
B B1 100
B B2 200
B B3 300
State Sales CNT
A1 100 A
A2 200 A
A3 300 A
==============================
Data Appending is not possible since Column names are not the same in 2 Databases.
CNT State Sales
B B1 100
B B2 200
B B3 300
CNT States Sales
A A1 1000
A A2 2000
A A3 3000
==============================
Data Appending is not Possible since Metadata is not same in 2 Databases.
CNT(Char 20) State(Char 20) Sales(Integer 12)
B B1 100
B B2 200
B B3 300
CNT(VarChar 20) States(Char 20) Sales(Integer 12)
A A1 1000
A A2 2000
A A3 3000
==============================
Data Appending is POSSIBLE since Metadata, Sequence of the Fields are same in 2 Databases.
CNT(VarChar 20) State(Char 20) Sales(Integer 12)
B B1 100
B B2 200
B B3 300
CNT(VarChar 20) States(Char 20) Sales(Integer 12)
A A1 1000
A A2 2000
A A3 3000
After Implement the Data Appending:
CNT State Sales
A A1 100
A A2 200
A A3 300
B B1 100
B B2 200
B B3 300
Data Appending Principle: Metadata, Sequence of the Columns should be same.
Meta Data: Column Name, Data Type, Size)
Sets
Sets - are custom fields that define a subset of data based on some
conditions. Computed sets update as data is being changed.
-> Alternatively, a set can be based on a specific data point in your view.
Sets are subset of data, which are derived from either only Dimension or combination
of Dimension & Measures.
EX:
1) Right click on Region, click on "set".
2) Click on condition "By Field", Select the measure, define condition.(Sales>=10000)
3) Click on "OK".
4) Set has been created, it shows the list of customers whose sales are more than 10K.
EX: Combine sets
1) We can use the sets for set analysis.
Set 1 = Top 3 Customers
Set 2 = Bottom 3 Customers
2) Combination of Top 3, Bottom 3
Set 1 Union Set 2
3) Right click on Set 1 then click on "Combine Sets"
then select option "Union"
Additional Points:
1) We can use Parameters instead of Top 3 as Top N.
And We can use Parameters instead of 10k.
2) We use sets for set Analysis like Union, Intersect, Minus.
File Extension Types in TABLEAU
Workbooks (.twb) – Tableau workbook files have the .twb file extension and are marked with the workbook icon. Workbooks hold one or more worksheets and dashboards.
• Bookmarks(.tbm) – Tableau bookmark files have the .tbm file extension and are marked with the bookmark icon. Bookmarks contain a single worksheet and are an easy way to quickly share your work.
• Packaged Workbooks (.twbx) – Tableau packaged workbooks have the .twbx file extension and are marked with the packaged workbook icon. Packaged workbooks contain a workbook along with any supporting local file data sources and background images. This format is the best way to package your work for sharing with others who don’t have access to the data.
• Data Extract Files (.tde) – Tableau data extract files have the .tde file extension and are marked with the extract icon. Extract files are a local copy of a subset or entire data source that you can use to share data, work offline, and improve database performance.
• Data Connection Files(.tds) – Tableau data connection files have the .tds file extension and are marked with the data connection icon. Data connection files are shortcuts for quickly connecting to data sources that you use often.
Adding external Images to the Report
1) Basically Images are stored at Documents\My Tableau Repository\Shapes.
2) We can add external images in this area.
3) Create a folder, add images into this folder.
4) Come to the report, Drag any Dimension into the Work Area, Then select "Shapes" as the mark.
5) Then edit the Shapes , then Reload the Shapes in the view.
6) Then Select the New folder which was created in previous steps.
7) Then you can assign the Shapes accordingly.
======================
Create a new Folder in the Shapes Folder, then add your shapes.
2) Then Reload the Shapes in the view.
3) Then Select the New folder which was created in previous steps.
4) Then you can assign the Shapes accordingly.
Now we can show New Shapes on the Report.
Extracting Data
Extracts are saved subsets of a data source that you can use to improve performance, upgrade your data to allow for more advanced capabilities, and analyze offline. You can create an extract by defining filters and limits that include the data you want in the extract. After you create an extract you can refresh it with data from the original data source. You can either fully refresh the data, replacing all of the extract contents; or you can increment the extract; which only adds rows that are new since the last refresh.
Extracts can:
Improve performance. For file based data sources such as Excel or Access, a full extract takes advantage of the Tableau data engine. For large data sources, a filtered extract can limit the load on the server when you only need a subset of data.
Add functionality to file based data sources, such as the ability to compute Count Distinct.
Provide offline access to your data. If you are traveling and need to access your data offline, you can extract the relevant data to a local data source.
Extracts are saved with.tdeextension.
Creating an Extract
Select a data source on the Data menu and then select Extract Data to open the Extract Data dialog box.
Optionally define filters to limit the data that will be extracted. Any fields that are hidden in the Data window will be automatically excluded from the extract. Click the Hide All Unused Fields button to quickly remove them from the extract.
To add filters, click the Add button under the Filters list.
============== ============================
Using Extracts
After you create an extract, the current workbook begins using the extract. However, the extract connection is not saved with the workbook until the next time you save. That means, if you close the workbook without saving first, the workbook will connect to the original data source the next time you open it.
You may want to create an extract with a sample of the data so you can set up the view and then switch to the whole data source, thus avoiding long queries every time you place a field on the shelf. You can toggle between using the extract and using the entire data source by selecting a data source on the Data menu and then selecting Use Extract.
You can remove an extract at any time by selecting a data source on the Data menu and then selecting Extract > Remove. When you remove an extract you can choose to Remove the extract from the workbook only or Remove and delete the extract file, which will delete the extract from your hard drive.
You can see when the extract was last updated and other details by selecting a data source on the Data menu and then selecting Extract > History.
Refreshing Extracts
When the underlying data changes, you can refresh the extract by selecting a data source on the Data menu and then selecting Extract > Refresh. Extracts can be configured to be fully refreshed, replacing all of the data with what’s in the underlying data source, or incrementally refreshed, adding just the new rows since the last refresh.
Full Extracts
By default, extracts are fully refreshed. That means that every time you refresh the extract, all of the rows are replaced with the data in the underlying data source. While this kind of refresh ensures you have an exact copy of what is in the underlying data source, it can sometimes take a long time and be expensive on the database depending on how big the extract is.
If the extract is not set up for incremental extract, selecting refreshing the extract will fully refresh the extract. If you’re publishing the data source to Tableau Server, you can specify the type of refresh in the Scheduling & Passwords dialog box.
Incremental Extracts
Rather than refreshing the entire extract, you can set it up to only add the rows that are new since the last time you extracted data. For example, you may have a data source that is updated daily with new sales transactions. Rather than rebuild the entire extract each day, you can just add the new transactions that occurred that day. Then once a week you may want to do a full refresh just to be sure you have the most up to date data.
Follow the steps below to set up an extract to be incrementally refreshed.
Select a data source on the Data menu and then select Extract.
In the Extract Data dialog box, select All rows as the number of Rows to extract. Incremental refresh can only be defined when you are extracting all rows in the database. You cannot increment a sample extract.
Select Incremental refresh and then specify a column in the database that will be used to identify new rows. For example, if you select a Date field, refreshing will add all rows whose date is after the last time you refreshed. Alternatively, you can use an ID column that increases as rows are added to the database.
When finished, click Extract.
The steps above can be used to define a new extract or configure an existing extract for incremental refresh. If you are editing an existing extract, the last refresh is shown so you can be sure you are updating the extract with the correct data.
If you publish the data source to Tableau Server you can specify a schedule for incremental refresh as well as full refresh in the Schedules & Passwords dialog box.
Extract History
You can see a history of when the extract was refreshed by selecting a data source on the Data menu and then select Extract > History.
The Extract History dialog box shows the date and time for each refresh, whether it was full or incremental, and the number of rows that were added. If the refresh was from a file, it also shows the source file name.
Adding Data to Extracts
There are two ways you can add new data to an extract: from a file or from a data source. However, to add new data, the columns in the file or data source must match the columns in the extract.
Add data from a file
You can add new data to an extract from a file-based data source. Use this option when the file type of the extract is the same asthe file type of the data that you want to add. Alternatively, you can add data from a Tableau data extract (.tde) file. For example, you may have text files that are generated for a task that is performed every day. To add each day's worth of information to your extract whose original data source is also a text file, use the Append Data from File command.
To add data from a file
On the Data menu, select a data source, and then select Extract > Append Data from File.
Browse to and select the file that has the new data.
Note: By default, the file format of the extract's original data source is used. To add data from a Tableau data extract, click the file format drop-down list, and then select Tableau Data Extract (.*tde).
When finished, click OK.
Add data from a data source
You can also add new data to an extract from another data source in the workbook. Use this option when the file type of the extract is different from the file type of the data you want to add. For example, you created an extract from a data warehouse that has the past ten years worth of data. However, new data has been kept in an Excel workbook. You can add new data to the extract by using the Append Data from Data Source command.
Note: Joins or custom SQL should be specified in the data source before adding data to the extract.
To add data from a data source
On the Data menu, select a data source, and then select Extract > Append Data from Data Source.
In the dialog box, select the data source that you want to append.
When finished, click OK.
Using either option will add new rows to the extract. To see a summary of the number of rows that were added, select a data source on the Data menu and then select Extract > History.
Note: When you refresh this extract, the data will be replaced with the data from the original data source.
Upgrading Legacy Extracts
If you have data extracts that were created before version 6.0, you should upgrade the extracts to use the data engine. When you open the workbook, you are given the option to upgrade the extracts.
You can also upgrade the extracts by selecting a data source on the Data menu and then selecting Upgrade Extract.
Optimizing Extracts
To improve performance when working with extracts you can optimize the extract. Optimizing an extract creates a secondary structure in the extract that speeds up future queries.
Optimize the extract by selecting a data source on the Data menu and then selecting Extract > Optimize.
The following types of optimizations are made:
Materialized Calculated Fields
Calculated fields are computed in advance and stored in the extract. In future queries, Tableau can look up the already computed value rather than running the computation again. The following types of calculated fields ARE NOT materialized:
Calculations that use unstable functions such as NOW() and TODAY()
Calculations that use external functions such as RAWSQL and R
Table calculations
In addition, if the formula for a materialized calculation changes or the calculation is deleted from the data source, the materialized calculation is dropped from the extract until the extract is optimized again.
Acceleration Views
When a workbook contains filters that are set to show only relevant values, computing the available values for that filter can be an expensive query. For these filters, Tableau must evaluate the other filters in the workbook first and then compute the relevant values based on their filter selections. To speed up these queries, a view can be created that computes the possible filter values and caches them for faster lookup later.
Refreshing Extracts
Before you attempt to update an extract, verify the following:
The data source was originally published as an extract.
Tableau Desktop is connected to the published data source, as indicated by the Tableau Server icon next to the data source name in the Data window:
To refresh an extract on Tableau Server or Tableau Online from Tableau Desktop, right-click the data source in the Tableau Desktop Data window, select Tableau Data Server, and choose one of the following options:
Refresh from Source
Refreshes the extract (full or incremental) using the data in the original data source.
This command is available only for extracts that include a connection to the original data source. If you connected directly to a Tableau Data Extract file (.tde) and then published it, the connection to the original data source is not included.
Append Data from Data Source
Updates the extract from another data source in the workbook.
Append Data from File
Updates the extract from the contents of a file if the original data source type of the extract is the same file-based data source or a Tableau data extract (.tde) file.
Note: If you do not see the Tableau Data Server option, your data source may not be on Tableau Server or Tableau Online (in which case it will not show the icon above). If you see the Tableau Data Server option, but both commands are unavailable, the data source exists on the server, but it is not an extract.
Dashboard: General Definition:
A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored.
In Tableau, We use Tableau to place multiple Reports at a single place as well as to define the relationship from one report to others.
Creating a Simple Dashboard:
1) Create a Report with Region, Sales fields and Name it as "Region Sales"
2) Create another Report with State, Sales Fields and Name it as "State Sales"
3) Create a Dashboard by click on Dashboard menu, then click on "Create New Dashboard"
4) Then Drag the "Region Sales", "State Sales" report into Dashboard.
5) Now the Dashboard has 2 Reports.
Objects in Dashboard:
1) Horizontal: it is used to place the organize the reports horizontally.
2) Vertical: it is used to place the organize the reports Vertically.
3) Image: We can add Image to the Dashboard.
EX: Drag the Image object to the work Area, then provide the path of the Image.
4) Webpage: It is used to add the Web Page to Dashboard.
EX: Drag the Webpage object to the work Area, then provide the URL.
5) Blank: It is used to keep space between two Reports on the Dashboard.
6) Text: It is used to add the textual data to the Dashboard.
Actions :
Actions, which are used to define the interaction between reports.
Action Types:
1) Filter: Action Filters are used to define the Filter from 1 report to another, it means whichever we use in one report that related data will only show in other report.
EX:
1) Create a Report with Region, Sales fields and Name it as "Region Sales".
2) Create another Report with Region,State, Sales, Profit Fields and Name it as "State Sales"
3) Create a Dashboard by clicking on the Dashboard menu, then click on "Create New Dashboard".
4) Then Drag the "Region Sales", "State Sales" report into Dashboard.
5) Now the Dashboard has 2 Reports.
6) Click on the "Dashboard" file menu, then click on "Actions" , then on "Filter".
7) Select "Region Sales" as Source Sheet, "State Sales" as Target Sheet.
8) Select Run on as "Select"
9) Click on "OK".
10) Click on any of the Region in "Region Sales" report, then we can see selected Region related Data in the "State Sales".
3) Highlight:
Highlight option is used to highlight the selected data related to other reports.
EX:
1) Create a Report with Region, Sales fields and Name it as "Region Sales"
2) Create another Report with State, Sales Fields and Name it as "State Sales"
3) Create a Dashboard by click on Dashboard menu, then click on "Create New Dashboard"
4) Then Drag the "Region Sales", "State Sales" report into Dashboard.
5) Now the Dashboard has 2 Reports.
6) Click on "Dashboard" file menu then click on "Action" then click on "Highlight".
7) Select "Region Sales" as Source Sheet, "State Sales" as Target Sheet.
8) Select Run on as "Select".
9) Click on "OK".
10) Click on any of the Region in "Region Sales" report, then we can see selected Region related Data in the ”State Sales" will be highlighted.
3) URL:
We use this kind of Actions to navigate from Report to any specific webpage.
1) Create a Report with Region, Sales fields and Name it as "Region Sales".
2) Click on "Dashboard" file menu then click on "Action" then click on "URL".
3) Then Define "https://www.google.co.in/" in the URL, click on "OK".
4) click on any of the regions in the report, it will take you to the google page.
========================
Run Action On:
We use "Run Action On" to define the User interaction with the report. After defining the Action type, we define Run Action On.
Run Action Types:
1) Hover: We use this type to define the interaction from 1 report to another, in this approach action will be applied by keeping mouse hover on the Source Report.
2) Select: We use this type to define the interaction from 1 report to another, in this approach action will be applied when select (Click) any data in the Source Report.
3) Menu:We use this type to define the interaction from 1 report to another, in this approach action will be applied when click on data then click on menu bar in the Source Report.
Creating Dashboards:
Navigating with in the Same Dashboard
1) Create a Report with Region, Sales fields and Name it as "Region Sales"
2) Create another Report with State, Sales Fields and Name it as "State Sales"
3) Create a Dashboard by click on Dashboard on menu bar, then click on "Create New Dashboard", Right click on Dashboard at footer level then Rename as " Region, State Sales Dashboard".
4) Then Drag the "Region Sales", "State Sales" report into Dashboard.
5) Now the Dashboard has 2 Reports.
6) Click on "Dashboard" menu bar then click on "Action" , then on "Filter"
7) Select "Region Sales" as Source Sheet, "State Sales" as Target Sheet.
8) Select Run on as "Select"
9) Click on "OK".
10) Click on any of the Region in "Region Sales" report, then we can see selected Region related Data in the "State Sales".
Navigating from one Dashboard to another.
1) Create a Report with Region, Sales fields and Name it as "Region Sales"
2) Create another Report with State, Sales Fields and Name it as "State Sales"
3) Create a Dashboard by clicking on Dashboard on the menu bar, then click on "Create New Dashboard", Right click on Dashboard at footer level then Rename as “Region Sales Dashboard". Then Drag "Region Sales" report into the work area.
4) Create a Dashboard by clicking on Dashboard on the menu bar, then click on "Create New Dashboard", Right click on Dashboard at footer level then Rename as "State Sales Dashboard". Then Drag "State Sales" report into the work area.
5) Now there are 2 Dashboards.
6) Go to “Region Sales Dashboard" menu bar then click on "Action" , then on "Filter"
7) Select "Region Sales" as Source Sheet from Region Sales Dashboard, "State Sales" as Target Sheet from .State Sales Dashboard.
8) Select Run on as "Select"
9) Click on "OK".
10) Click on any of the Region in "Region Sales" report, then we can see selected Region related Data in the "State Sales".
Note:
1) We can define the Size of the view in the Dashboard by selecting the Sizes which are located in the Left bottom side.
2) When we drag the Reports into the Dashboard, it occupies the total space of the Dashboard. If you want to define the size then select the "Float" option then we can define the Size of the Report in the Dashboard.
Float: it is used to move reports from 1 place to another place within the Dashboard and can define the custom size.
Tiled: It is fixed size, occupies maximum size in the Dashboard, If we place 2 reports in Dashboard, that 2 reports occupies total space of the Dashboard.
3) Text Object: We use if only to add the comments or to show any textual data along with Report in the Dashboard.
"State Sales".
Note:
1) We can define the Size of the view in the Dashboard by selecting the Sizes which are located in the Left bottom side.
2) When we drag the Reports into the Dashboard, it occupies the total space of the Dashboard, if you want to define the size then select the "Float" option then we can define the Size of the Report in the Dashboard.
Float: it is used to move reports from 1 place to another place within the Dashboard and can define the custom size.
Tiled: It is fixed size, occupies maximum size in the Dashboard, If we place 2 reports in Dashboard, that 2 reports occupies total space of the Dashboard.
3) Text Object: We use it only for adding comments or to show any textual data along with a Report in the Dashboard.
Table Calculations:
These Calculations are a special type of Calculations in the Tableau, we use these calculations to create calculations over calculations to get the more detailed level analysis. For example we calculate the Sum of sales by Year then we use Table Calculations to see Year over Year Growth or What is the Contribution of each Year when we compare with other years within the analysis.
Important Terminologies:
Addressing:The fields which are used to create Table Calculations is called Addressing. (The dimensions that define the part of the table you are applying the calculation to are called addressing fields.)
Partition:The fields which are used to split into pieces are called Partition.(The dimensions which are used to perform the groupings are called “Partition”.
The fields which are used for Grouping purposes.)
Pane: Once divide the entire view into pieces, then each piece is called a pane.
Down: Calculation will take place from top to bottom direction (Vertically).
Across:Calculation will take place from Left to Right direction (Horizontally).
Calculations: We use different kinds of calculations in Table Calculation, the following are lists of Calculations.
Running Total
Difference from
Percentage Difference
Rank
Percentage of Total
Moving Average
In the following example, Years on Columns, Products on Rows.
Row - Product - Across - Left to Right (Direction)
Column - Year- Down - Top to Down (Direction)
In this Example, We are calculating Running Total using Sales, Region.
In this Example, We are calculating Percentiles using Sales, Region.
In this Example, We are calculating ranks using Sales, Region, High Sales is ranked as 1 then followed by next highest Sales is ranked as 2......
Here we are using two Dimensions to Apply the Table Calculations. We are going to use this report to see the results after applying Table Calculations.
Running Total from left to right.
Addressing: Year
Partition: Region
This example shows the running total of each region separately by summing up each year .
Running Total from Top to Bottom
Addressing: Region - Calculation move in this direction
Partition: Year - Calculations start from each Year
This example shows the running total of each year separately by summing up each region.
Difference From Previous Value from Left to Right.
Addressing: Year - Calculation move in this direction
Partition: Region - Calculations start from each Year
Here we are applying Percentage of total(Down) it means calculation over the Region within each Year.
Each Year is a Partition. And calculations are moving from top to bottom. It means Addressing the Region .
—-----------------------------------
Here we are applying Percentage of total(Across) it means calculation over the Years within each Region.
Each Region is a Partition.
Calculations are moving left to right, it means Addressing on Years.
https://help.tableau.com/current/pro/desktop/en-us/calculations_tablecalculations.htm
LOD: Level of Details
We LOD expressions to do the calculations at a very lower granular level or at a higher granular level it depends on the business needs.
Granularity - Level detail of data
Date: Year(Low Granularity = Exclude) -> Quarter -> Month -> Week -> Day(High Granularity = Include)
Geography: Country(Low Granularity= Exclude) -> Region -> State -> City(High Granularity= Include)
In Tableau a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED) and it is similar to group by in SQL.
Fixed:
If we don't specify any dimension for Fixed LOD expressions then we get the calculation at table level.
—----
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting total sales at Region level.
—--- —--- —----
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting total sales at Region & Category level.
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting Max sales at Region & Category level.
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting Min sales at Region & Category level.
If we specify any dimension for Fixed LOD expressions then we get the calculation at specified level.
In this example, we are getting the Number of Records at Region & Category level.
Exclude:
If any dimension is part of the view but we don't want to include in the calculations then we use this function.
If we specify any dimension for Exclude LOD expressions then we get the calculation by ignoring the specified dimension. In this example, we are getting total sales at Region level, even though we have Category in the view since we are Exclude the dimension Category.
If we specify any dimension for Exclude LOD expressions then we get the calculation by ignoring the specified dimension. In this example, we are getting total sales at Region, Segment level, even though we have Category in the view since we are Exclude the dimension Category.
If we specify any dimension for Exclude LOD expressions then we get the calculation by ignoring the specified dimension. In this example, we are getting total sales at Region level, even though we have Category, Segment in the view since we are Exclude the dimension Category, Segment.
Include:
If any dimension is not part of the view but we want to include in the calculations the results in the view then we use this function.
If we specify any dimension for Include LOD expressions then we get the calculation by including the specified dimension. In this example, we are getting AVG sales at Region, Category, Customer level, even though we have only Region, Category dimensions in the view since we are using Include Function along with the dimension Customer Name.
—---------------
If we specify any dimension for Include LOD expressions then we get the calculation by including the specified dimension. In this example, we are getting MAX sales at Region, Category, City level, even though we have only Region, Category dimensions in the view since we are using Include Function along with the dimension City level.
We use charts to convey the message in a simple, understandable and visual way.
There are several kinds of charts offered by Tableau.
Text_Table
These kinds of charts are being used for the data like a table, it includes Zero or more Dimensions & Zero or more Measures.
Steps:
1) Drag Column [ Ship Mode] onto the Column shelf.
2) Drag Column [ Customer Name] onto the Row shelf.
3) Drag Column [ Sales] onto the Text shelf.
4) Select Text Table, from Show Me canvas.
Highlight_Table:
1) These kinds of charts are being used to show the data like a table, it includes Dimensions & Measures. We can highlight the data based on colors.
2) In a glance we can identify which are having more weightage and which are having less weightage in terms of specified measure.
3) It is an Advanced CrossTab view, it has color legend & Size Legend.
4) We can place one or more dimensions and only one measure
Steps:
1) Drag Column [Ship Mode] onto the Column shelf.
2) Drag Column [Segment] onto the Row shelf.
3) Drag Column [Sales] onto the Text shelf.
4) Select Highlight Table, from Show Me canvas.
Heat Maps:
These kinds of charts are being used to show the data like a table, it includes Dimensions & Measures. We can highlight the data based on colors.
The main difference between Heat Map & Highlight tables is that Heat map has one option that Cell size is controlled by measure value.
Steps:
1) Drag Column [ Ship Mode] onto the Column shelf.
2) Drag Column [ Region] onto the Row shelf.
3) Drag Column [ Sales] onto the Text shelf.
4) Select Heat Map, from Show Me canvas.
Line Charts
Line charts are used to show the trends over a period of time. Here we are using the Time Dimension as 'Discrete'.
Steps:
1) Select [Order Date Updated] &[Sales] using the control button and select Line Chart from Show Me canvas.
2) Right click on [Order Date Updated] on the columns shelf, then select 'Discrete'.
Line charts are used to show the trends over a period of time. Here we use the Date field as 'Continuous'.
Steps:
1) Select [Order Date Updated] &[Sales] using the control button and select Line Chart from Show Me canvas.
2) Right click on [Order Date Updated] on the columns shelf, then select 'Continuous'.
3) Drag [Segment] onto the Detail shelf, now the View shows Year wise Sales further breakdown by Segments.
Area Charts:
Area charts are similar to Line charts, we use them to show the trends over a period of time. Here it occupies the space by color. Here we use the Date field as 'Discrete'.
Steps:
1) Select [Order Date Updated] &[Sales] using the control button and select Area Chart from Show Me canvas.
2) Right click on [Order Date Updated] on the columns shelf, then select 'Discrete'.
Area charts are similar to Line charts, we use them to show the trends over a period of time. Here it occupies the space by color. Here we use the Date field as 'Continuous'.
Steps:
1) Select [Order Date Updated] &[Sales] using the control button and select Area Chart from Show Me canvas.
2) Right click on [Order Date Updated] on the columns shelf, then select 'Continuous'.
3) Drag [Region] onto the Color shelf, now the View shows Year wise Sales further breakdown by Regions.
Circle Views:
Circle Views are one kind of chart, we use One More Dimensions and One More measures.
Steps:
1) Select [Category] &[Sales] using the control button and select Circle View from Show Me canvas.
2) Drag [Region] onto the Colors shelf.
Side by Side Circle View
Circle Views are one kind of chart, we use One More Dimensions and One More measures.
Steps:
1) Select [Region] &[Sales] using the control button and select Circle View from Show Me canvas.
2) Drag [Category],[Order Date Updated] onto the Column shelf.
3) Now this view Shows the sales of Category and further divide into Year wise.
4) Easy to compare the Each Category Sales YoY.
Tree Maps
Tree Maps are used to Show the Summary Data by one Dimension then further drill down by another Dimension.
Steps:
1) Select [Region] &[Sales] using the control button and select Tree Map from Show Me canvas.
2) Segment onto the Detail Shelf to drill the Regions data Segment wise.
3) Drag [Sales], [Region] onto the Label shelf.
4) Apply Formatting on the work area.
Bar graphs
We use Bar graphs to compare a measure among the members of a dimension.
In this example, We are comparing each Region's Sales over several years.
Bars are visualized in a horizontal way.
Steps:
1) Drag Columns [Region], [Order Data Updated] onto the Rows shelf, [Sales] onto the Column shelf.
2) Select Horizontal Bar chart from Show Me canvas.
3) Drag Column [Region] onto the Colors Shelf.
4) Apply Formatting on the work area.
—-
We use Bar graphs to compare a measure among the members of a dimension.
In this example, We are splitting each Region by years.
Steps:
1) Drag Columns [Region] ,[Order Date Updated] on to Column shelf, [Sales] on to Rows shelf.
2) Select Stacked Bar chart from Show Me canvas.
3) Drag Column [Order Date Updated] onto the Colors Shelf.
4) Apply Formatting on the work area.
We use Bar graphs to compare a measure among the members of a dimension.
In this example, We are comparing each Region's Sales over several years.
Bars are visualized in a vertical way.
Steps:
1) Drag Columns [Region] ,[Order Date Updated] on to Column shelf, [Sales] on to Rows shelf.
2) Select Bar chart from Show Me canvas.
3) Drag Column [Order Date Updated] onto the Colors Shelf.
4) Apply Formatting on the work area.
Scatter Plots
Scatter Plots are used to show the co-relationship between two measure values.
Further we can drill down by using dimensions.
Steps:
1) Select [Profit] &[Sales] using the control button and select Scatter Plot from Show Me canvas.
2) Drag [Region] onto the Color shelf, now the View shows Year wise Sales & Profit.
3) It shows co-relationship between [Profit] &[Sales] further broken down by [Region] wise.
We can measure for bubble size, we can use a dimension to apply different colors on different dimension members. It is one of the good looking visualizations. We use it in text mining as well.
Steps:
1) Select [Profit] &[Sales] using the control button and select Scatter Plot from Show Me canvas.
2) Drag [Region] onto the Color shelf, now the View shows Year wise Sales & Profit.
We use Pie charts to show the contribution of each member of a specific dimension in specific measure.
In this example, We are getting each region's contribution or proportion in the Sales measure.
Steps:
1) Select [Region] &[Sales] using the control button and select Pie Plot from Show Me canvas.
2) Drag [Region] onto the Label shelf, now the View shows Region wise Sales.
Steps:
1) Select [Region] &[Sales] using the control button and select Pie Plot from Show Me canvas.
2) Drag [Region] onto the Label shelf, now the View shows Region wise Sales.
3) Drag [Category] onto the Column shelf, now the View shows Category wise Sales, Each category is further divided into different regions.
Tableau Work book - Tableau Public
