QuickSight Dataset Joins: Explained

Learning Center Articles
, , , , , , ,
1

In Amazon QuickSight you can create a (child) dataset by joining existing (parent) datasets together.

When joining datasets in this way, there are some key concepts to be aware of:

  1. A dataset is considered same-source when it doesn’t have any joins, or when all of the following conditions are met:
  • If any of the logical tables refer to a QuickSight data source:
    • All of the logical tables in this dataset must refer to the same QuickSight data source.
  • If any of the logical tables refer to a QuickSight dataset that is a parent dataset:
    • The parent dataset must use Direct Query.
    • The parent dataset must refer to the same QuickSight data source.
  1. If the above conditions aren’t met, the dataset is considered a cross-source join.
  2. Datasets can be one of 2 types, either Direct Query or SPICE
  • A Direct Query dataset connects directly to a data source, pulling data from that data source every time it is accessed
  • A SPICE (Super-fast, Parallel, In-memory Calculation Engine) dataset ingests data from a data source into QuickSight’s in-memory engine. When the dataset is accessed, data is returned from SPICE, not by running a new query against the originating data source.
  1. Datasets support Row Level Security (RLS), if any parent datasets have RLS enabled, this can affect if the child datasets are supported (in SPICE or Direct Query) or not, with resulting child datasets adhering to these points:
  • Child datasets that are created from a parent dataset that has RLS rules active can only be created with Direct Query.
  • Child datasets that inherit the parent dataset’s RLS rules aren’t supported in SPICE.

Given the above, to help determine what joins of parent datasets are supported and whether the resulting child datasets can use SPICE or Direct Query, the following diagram shows the possible combinations.

image

To use the diagram, start with the parent datasets in the middle:

  1. Identify if your parent datasets are:
  • all in SPICE,
  • a combination of SPICE and Direct Query, or
  • all using Direct Query
  1. Identify if the dataset join is cross-source or same-source
  • If at least one of the parent datasets are in SPICE, then the join is cross-source
  • If all datasets are Direct Query then
    • If they all use the same QuickSight data source, then the join is same-source
    • If not, then the join is cross-source
  1. Do any of the parent datasets use RLS?
  • If yes, follow the arrows up for the type of dataset join
  • If no, follow the arrows down for the type of dataset join

Hope this helps,
Andrew

1 Like