Skip to content

Instantly share code, notes, and snippets.

@Hugoberry

Hugoberry/d2-layout-pre.sql

Last active May 23, 2023 22:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Hugoberry/cb2687e1ca49d0d2305fbb59fd0b9ebb to your computer and use it in GitHub Desktop.
Save Hugoberry/cb2687e1ca49d0d2305fbb59fd0b9ebb to your computer and use it in GitHub Desktop.
Download ZIP
sqlite query to surface the relationships from metadata.sqlite in PowerBI
Raw
d2-layout-pre.sql
with cte as (SELECT
ta.name as ta , tb.name as tb
FROM
'relationship' rel
join 'table' ta on rel.Fromtableid = ta.id
join column ca on rel.fromcolumnid = ca.id
join 'table' tb on rel.totableid = tb.id and tb.systemflags =0
join column cb on rel.tocolumnid = cb.id)
select ta from cte
union select tb from cte
order by 1
Raw
d2-layout.sql
SELECT
ta.name ||'.'|| ca.ExplicitName as ca,'<-', tb.name ||'.'|| cb.ExplicitName as cb--, rel.fromCardinality, rel.toCardinality
FROM
'relationship' rel
join 'table' ta on rel.Fromtableid = ta.id
join column ca on rel.fromcolumnid = ca.id
join 'table' tb on rel.totableid = tb.id and tb.systemflags =0
join column cb on rel.tocolumnid = cb.id
order by 2
Raw
relationships.sql
SELECT ta.NAME AS ta,
ca.explicitname AS ca,
tb.NAME AS tb,
cb.explicitname AS cb,
rel.fromcardinality,
rel.tocardinality
FROM 'relationship' rel
JOIN 'table' ta
ON rel.fromtableid = ta.id
JOIN COLUMN ca
ON rel.fromcolumnid = ca.id
JOIN 'table' tb
ON rel.totableid = tb.id
AND tb.systemflags =0
JOIN COLUMN cb
ON rel.tocolumnid = cb.id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment