11 Replies Latest reply on Apr 4, 2017 10:17 AM by Matt Coles

    Find Connected Workbooks to Published Data Source using Tableau Server Postgres DB

    Egor Larin

      Hello folks!

       

      I'm trying to create a custom report from Tableau Server Postgres DB and answer very simple question

      • Show all connected workbooks with owners information to Published Data Sources

      Very similar to that view:

       

      I've explored the forum but didn't find the correct answer

       

      What I tried

      • Used 3 main tables
        • data_connections
        • datasources
        • workbooks
      • Can't find the common ID for Published Data Sources and Data Sources used under connections in workbooks
      • I saw that somebody used that:
        • SELECT * FROM data_connections
          INNER JOIN workbooks ON data_connections.owner_id=workbooks.id AND data_connections.owner_type='Workbook'
          RIGHT JOIN datasources ON data_connections.caption=datasources.name AND data_connections.owner_type='Datasource'
        • But I'm not sure if JOIN on NAME is a good idea
      • I found that my users can rename connection to Published Data Source. Like that:
        • I have Published Data Source - Sample - Superstore (publ)
        • For one report (not-renamed_pds.twb) I'm using original name while creating my workbook - Sample - Superstore (publ)
        • For another (renamed_pds.twb) I'm using renamed Published Data Source while creating my workbook - renamed PDC

      • Tableau Server GUI will return me that info under both workbooks:

      • At the end I can see results of my JOINs like that, and of course it is incorrect because I'm missing relationships between original Published Data Source and renamed one (still it is the same - data connection was renamed):
      • Datasources table shows me that:
        • One record for general Published Data Source
        • Second and Third records are for Connected Workbooks (not-renamed_pds.twb & renamed_prd.twb)
      • _Datasoruces table shows me that:

        • Just one record only for general Published Data Source
      • Also I've tried to use LOD to find Data Source ID per Workbook but there is no common field between Connected Published Data Source and Published Data Source per Workbook ID
      • Data_connetions table has 2 rows for Workbook and Data Source that connects to it but again - no common id to combine wokrbooks under the same Published Data source

       

      So my question is very simple:

      • Is there any field to find relationships between Published Data Source and renamed/used in Data Connection?
      • How to show Connected Workbooks like we have in Tableau Server GUI? Result should look like that:

       

      Looking forward for any advise under that topic.

      Toby Erkson, Tamas Foldi, Matt Coles - I know you have a big experience on that, maybe you can give advise. I appreciate any help from your side.

       

      BR,

      Egor