Tableau - Connecting to Data

7 minute read

Published:

Connecting to Data

  • Different File Types, Server-based data sources - multiple data sources can be connected
  • Flat Files - Excel, Text, JSON, etc
  • Connect to datasource Excel file
    • Drag Orders to Canvas or Double click Orders from left Panel
    • Select “Use Data Interpreter” to clean Excel Workbook
    • Change Table name
    • Double Click name
  • Change column name
    • Double click on the name
  • Review Data types
    • Numeric Icons (#) in the headers are
      • Blue for Discrete
      • Green for Continuous
    • Try Changing datatype
      • Left click on the data type
      • Row ID is numeric but it can be text since no mathematical function need to be applied on Row ID
    • Column names can be renamed
    • Splitting the string column
      • Right click Order ID and select
        • split or
        • custom split
  • View Data
    • Hover on sheet under Sheets (Left Panel under Data Source/connections) and click right icon of the sheet to view data
  • Check other Options
    • Create a new Worksheet;
      • One Visualization
    • Dashboard
      • Multiple Visualization
    • Story
      • Combination of Worksheets and Dashboards
  • Connection Options
    • Available in Tableau Desktop
    • Live
      • Default Option
      • Direct Connection to the data
      • Leaves the data in the database/source file
      • Any update to the data, can be refreshed
      • Best when we want to leverage high performance database’s capabilities
      • Sometime it can be slow
    • Extract
      • compressed snapshot of data stored locally and loaded into memory
      • Any update to the data, will not be updated using refresh but can be updated using extract ->refresh
        • Option available on right clicking the data source in the worksheet
      • Extract data into Tableau’s high performance in-memory data engine. Useful when connecting to a slow database
      • We may import all data or click edit (along side extract) to select specific elements
    • Visual indications on Worksheet
      • Live - Single cylinder for the data source in the worksheet
      • Extract - Double cylinder for the data source in the worksheet
    • Metadata grid
      • to view quickly data fields
      • available on Data Source above table in Public and left in Desktop

Joining Data

  • Connect to data source
  • Drag first table to canvas
  • Double click first table in canvas to open physical layer
    • to close this view click cross on the right side of the view
  • Double click another table in the left pane to join canvas or Drag to next of previous table
  • Click the join to configure the join
    • select join type
    • select join variable Order ID
  • close
  • Join Types
    • Inner
      • values that matches in both tables.
    • Left
      • values from left and corresponding matches from right
      • Null value in grid when no match in right
    • Right
      • values from right and corresponding matches from left
      • Null value in grid when no match in left
    • Full Outer
      • all values from both tables
      • null values when no match
  • Union
    • combining tables by appending rows (tables to have same fields and data types else null values)
    • In the physical layer, drag People below Order

Dimensions and Measures, Blue and Green

  • Data field roles and types

    • Each field is assigned:
      • Data Type
        • integer, string, date
      • Role
        • Discrete Dimension or Continuous Measure [More Common]
        • Continuous Dimension or Discrete Measure [Less Common]
    • Dimensions
      • Qualitative values
        • name, date, geographical data
        • used to categorize, segment, and reveal the details
        • Affect the level of detail in the view
    • Measures
      • Numeric, Quantitate values
      • can be aggregated
      • dragging a measure into view will apply default aggregation
  • Blue vs Green Fields
    • Blue: Discrete
      • Blue/Discrete values are treated as finite
      • Discrete fields add headers to the view
    • Green: Continuous
      • Green/Continuous values are treated as infinite range
      • Continuous fields add axes to the view
  • Visual Cue for Measure
    • Field is aggregated with a function
      • there are some with exception
  • Dimensions and Measures
    • Dimensions are used to segment data
      • i.e. Grouping similar data together
    • Measures can be aggregated

Examples

Quantity:
Green and Axis -> Continuous

No Agg Function -> Dimension
Quantity:
Blue and Horizontal header -> Discrete

No Agg Function -> Dimension
Sales - Green and Aggregation -> MeasureSales - Green and Aggregation -> Measure
  • Date dimensions can be discrete or continuous

Dimension or Measure?

  • Qualitative, usually discrete
  • Used to group and segment data
  • Blue color coded
  • Quantitative, usually continuous
  • can be aggregated
  • Green color coded

Tableau Interface

  • Create a new Worksheet
    • Two Tabs on the left side
      • Data and Analytics
    • Data pane
      • Open Data Sources
        • global_superstore_order
          • right click to check for options
      • Fields
        • Dimension and Measures separated by line
          • Dimension - Categorical, Qualtative, time - can segment data
          • Measure - Quantative Date - numeric - can be measured - can be aggregated
        • Blue - Discrete, Categorical
        • Green - Continuous
        • Calculated Fields
      • Views
        • Rows - y-axis
        • Columns - x-axis
        • Pages
          • break view into pages e.g. one page for each region
        • Filter
        • Marks
          • Cards
            • Color, Size, Label, Text, Detail, Tooltip to add context or too encode the marks on a view
          • Type
            • Automatic, Bar, Line, Area, etc to change marks in the view
      • Toolbar

Task

  • Clear sheet
  • Load Global Superstore Data
  • Drag Country field to Rows
  • Sales Column to Text
    • Which country has highest sales
      • United States
    • Total Sales by United States
      • 2,297,201
  • Apply Filter on Segment
    • Select Consumer in Filter
    • Sales in consumer segment for Australia
      • 499,313
  • Remove Segment Filter and Apply Filter on Quantity
    • select sum of quantity in filter
    • How many countries have total quantity at least 10,000
      • 3
    • Country with lowest sales with quantity at least 10,000
      • France
  • Add Segment in Rows
    • Sales for United Sates in Home Office Segment
      • 429,653
  • Add Segment in Columns and Filter on Sales
    • Which country has lowest sales among countries that have sales higher than 200,000
      • Country (Rows); Segment (Columns); Apply Filter on total (sum) sales so that minimum sales is 200,000
        • Indonesia
  • Add Segment to Pages
    • Navigate to different segment from right panel (in case navigation option not available then click show me)
      • Navigate by arrow
      • Navigate by Dropdown

Grouping Data

  • Right Click on field Category
    • Create
      • Group
        • Select Furniture and Office Supplies
          • Click Group
  • A new field will be created with icon Paper Clip
  • Right click to edit group and select check box - Include Other
  • All other fields which have not been selected will be included in the other
  • Create Group on Countries and use Find Option
  • Drag fields to the Group

Creating Folder

  • We can group fields in the folder, useful when there are many fields
    • Right Click on the field
      • Folders -> Create Folder
    • Drag fields to Folder

Create aliases

  • Rename members in the view
    • Right Click Market
      • Aliases -> Select value and edit

Edit Default Properties

  • Right click sales
    • Default Properties -> Number Format -> Currency Standard -> Country (may search)
    • Default Properties -> Number Format -> Currency Custom -> Enter Values

Save and open a Tableau data source (.tds)

  • To save changes to the metadata
  • open Data from the main tool bar
    • Data source selected -> Add to saved data sources
  • To load
    • Connect -> More -> Select tds