I'm running a number of Custom Admin Reports on tableau - one which details all of the TS Data Connections (types, number of connections, workbooks missing data connections and so forth).
Please find attached the custom SQL code used and a screen of how the dashboard looks and the data connections.
-- All Datasources
d.id AS "Datasource ID" ,
d.luid AS "Datasource LUID" ,
d.name AS "Datasource Name" ,
d.repository_url AS "Datasource Repository URL" ,
d.owner_id AS "Datasource Owner ID" ,
d.project_id AS "Datasource Project ID" ,
d.site_id AS "Datasource Site ID" ,
d.is_hierarchical AS "Datasource Is Hierarchical" ,
d.is_certified AS "Datasource Is Certified" ,
d.data_engine_extracts) AS "Datasource Has Extract" ,
d.incrementable_extracts AS "Datasource Incrementable Extracts" ,
d.refreshable_extracts AS "Datasource Refreshable Extracts" ,
d.data_engine_extracts AS "Datasource Data Engine Extracts" ,
d.extracts_refreshed_at AS "Datasource Extracts Refreshed At" ,
d.db_class AS "Datasource DB Class" ,
d.db_name AS "Datasource DB Name" ,
d.table_name AS "Datasource Table Name" ,
WHEN true THEN false
END AS "Is Embedded in Workbook" ,
WHEN p_dc.dbclass = 'sqlproxy' THEN true
END AS "References Published Data Source" ,
d.parent_workbook_id AS "Parent Workbook ID" ,
-- "Underlying" Data Sources (e.g., if the data source in a Workbook points to a Tableau Server published datasource)
WHEN 'sqlproxy' THEN p_ds.id
END AS "Datasource ID (underlying)" , -- represents the underlying datasource id. If a workbook connects to a published datasource, use that ID rather than the datasource id referenced by the workbook.
WHEN 'sqlproxy' THEN p_ds.data_engine_extracts
END AS "Datasource Data Engine Extracts (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.refreshable_extracts
END AS "Datasource Refreshable Extracts (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.incrementable_extracts
END AS "Datasource Incrementable Extracts (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.extracts_refreshed_at
END AS "Datasource Extracts Refreshed At (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.extracts_incremented_at
END AS "Datasource Extracts Incremented At (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.name
END AS "Datasource Name (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.owner_id
END AS "Datasource Owner ID (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.project_id
END AS "Datasource Project ID (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.db_class
END AS "Datasource DB Class (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.db_name
END AS "Datasource DB Name (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.table_name
END AS "Datasource Table Name (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.is_hierarchical
END AS "Datasource Is Hierarchical (underlying)" ,
WHEN 'sqlproxy' THEN p_ds.is_certified
END AS "Datasource Is Certified (underlying)" ,
p_ds.repository_url AS "Datasource Repository URL (underlying)"
FROM datasources d -- all datasources, published and embedded
FROM data_connections -- used to obtain extract information on first-level datasources (e_dc = embedded data connections)
) as e_dc
ON d.id = e_dc.datasource_id
LEFT JOIN data_connections p_dc -- used to obtain information on what datasources (in a workbook) are connecting to what published datasources
ON d.id = p_dc.datasource_id
AND p_dc.dbclass = 'sqlproxy'
LEFT JOIN datasources p_ds -- just the published "conectable" datasources for supplemental information
ON p_dc.dbname = p_ds.repository_url
AND p_ds.connectable = true
Thank you for the PSQL code, I am using the same as my starting point. What I have learnt from my study is Tableau historical event measures the published data sources and embeded data source separately. For published one, the event is 'access datasource', for embedded one, the event name is actually 'access views'. So for now I am using number of view access as an indicator for embedded data sources view evaluation.
By taking that assumption, there is actual a shortcut to get the number of views, from _views and _datasources two views I can directly get naccess and nviews as number of access.
1. Use the TS Events data source to get this. It will save you all kinds of time.
2. The numbers will not necessarily tally, because the Tableau Server version does not count connections to a data source from Tableau Desktop--it only counts connections created when a viz is loaded on Tableau Server.
Gareth Blake-Coggins, thanks for sharing. The TS Data Connections data source will not show any information about usage access metrics, however. It can only answer questions about what workbooks reference what data sources, now how often they're accessed. TS Events is the right data source for that.
Thank you Matt! Miss out such gem on GitHub! Love to leverage on TS Events.
This is not very helpful. I went to GitHub and there are no instructions or way for me to see how to download the workbooks.
Where can I get the workbooks beside GitHub. thank you for any help on this.
Disregard. I copied the html/xml(?) code into a text document and then renamed it to be a workbook.
I'm sorry you found the reply unhelpful, John. GitHub, being oriented towards software developers, is a bit of a different environment than most members of the Tableau community are familiar with. This is a new page, so documentation is still pretty light at the moment. You can navigate it like this:
1. Scroll down on the main page to the Compatibility grid.
2. Find the column that matches your version of Tableau Server.
3. Find the row for the data source you want to use, and right-click the link under the appropriate column. Select "Save Link As" and save the .twb to your computer.
4. Open the .twb in Tableau Desktop. When prompted to connect, point it at your Tableau Server PostgreSQL repository database (which should have had access enabled ahead of time).
Hope that is helpful.
That number will include anyone accessing a data source independently of a published dashboard, as well--connecting remotely from Tableau Desktop, jumping right into authoring a new workbook via Web Edit, connecting via Prep, etc.