Skip to main content

I am preparing an Inventory report that displays details about each object in a workbook, its related datasource, extract details and the path along with its usage. Precisely in the order Project>Parent Folder>Child Folder>DataSource >Workbook>Owner>date. I tried building a data model using tableau.github.io/tableau-data-dictionary/2021.2/data_dictionary.htm⌗public.datasources_anchor. However, The outcome isn't as expected for Datasource and Workbook columns. When datasource is NULL, Workbook values are populated and viceversa. I am not sure if the column used to join datasource and workbook tables are correct. I am looking to have a mapping between them. Kindly advise..

Query used to generate the report: 

select h_events.created_at as h_events_created_at,h_events.hist_actor_user_id,h_events.hist_project_id,h_events.hist_target_site_id,h_events.hist_view_id,h_events.hist_workbook_id,h_events.historical_event_type_id,h_events.hist_group_id,h_events.hist_datasource_id,

 

h_projects.id as h_project_id,h_projects.name as h_project_name,h_projects.project_id as project_id,

 

proj.id as proj_id,proj.name as proj_name,proj.site_id as prooj_site_id,proj.parent_project_id as proj_parent_project_id,

 

h_sites.id as h_sites_id,h_sites.name as h_site_name,h_sites.site_id as site_id,

 

h_datasources.id as h_datasources_id,h_datasources.name as h_datasource_name,h_datasources.datasource_id as datasource_id,

 

h_workbooks.id as h_workbook_id,

h_workbooks.name as h_workbook_name,

h_workbooks.workbook_id as workbook_id,

 

datasource.id as datasources_id,datasource.name as datasource_name

from historical_events as h_events

inner join (select hs.id, hs.name, hs.site_id from public.hist_sites hs where hs.name='ICGCA') h_sites

on h_sites.id=h_events.hist_target_site_id

 

left outer join (select hp.id, hp.name, hp.project_id from public.hist_projects hp) h_projects

on h_projects.id=h_events.hist_project_id

 

left outer join (select id, name, site_id, parent_project_id from public.projects) proj

on proj.id = h_projects.project_id

 

left outer join (select hd.id, hd.name, hd.datasource_id from public.hist_datasources hd) h_datasources

on h_datasources.id=h_events.hist_datasource_id

 

left outer join (select hw.id, hw.name, hw.workbook_id from public.hist_workbooks hw) h_workbooks

on h_workbooks.id=h_events.hist_workbook_id

 

left outer join (select ds.id, ds.name from public.datasources ds) DataSource

on DataSource.id=h_datasources.datasource_id

3 件の回答