Brain dump before Tableau Certification

by Harriet Owen

Want to see into my personal revision notes? This blog provides some key notes on geocoding, spatial and other specific data connections.

  1. Geocoding:
    - Used when the geographic locations within your dataset are not automatically geocoded in Tableau.
    - Allows you to add to existing roles, create new roles and create hierarchies.
    - When creating a new custom geocode go to map menu > geocoding > import custom geocoding... (note: this has to be a text file)
    - If a workbook is saved as a package, all geocoding files will be saved within the package.
    - You can import the custom geocoding from the packaged workbook into the tableau repository but that will replace any custom geocoding on that system
    - To insert geocoding from a packaged workbook, select 'import from current workbook'
    - Connect on similar columns, like other data connections (relationships, joins...)
  2. Spatial
    - The following spatial file types can be connected to: Shapefiles, MapInfo tables, KML (Keyhole Markup Language) files, GeoJSON files, TopoJSON files, and Esri File Geodatabases.
    - Point, line or polygon maps can then be created from the data in these file types.
    - Connect to spatial files in the same way you would other file types.
    - See this link for the groups of files required for a spatial set up: https://help.tableau.com/current/pro/desktop/en-us/maps_shapefiles.htm
    - Tableau interprets the spatial files by creating latitude and longitude coordinates and puts this into a geometry field to be used in Tableau.
    - You can join spatial files to other spatial files or general files, as long as there is a common field to join on.
  3. Extracts:
    - .twbx: extract automatically saves as part of package
    - .twb: extract will be asked where you want it saved (.tde/.hyper)
    - Since Tableau 10.5 the file format for an extract changed to .hyper from .tde. A .hyper supports the same fast and analytical query performance, but can store even larger extracts.
    - Note: if an extract that is in the form of a .tde is updated it will become a .hyper and this conversion cannot be reversed. However, a .hyper cannot be opened in an earlier version (prior to 10.5) of Tableau.
    - See: https://help.tableau.com/current/online/en-us/extracting_upgrade.htm
    - Note: an extract will make your workbook more performant
    - The advanced options to an extract allow for data to be extracted at a specific aggregated level, you can select what range of rows you want and you can hide all unused rows (helps with performance)
  4. Saving and publishing data sources
    - Add to saved data sources (.tds) - only saves connection information, not data source itself
    - Publishing to server: select project, and give appropriate name, permissions for what can be done with the workbook can be defined
    - Editing publish data source: can add calcs/hierarchies and update by republishing
    - Publishing an extract offers option to schedule refresh
    - Tableau online can directly maintain live connections with cloud databases
  5. Connecting to the PDFs
    - PDFs lack metadata about data
    - Page picker: decide which part of document you want to scan
    - Data Interpreter is very useful here if cleaning is needed
    - Tableau works best when connecting to tabular tables...however they aren't always this clean in the PDF
    - Therefore, Tableau might interpret multiple ways of reading a messy table and you can select the most appropriate, however this still may require some cleaning through either unions, data filtering, data interpreter, pivoting, aliasing, data types and so on...
    - Note: tableau looks at absolute page numbers so be wary when selecting which page Tableau should read
  6. Connecting to cubes
    - OLAP or cube
    - Examples: Microsoft Analysis Services or Oracle Essbase
    - They process and transform data from a relational database into pre-aggregated results which define hierarchal structures
    - They can be quite hard for Tableau to read...
    - Cannot take an extract of a cube, and can only be accessed my non-Mac's
    - Cubes include KPI data types which Tableau does not recognise
    - Connecting to cube data source: can only view metadata
    - Measures do not aggregate themselves like usual in Tableau because a cube data source is pre-aggregated (this aggregation definition cannot be changed in Tableau) - table calcs are available.
    - Hierarchies can also not be changed - hierarchy filters exist.
    - Should avoid using continuous dates when using cube data sources in Tableau because it will maintain at the lowest level of detail and therefore, excessive amount of data points...cannot roll up to yearly or monthly view.
    - A workbook using a cube data source connection can be published to Tableau Server, but it does not support pass-through connections

What are pass-through connections in Tableau?
- Known as RAWSQL pass-through connections
- They send SQL expressions directly the to the database, before interpreted by Tableau.
- Does not work with extracts or published data sources