SQL/ Tableau Final

Logical Equals:

= or ==

•to enclose field names

[ ]

to start a comment

//

Logical Operators:

AND, OR, NOT

Available Function Categories:

Number String Date Type Conversion Logical Aggregate User Table Calculation

_____ a field gives a menu of metadata options

Right-clicking

What is the VizQL baseline translated into?

SQL (Structured Query Language), MDX (Multidimensional Expressions), and TQL (Tableau Query Language)

A Tableau dashboard

a collection of views, filters, parameters, images, and other objects that work together to communicate a data story

____ (color) check for primary source

blue

•Most dimensions are _____by default and most measures are _____by default

discrete, continuous

•OLAP data sources cannot be ____and require a __ _______

extracted, live connection

•Floating objects (do what?)

float on top of the dashboard in successive layers

Metadata

information about the data (field name, data type, default format, comments, aliases, etc.)

Area (annotation)

•Associated with an area in the view •Typically shown when at least a part of the area defined is visible

•Row Level Calculations

•Performed for every row of underlying data

Types of annotations

Mark Point Area

Types of calculations

•Row-level •Aggregate-level

•Annotations - an alternative to _____

labels

What are the Tableau tasks?

1. Data connection, integration, and preparation 2. Data exploration 3. Data visualization 4. Data analysis 5. Data storytelling

Measures and Dimensions (explain both)

1.Measures: Values that are aggregated. They can be summed, averaged, and counted or have a minimum or maximum 2.Dimensions: Values that determine the level of detail at which measures are aggregated •Creating groups in which the measures fit •The combination of dimensions used in the view defines the view's basic level of detail

Ways to create calculated field

1.Navigate to Analysis | Create Calculated Field ... from the menu 2.Use drop-down menu next to Dimensions in the data pane and select Create Calculated Field 3.Right-click an empty area in the data pane and select Create Calculated Field 4.Use the drop-down menu on a field, set, or parameter in the data pane and select Create | Calculated Field ... 5.Double-click an empty area on the Rows, Columns, or Measure Values shelves or in the empty area on the Marks card to create an ad hoc calculation

•Create Floor to indicate whether apartment is upstairs or downstairs (IF THENS)

IF INT(Unit) = 1 OR INT(Unit) = 2 THEN "Downstairs" ELSEIF INT(Unit) = 3 OR INT(Unit) = 4 THEN "Upstairs" ELSE "Unknown" END

Heat map

Ex. day on columns, month on rows + sum (sales) on color

Approaches to Building Dashboards

Guided Analysis Exploratory Scorecard/Status Snapshot Narrative

Bullet chart (how to make? how does it look like)

Make a bar chart + add an element to detail (you might have to drag something over from analysis as well)

•Tableau places any calculation with a numeric result under _______ by default

Measures

CHECK THAT YOU KNOW ALL THE DIFFERENT TYPES AND HAVE A VAGUE IDEA ON HOW TO MAKE THEM

PLS

Tableau Workspace

See Ch.1 Slides 5 - 9

•Display a filter control for most fields by right-clicking on it and selecting _______

Show Filter

•Utilizes VizQL was developed as a ____ ____ ____

Stanford Research Project

What language does Tableau utilize to help illustrate the data?

Utilizes VizQL - a visual query language which works behind the scenes

How to apply filters to different sources (specify the scope of the filter)?

by using the menu for the field on the Filters shelf •Select Apply to and select an option below: •All related data sources - All data sources will be filtered by the value(s) specified •Relationships of fields are same as blending •Sometimes referred to as cross-data source filtering •Current Data Source - data source for that field will be filtered; any views using that data source will be affected by the filter •Selected Worksheets - worksheets selected that uses the data source of the field will be affected by the filter •Current Worksheet - only the current view will be affected by the filter

•Calculations consist of ....

code that references other fields, parameters, constants, groups, or sets, and use combinations of functions and operations to achieve a result

•A data source

may contain more than one connection which can be joined together, such as a table in SQL Server joined to an Excel table

____ (color) check for secondary source

orange

Tableau has a _______ ______ and _____ environment

realtime drag and drop

•A connection

refers to the connection made to a single set of data, such as table(s) in a single database or file(s) in a directory

•Tiled objects (do what?)

snap into a tiled layout next to other tiled objects or within layout containers

•Objects can be ___ or ____

tiled or floating

Tableau will only query the data source when __________________ or ________________; otherwise it will use the results stored in a ______________

you make changes requiring a new query or refresh a view; local cache

to enclose level of detail calculations

{ }

Arithmetic Operators:

•+, -, *, /

Common Tableau file extensions

•.tde = Tableau Data Extract •Contains data extracted from the source •.twb = Tableau Workbook •Contains definitions for all the connections, fields, visualizations, and dashboards •Does not contain any data or external files, such as images •.twbx = Tableau Packaged Workbook •Any extracts and external files are packaged together in a single file with the workbook

•Tableau's Definition of a Dashboard

•A dashboard is a set of worksheets along with other various components such as legends, filters, parameters, text, containers, images, and web objects arranged on a single canvas

Parameters

•A placeholder for a single global value, such as a number, date, or string •May be shown as controls (sliders, drop-down lists, or type-in text boxes) on dashboards or views •A parameter is global •If the value is changed, every view and calculation in the workbook that references the parameter will change

Extracts

•A snapshot of the data source at the time of the latest refresh •Data can be retrieved from the data source again to refresh the extract •Extracts offer the benefit of being portable and extremely efficient Incremental refresh can be used to add new records

Level of Detail Calculations

•A special type of calculation that allows you to perform aggregations at a specified level of detail, which may be different from the level of detail defined in the view, and then work with the resulting value at a row level •Like a hybrid between Row Level calculations and Aggregate Row Level calculations •Abbreviated as LoD calcs or LoD expressions

•Dashboard Definition by Stephen Few

•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 at a glance

•Box and Whisker Plots

•Add additional information and context to distributions •Show upper and lower quartile and whiskers that extend to either 1.5 times the upper/lower quartile or to the max/min values in the data •Allows you to see outliers

Ad Hoc Calculations

•Add calculated fields to shelves in a single view without adding fields to the data pane •Field can be dragged to the data pane to transform it into a regular calculated field that will be available for other views using that data source

Full Outer Joins

•All records from tables on both sides will be kept •Matching records will have values from the left and right tables •Unmatched records will have NULL values where either the left or the right matching record was not found •If the data source does not support a full outer join, the option will be disabled

Left Outer Joins

•All records from the table on the left will be kept •Matching records from the table on the right will also have values in the resulting table •Unmatched records from the left table will have NULL values for all the fields from the table on the right

Right Outer Joins

•All records from the table on the right will be kept •Matching records from the table on the left will also have values in the resulting table •Unmatched records from the right table will have NULL values for all the fields from the table on the left

•Do not use an extract when: .....

•You have sensitive data; can extract all but sensitive data •You need to manage security based on login credentials •You need to see changes in the source data updated in real time •The volume of data makes the time required to build the extract impractical •This is also dependent on data type of fields number of fields, speed of the data source, and network bandwidth

Blending Data Sources

•Allows you to use data from multiple data sources in the same view •Data sources may be of different types (Oracle with Excel data, etc.) •Data blending is done at an aggregate level and involves different queries sent to each data source •Differs from joins which is done at a row level and involves a single query to a single data source •Linking fields are dimensions used to match data blended between primary and secondary data sources

•Data analysis

•Analytical functions to show complex relationships, patterns and data correlations

Mark (annotation)

•Associated with a specific mark (such as bar or shape in view) •Can display any data associated with them mark •Will be shown in view as long as that mark is visible

Point (annotation)

•Associated with a specific point as defined by one or more axes in the view •Can display values, which define the X and/or Y location of the point •Will be shown in view as long as the point is visible

Histograms

•Bars show count of occurrences of a value •BINS are ranges of measurevalues that can be used asdimensions to slice the data HOW TO CREATE BINS?

•Data storytelling

•Build fully interactive dashboards and stories with visualizations and insights to share

Stacked bar

•Can be difficult to compare values across categories for any bar other than the bottom-most (or left-most) bar

(A Table task) Data connection, integration, and preparation

•Can connect to data from many sources and combine data from multiple sources

•Data exploration

•Can view dataset visually

•Data visualization

•Can visualize data to ask and answer questions, raise new questions and gain new insights

You can sort in multiple ways:

•Click on one of the sort icons on the toolbar •Click on the sort icon on the axis •Use the drop-down menu on the active dimension field and select Sort to view and edit sorting options •You may also select Clear sort to remove any sorting •Drag and drop row headers to manually rearrange them •Does not get updated with data refreshes

Performance of Extracts

•Columnar and extremely efficient •Load and move quickly between memory and disk storage •Many calculated fields are materialized in the extract •The pre-calculated value is stored in the extract •Other ways to increase performance include: •Hide unused fields not used in any view or calculation (Tableau can do this automatically through the Hide Unused Fields option) •Filter the extract to use the subset of data you are interested in •Optimize the extract after creating or editing calculated fields, or deleting/hiding fields •Store extracts on solid state or drives that are defragmented regularly

Approaches to Building Dashboards: Scorecard/Status Snapshot

•Contains Key Performance Indicators (KPIs) which are metrics that indicate good versus poor performance •Provide top-level summary and enough detail to be able to drill down quickly to find and fix problems and reward success

Approaches to Building Dashboards: Guided Analysis

•Dashboard designed to guide the audience through a discovery process so the audience develops an understanding of the data implications and the need to act is clear

•Limiting scope of data - filter data at source

•Data source filters - applied before all other filters •Extract filters - limit data stored in a Tableau Data Extract file •Data source filters may be converted to extract filters •Custom SQL filters - accomplished using a live connection with custom SQL that has a Tableau parameter in the WHERE clause (Chapter 4)

•Combination Charts

•Extend the use of dual axes to overlay different mark types

•TYPE: is the type of LoD calculation (What types are there?)

•FIXED: Aggregates at the level of detail specified by the list of dimensions in the code regardless of what dimensions are in the view •INCLUDE: Aggregates at the level of detail determined by the dimensions in the view and the dimensions listed in the code •EXCLUDE: Aggregates at the level of detail determined by the dimensions in the view, excluding any listed in the code

Two types of geographic visualizations: (name them)

•Filled maps •Symbol maps

•Can connect to a wide variety of files and databases (what are they?)

•Flat files (Excel and text files) •Relational databases (SQL Server, Oracle) •Cloud-based data sources (Google Analytics) •OLAP data sources (Microsoft Analysis Services) •Text file (.csv file)

•Dashboard Objectives. Your data story in Tableau should:

•Focus on the most important information •If it doesn't support the main story, exclude it or include on other more appropriate dashboards •Must meet your key objectives •These key objectives are ranging from giving information, to providing an interface for further exploration, to prompting your audience to take action or make key decisions •Anything that doesn't support your objectives should be reserved for other dashboards •Must be easily accessible and the primary idea should be obvious

•Continuous Fields

•Have values that flow from first to last •Numeric and date fields are typically continuous •Values have an order which would make little sense to change •Shown as green in Tableau

Joins

•If key fields and relationships have been defined in the database, Tableau will automatically create the joins as you add additional tables to the data source designer •Otherwise it will attempt to match field names and you will need to adjust the joins as needed

•Circle Charts with Jittering

•Jittering adds some intentional noise to a visualization to avoid overlap without harming the integrity of what is communicated •Generally uses the Index() or Random() functions

Two different ways to link related data together in Tableau

•Joins - link tables of data together on a row-by-row basis •Blends - link together multiple data sources at an aggregate level

Filter data in Tableau to perform analysis to:

•Limit scope to a subset of data •Narrow your focus •Drill into detail

Bar in bar chart (what values does it automatically include? when is it useful? what does it look like and how do you make it?)

•Measure Names •A special dimension field thatTableau adds to every datasource that is a placeholderfor the names of measures •Measure Values •A special measure field that Tableau adds to every data source that is a placeholderfor the values of other measures •Created any time you want twoor more measures to share the same space within a view

Creating Parameters

•Multiple ways to create 1. Use drop-down menu next to Dimensions in the data pane 2. Right-click on an empty area in the data pane and select Create Parameter Field 3. Use the drop-down menu on a field, set, or parameter in the data pane and select Create | Parameter... •Parameters created from fields will only contain the values (discrete) or range (continuous) defined by the field at the time they are created •The list or range will not be dynamically updated to reflect changes in the data

•Virtually unlimited accessibility of Tableau to data (what type of connections can Tableau make?)

•Native Connectors for cloud-based data •Web Data Connector - allows you to custom-build a connector for any online data •ODBC Generic Connection •Extract API - allows you to program code to extract and combine any data source

Inner Joins

•Only records that match the join condition from both the table on the left and the table on the right will be kept

•Aggregate Level Calculations

•Performed at an aggregate level defined by the fields used as dimensions in the view

•Table Calculations

•Performed at an aggregate level on a table of aggregate data which has been returned by the data source to Tableau •Not part of the query to the data source; applied in cache just prior to the view being rendered

Approaches to Building Dashboards: Exploratory

•Provide audience with an analytical tool that gives them the ability to explore and interact with various aspects of the data on their own •Allows audience to see what is important now and where a decision point might be for the future

Filtering Dates

•Relative date •Allows filtering a date based on a specific date (such as 3 weeks from today or last 6 months from first January) •Range of dates •Allows filtering a date based on a range with a starting date, ending date, or both •Date Part •Allows filtering based on discrete parts of date such as Years, Months, Days, or combinations of parts such as Month/Year and Month/Day/Year •Individual dates •Allows filtering based on each individual value of the date field in the data •Count or Count(Distinct) •Allows filtering based on the count or distinct count of date values in the data

Calculate Price per Square Foot from Price and Area values

•SUM(Price)/SUM(Area)

Cross-database Joins

•Tableau 10 introduced the ability to join (at a row level) across multiple different data connections •For example you can join SQL Server tables with text files or Excel files or tables in one database with tables in another •You need to make sure that you have field(s) that you are joining contains comparable information and have the same data types

File extensions that Tableau can connect to

•Tableau data extract (.tde file) •Can only access data contained in the extract; not from the original source •Microsoft Access (.mdb or .accdb) •Microsoft Excel (.xls, .xlsx, or .xlsm) •Multiple Excel sheets or sub tables may be joined or unioned in a single connection •Text file (.txt, .csv, .tab) •Multiple text files in a single directory may be joined or unioned in a single connection •Statistical files (.sav, .sas7bdat, .rda, .rdata) •From tools such as SPSS, SAS, or R •Other Tableau workbooks (.twb, .twbx) •Data connections saved in another Tableau workbook can be imported; changes affect only the current workbook

Approaches to Building Dashboards: Narrative

•This type of dashboard tells a clear story in a visual way •May have aspects of exploration, guided analysis, or performance indication showing what is necessary to communicate the meaning of the data

Pie chart (when to use etc)

•Typically a bar chart is a better alternative •Do not use if more than 2 or 3 slices •Also sort the dimension that defines the slice which will order the slices as another way to ascertain size

Scatterplot

•Used for understanding therelationship between two measures •The dimensions of Department and Category on the Marks card define the view level of detail

Gannt Chart (useful for___)

•Useful for understanding any series of events with duration, especially if those events have some kind of relationship

Bar Charts (how do they look like and when are they useful? how do you make them?)

•Useful in comparing differences of measured values across different categories •Category is a discrete dimensionin the view which defines rowheaders •Sales defines an axis because it iscontinuous and is summed (because it is a measure) for eachcategory

Treemap (useful when ... , alternative)

•Useful when you have hierarchies and dimensions with a high number of distinct values •Considered a non-Cartesian chart as there is no x or y axis and no row/column headers

•Discrete Fields

•Values that are shown as distinct and separate from each other •Can be reordered and still make sense •Shown in blue in Tableau

•Dual Axis

•View is using two axes that are opposite each other with a common pane •Unlike using Measure Names and Measure Values to show more than one measure on a single axis •To make the two axes measure with the same values you must right-click on the added axis and choose Synchronize Axis •Measures may need to be redefined to the same numeric data type

Portability and Security (of Extracts)

•With an extract, you can take the data with you and work offline •Will lose security that limits what data a person can read/access •Data for visible (non-hidden) fields contained in the extract or a packaged workbook can be accessed even it is not in the visualization

•Use an extract when: .....

•You need better performance than you can get with a live connection •You need the data to be portable •You are using legacy (JET driver) connections to Excel, Access, or text files and you need functions not supported by the JET driver, but supported by extract •Want to share a packaged workbook

Level of Detail Syntax

•{[TYPE] [Dimension 1], [Dimension 2] : AGG([Measure])} •TYPE: is the type of LoD calculation •Dimension 1, Dimension 2: A comma-separated list of dimensions that define the level of detail at which the calculation will be performed (can be more than 2) •AGG: the aggregate function you wish to perform (SUM, AVG, MIN, MAX, etc) •Measure: The field that will be aggregated by the aggregate function