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
Hi @Krishna Moorthy Purru, it is a pity about Metadata API because it is not simply an interface but when enabled it is the backend feature to collect metadata related to the content catalog and lineage. So it is very handy.
For postgres repository only, you can have a look at the Tableau community curated datasources: https://github.com/tableau/community-tableau-server-insights