3 Replies Latest reply on Feb 21, 2019 4:25 AM by Jeevan TM

    Enumerate Workbook to Datasource connection / relation using postgres

    Jeevan TM

      Hi,

       

      I need information on the relation between a workbook and a datasource for some internal automation. My setup is as below:

       

      1. Datasources are of type extract

      2. Workbooks are of type live, connecting to the datasources

      3. Have access to postgres db

      4. Can get and put requests through REST api calls

       

      What I need is,  a mapping for all workbooks vs datasource, for example, which says workbook w1 sits on datasource d1 and d2, w2 sits on d2 and d3 and so on. I have tried getting this mapping using postgres db, but did not succeed. I used these tables 'workbooks', 'views', 'datasources' from postgres db. 'views' table has a column 'datasource_id' which by the description is empty and it is. 

        • 1. Re: Enumerate Workbook to Datasource connection / relation using postgres
          Jeevan TM

          Ok got it from postgres db.

           

          datasources.parent_workbook_id = workbooks.id

           

          I do not know why I could not find this earlier  -_-

          • 2. Re: Enumerate Workbook to Datasource connection / relation using postgres
            Ivan Monnier

            Hello Jeevan,

             

            thanks for the tip, I am also searching for this.

             

            I think there is a problem though.

            When I look in the "datasources' table, I see (per datasource)

            - 1 line with the "connectable" field set to TRUE, which corresponds to the published datasource

            - N lines with the "connectable" field set to FALSE, which corresponds to the different parent_workbook_id

             

            The problem is that it reflects the workbook using a datasource named exactly like the published datasource.

             

            If a user renames the datasource in his workbook (or more likely, duplicates he datasource so the name becomes mydatasource (2), the parent_workbook_id will lose the relation to the published datasource).

            Less likely, if a user renames another datasource with the exact name of the published datasource, you will think that it is related to this datasource.

             

            I did not find (yet) any lower level id to have an undisputable relationship between the workbooks and the datasources.

            It has to exist, it is shown on Tableau server.

             

            I hope someone will come with an idea, I have now several thousands of workbooks and hundreds of datasources, and I need to put some governance.

             

            Best regards

             

            Ivan

            • 3. Re: Enumerate Workbook to Datasource connection / relation using postgres
              Jeevan TM

              Hi Ivan,

               

              Thanks for pointing that out.

               

              I found this in some post,

               

              select ds.*, wb.* from recommendations.datasources ds

              inner join recommendations.workbooks wb on ds.rec_workbooks_id = wb.id

               

              Could you confirm if this helps? 'recommendations' is another schema which I don't think is well documented anywhere.