Multiple Level Relationship / Filtering

Question & Answer
,
1

Hi All,

do you have any suggestion on how to structure the data to properly work on QS?

Since we cannot use relationship at the moment, see Multiple Datasets to be connected like "relationship" than "join" I would need a valid workaround.

I will explain the use case with this image:

Capture
Capture605×682 89.7 KB

Can you think on a more efficient workaround? to avoid using calculated fields in this way.

Thanks!

2

Hi Andrea,

The higher hierarchy levels that you want to filter on will need to be in the dataset that drives the visual. Consider introducing a hierarchy relationship table with a flattened structure like the sample given below. In base tables, use hierarchy level key and hierarchy level columns and then join with hierarchy relationship table on those columns while pulling data into datasets (You can also do a custom sql mapping the specific level key from base table and hard coding the level against the hierarchy relationship table). This will help you get all the higher hierarchy levels into datasets in a scalable way.

image
image900×448 54.4 KB

(There is the alternate option to use cascaded filter controls. But that can be used only if you always make selections all the way to lowest level of the hierarchy. I don’t think that will work for your use case. Just mentioning this option as well for completeness)

Regards,
Arun Santhosh

3

Hi Arun, thanks. Your methodology is what I’ve applied so far, the problem is that I have much more levels and this is not scalable

4

Hi Andrea. I’ll communicate this feature request to the correct team

5

I resolved this issue by creating a hierarchy data structure like this:

image

It allows an n-level relationship structure. If you’re using Athena as a datasource, you must base your Quicksight dataset on a query like the one below. It returns each row, it’s parent, and its fully qualified lineages. This query limits the depth of your structure to the number of Common Table Expressions (CTEs) that you manually script.

with L0 as (
select 0 level 
,id
,name
,name fqn
,'' parentname
,'' parentfqn
,cast(h.id as varchar) || '.' fqid
,'' parentfqid
,h.class fqc
,h.owner
,h.owner fqo
,h.class
from hierarchy h where parent = 0
)
, L1 as (
Select length(L0.fqid) - length(REPLACE(L0.fqid, '.', '')) Level
,h.id
,h.name
,L0.fqn || '/' || h.name fqn
,L0.name parentname
,L0.fqn parentfqn
,L0.fqid || cast(h.id as varchar) || '.' fqid
,L0.fqid parentfqid
,L0.fqc || '/' ||  h.class fqc
,h.owner
,L0.fqo || '/' ||  h.owner fqo
,h.class
from hierarchy h
join L0 on L0.fqid = cast(h.parent as varchar) || '.'
)
, L2 as (
Select length(L1.fqid) - length(REPLACE(L1.fqid, '.', '')) Level
,h.id
,h.name
,L1.fqn || '/' || h.name fqn
,L1.name parentname
,L1.fqn parentfqn
,L1.fqid || cast(h.id as varchar) || '.' fqid
,L1.fqid parentfqid
,L1.fqc || '/' ||  h.class fqc
,h.owner
,L1.fqo || '/' ||  h.owner fqo
,h.class
from hierarchy h
join L1 on L1.fqid like '%.' || cast(h.parent as varchar) || '.'
)
,
Orgs as (
select * from L0
union select * from L1
union select * from L2
)

select *
, split_part(fqn,'/',1) L0,split_part(fqc,'/',1) L0Class, split_part(fqo,'/',1) L0Owner
, split_part(fqn,'/',2) L1, split_part(fqc,'/',2) L1Class, split_part(fqo,'/',2) L1Owner
, split_part(fqn,'/',3) L2, split_part(fqc,'/',3) L2Class, split_part(fqo,'/',3) L2Owner
from Orgs

I can tie other data into it using the ID value. It works in Quicksight for Pivot tables and Sankey Diagrams.

1 Like
6

Thanks for sharing this detailed solution @heraclitus! :slight_smile: