Combining Tables, Fields, Rows Flashcards
Relationship
Description: ____
Tables aren’t merged together in the ____.
Joins: how are joins selected? What type of joins are supported?
LOD for each table: ____
Type:
Dynamic, flexible way to combine data from multiple tables
Table:
~Tables aren’t merged together in the data source. Relationships create independent domains at multiple levels of detail.
Joins:
~Tableau automatically selects join types based on the fields being used in the visualization
~Supports full outer joins, make all row and column data from related tables potentially available in the data source
LOD:
~Maintain each table’s LOD in the data source and during analysis
~Create independent domains at multiple LODs. Tables aren’t merged together in the data source.
~You don’t need to use LOD expressions (i.e. FIXED) to deduplicate data in related tables.
If you could use a Relationship and a Join to combine data, which one should you try first? Why?
Relationship bc it makes data preparation and analysis easier & more intuitive
BONUS:
List of advantages:
~ Make your data source easier to define, change, and reuse.
~Make it easier to analyze data across multiple tables at the correct level of detail (LOD).
~Do not require the use of LOD expressions or LOD calculations for analysis at different levels of detail.
~Only query data from tables with fields used in the current viz
Joins
Description
Tables
LOD
Static way to combine data. Join can’t be changed without impacting all sheets using that data source
Tables are merged into 1 table (sometimes this means umatched meaure values are dropped)
1 Table also means that sometimes (when fields are at diff LOD) -> joined data may duplicate aggregated values
Joins, Unions, Relationships, or Blending?
Requirements:
~fields that define relationship must have same data type
~No defining based on geographic fields
~Doesn’t support circular relationships
~No defining between published data sources
Relationships
How do you combine Published Tableau data sources?
Bending
Bonus:
Can also edit the original data sources to natively contain the join
Join Clause
+ Requirement
Tells Tableau which fields are shared between the tables and how to match the corresponding rows
2 matching fields must have same data type
Join Types
Full, Inner, Outer, Left, Right, LeftOnly, RightOnly
Joins vs Unions
Table Requirements:
Appends:
Data:
Tables Require:
a common fields VS similar structure
Appends:
Columns (fields) VS rows
Widens VS lengthens data
Creates a new data source
Joins, Unions, Relationships, or Blending?
Joins & Unions
Only combines data using Left Joins & doesn’t create a new data source
Joins, Unions, Relationships, or Blending?
Blending
Combines using Full Outer joins
Joins, Unions, Relationships, or Blending?
Relationships
Combines data and then aggregates data
Joins, Unions, Relationships, or Blending?
Joins and Unions
e.g. if the left table has ‘Orange’ with 2 values (Orange, 2 & Orange, 8), those values are combined with the right table and then aggregated. If the second table has Orange with 1 value (Orange, 7), then that value is duplicated (Orange, 2, 7 and Orange, 8, 7). With this duplication, the value from the right table will sum to 14 when there is actually just one Orange, 7 in the original right table.
See this for reminder: https://www.youtube.com/watch?v=z2lAmjexQCc
Aggregates data then combines data
Joins, Unions, Relationships, or Blending?
Give example
Blending & relationships
e.g. if the left table has ‘Orange’ with 2 values (Orange, 2 & Orange, 8), those values are aggregated (Orange, 10) then the Left Table is combine with the Right Table
(No worrying about duplicates)
See this for reminder: https://www.youtube.com/watch?v=z2lAmjexQCc
What should you use if the linked fields need to vary from sheet-to-sheet?
Joins, Unions, Relationships, or Blending?
Use Blending
You can’t create _____ between tables from published data sources
Joins, Unions, Relationships, or Blending?
relationships or joins or unions
___ combines data directly
Joins, Unions, Relationships, or Blending?
Relationships, joins, unions
Think: combines @ the datasource?
____ never combines data directly.
Instead, it ___ query data source independently, aggregate the results to the appropriate level, then present the results together visually in the view.
i.e. ____ never truly combine the data but queries each data source independently.
Blends
Bonus: This is unlike relationships, joins, and unions
___ is performed on a sheet-by-sheet basis
Blending
For blending, what is the color of the primary and secondary data sources
blue & orange
After blending, what is used to indicate multiple dimension values in a single mark, because data blending takes aggregated results and combines them in the view?
(Think of this as a one-to-many issue)
How would you solve this issue?
hint: 2 solutions
.
.
.
.
.
..
.
.
.
..
.
Solution: Create a Join or increase the LOD by adding relevant fields.
See this if you forgot:
https://www.youtube.com/watch?v=cC-hZtiZlHg
Relationships VS Blends
Defined in the ____:
Can be Published?:
Tables semantically equal?:
Join Type:
Related fields are fixed?:
Related fields are linked?:
Scope: Data source VS
Worksheet b/t primary & secondary data source
[think about how you create a relationship (in the Data Source pane) VS a Blend(in worksheet) ]
Can be Published?: Can VS Cannot
Semantics (aka rules): all tables are = semantically (same rules) VS
Depend on selection of primary and secondary data sources, and how those data sources are structured
Join Type: Supports full outer join (everything) VS
Left only
Related (Joined/linked) fields are fixed?:: fixed VS
vary sheet-by-sheet
Related fields are linked?:: no (joined) VS yes, linked
Relate Your Data:
https://help.tableau.com/current/pro/desktop/en-us/relate_tables.htm?_gl=1mnxgvl_gaMTI1NDAzNjQ4My4xNzI2ODg2OTE5_ga_8YLN0SNXVS*MTc0MDEwMzg3Ni42MC4xLjE3NDAxMDcyMTUuMC4wLjA.
Semantics for Relationships: https://www.tableau.com/blog/relationships-tableau-data-model#:~:text=The%208%20Rs%20of%20relationship,left%2C%20right%2C%20and%20full)
We cannot use ____ on published data sources, so our only option for such cases is ___.
Relationships, Joins, or (Unions?)
Blend
Relate Your Data:
https://help.tableau.com/current/pro/desktop/en-us/relate_tables.htm?_gl=1mnxgvl_gaMTI1NDAzNjQ4My4xNzI2ODg2OTE5_ga_8YLN0SNXVS*MTc0MDEwMzg3Ni42MC4xLjE3NDAxMDcyMTUuMC4wLjA.
Relationships VS Joins
Defined:
Join defining required?:
Join Type:
Tables:
LOD(is at the ___):
Combines -> Aggregates?:
Duplicated Rows Possible?:
Unmatched records:
Running Queries:
Defined: b/t logical tables in the Relationship canvas (logical layer) VS
b/t physical tables in the Join/Union canvas (physical layer)
Join: Don’t require defining join type VS
Require Join planning & join type
Join: Dynamic VS Static
Tables: Act like containers for tables that are joined/unioned VS
Are merged into their logical table
LOD: at the aggregate for the viz VS
at the row lvl for the single table
Combines -> Aggregates?: No VS Yes
Duplicated Rows Possible?: No VS Yes
Unmatched records: included in aggregation, unless explicitly excluded VS
omitted from the merged data
Running Queries: Only data relevant to the viz is queried VS
Run as part of every query
(Therefore, relationships generate shorter queries)
Relate Your Data:
https://help.tableau.com/current/pro/desktop/en-us/relate_tables.htm?_gl=1mnxgvl_gaMTI1NDAzNjQ4My4xNzI2ODg2OTE5_ga_8YLN0SNXVS*MTc0MDEwMzg3Ni42MC4xLjE3NDAxMDcyMTUuMC4wLjA.
1.Use when combining data from different levels of detail.
- Use when you want to add more rows of data with the same column structure.
- Use when you want to add more columns of data across the same row structure.
- Use when combining data from different levels of detail.
- Blend
- Union
- Join
4.Relate
Relate Your Data:
https://help.tableau.com/current/pro/desktop/en-us/relate_tables.htm?_gl=1mnxgvl_gaMTI1NDAzNjQ4My4xNzI2ODg2OTE5_ga_8YLN0SNXVS*MTc0MDEwMzg3Ni42MC4xLjE3NDAxMDcyMTUuMC4wLjA.
