1 of 1 people found this helpful
#1 is possible. You will need to self-join datasources to datasources, on db_class = 'sqlproxy' and db_name as the repository URL for the published datasource. Workbooks will then join to the first datasources on owner_id and owner_type = workbook, if memory serves.
#2 is not possible, as the performance recordings aren't stored in the repository database. However, I think it it could be possible, somehow, to script out http calls to each view you want to render, append the performance_recording url to each call, and harvest the perf recording from each, as its own twbx. Beyond that you'll probably need to look in the Server logs for raw query strings and duration information.
Thanks for your valuable feedback.It helped me quite a lot and performance recording i am doing it separately. But for the first scenario can you share the screenshot of the table connection details which we normally do by drag and drop tables in the data window pane
It's easier to show in SQL actually, since you can't show all the join criteria without multiple screenshots. This shows all workbook datasource, and then p_ds contains the information on datasources the workbook connects to that are published.
FROM workbooks w
INNER JOIN datasources d -- all datasources a workbook connects to, published and embedded
ON w.id = d.parent_workbook_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 the workbook connects to
ON p_dc.dbname = p_ds.repository_url
AND p_ds.connectable = true
Thanks a lot Matt..:)