1 2 Previous Next 27 Replies Latest reply on Oct 26, 2017 10:50 AM by Luke Brady

    Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join

    Natraj Kanamarlapudi

      Hi,

       

      Is it possible to query the Tableau Server Postgres database to see what workbooks use what data connections?


      I was able to login to Postgres, but unable to find a common join field between "Workbooks" & "datasources" or "data connections" tables.  I joined with site id (even though there is only one site) and also tried my luck by joining system user id, project id etc... but none of them gave me the correct results.  In some cases I get to see the workbook name but same list of data sources repeated for every workbook.


      I have referred to following links, may be I am going blind but was unable to find an answer


      http://onlinehelp.tableau.com/samples/en-us/data_dictionary/data_dictionary_8.2.5.html#comments_anchor


      https://apandre.wordpress.com/2014/11/30/monitoring-tableau-server/



      I have to create a table something similar to following:


      Workbook NameData source NameData source Server (SQL Server / Excel etc) Data source Table name


      Really appreciate your help with this, thanks


      regards

      Natraj

        • 3. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
          Toby Erkson

          Just an FYI for everyone:

          The Resources has a good amount of useful stuff everyone should look at:

          • 4. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
            David Mannering

            I haven't actually worked with this table, but it looks like you can join workbooks to datasources using the data_connections table.  Here is the layout:

             

             

            data_connections

             

            Describes various data sources that are included in either workbooks or datasources.

            Name

            Type

            Description

            id

            integer

            Primary key for the record.

            server

            text

            DNS server name to connect to.

            dbclass

            character varying

            Type of data connection (ie mysql, postgres, sqlproxy etc).

            port

            integer

            TCP port number of the connect (eg. 5432 for postgres).

            username

            character varying

            Username to use when connecting.

            password

            boolean

            True means password is embedded in owner (see owner_type and owner_id fields).

            name

            character varying

            Unique identifier for this data_connection.

            dbname

            character varying

            The database name to which the particular connection is linked.

            tablename

            character varying

            The name of the table in the database that this connection connects to.

            owner_type

            character varying

            One of "Datasource" or "Workbook". It is the type of object making the data connection.

            owner_id

            integer

            The ID of the workbook or datasource that is creating the data connection.

            created_at

            timestamp without time zone

            When this record was created.

            updated_at

            timestamp without time zone

            When this record was last updated.

            caption

            character varying

            Friendly name of this connection as seen in the Desktop data pane.

            site_id

            integer

            A foreign key reference to the site in which this data connection is contained. (sites table)

            keychain

            text

            Encode string in yaml format. An attribute bag that will match the embedded keychain connection this connection is associated with.

            2 of 2 people found this helpful
            • 5. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
              Natraj Kanamarlapudi

              Thanks for that David, I am unable to find a common field between the three tables you referred.  I joined basing on Site_id I don't get accurate results.  Logically there should be a workbook_id or view_id that should link to data_connections / data_sources table.

              • 6. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
                Dan Scott

                I believe that what David was referring to were the owner_type and owner_id fields.  owner_type is either "Workbook" or "Datasource," which indicates which sort of thing uses this data connection, while the value of owner_id is the id of the corresponding record in either the workbooks or datasources tables (as determined by the value in the owner_type field).  So, if I am reading this correctly, to join data connections to their corresponding workbooks, you could use something like this:

                SELECT * FROM data_connections AS dc

                INNER JOIN workbooks AS w

                     ON w.id = dc.owner_id

                WHERE dc.owner_type = 'Workbook'

                ORDER BY w.id

                 

                Ordering by w.id, above, ensures that data connections belonging to the same workbook will be listed adjacent to one another.

                 

                Similarly, to join datasources with data_connections use:

                SELECT * FROM data_connections AS dc

                INNER JOIN datasources AS ds

                     ON ds.id = dc.owner_id

                WHERE dc.owner_type = 'Datasource'

                ORDER BY ds.id

                 

                In the second query, the order by clause is not particularly significant, since I am pretty sure that a datasource can only have one data connection that is linked to it.

                2 of 2 people found this helpful
                • 7. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
                  Natraj Kanamarlapudi

                  Thanks heaps for that Dan, appreciate it.  I will give that a go today.  One more question.  Do you know if I can find the "custom sql" that was used in a workbook in Tableau's Postgres DB?  Or is there any other way to view the custom sql without opening the workbook?  Thanks

                  • 8. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
                    Dan Scott

                    You're welcome   As for the custom SQL, I am pretty sure that it can be found inside the twb file that defines the workbook.  If you can look at that file, you should be able to locate what you want.

                     

                    So, how do you look at the twb file for a workbook?  If you are working with Tableau Desktop, you may already have the twb or twbx file available.  If you have the twb, great; if you have the twbx, then you can unzip it (it's really just a zip file by another extension) and locate the twb file inside of it.

                     

                    It's trickier if you need to retrieve the twb file from Tableau Server, but it can be done easily enough, if you know how.  This kind of data is stored in the pg_largeobject system table.  If you look at the workbooks table, you will see two fields: repository_data_id and repository_extract_data_id. One or the other of these will be non-NULL (possibly both). For the workbook you want, pick whichever one is non-NULL (let's say whichever one it is has a value of 1234, just for convenience).  You can, then download the twb, or twbx like this (with your own folder/file location, of course):

                     

                    SELECT lo_export(repository_data.content, '\\\\myShare\\myFolder\\Blah\\myMadeUpFileName')

                    FROM repository_data

                    WHERE id = 1234;

                     

                    This SQL will cause either the twb, or the twbx, associated with the workbook to be saved into the specified file.  If it is a twb file, then you can just open it up and read it, but if that doesn't work, then it must be a twbx file, and you should unzip it, and then you will be able to locate the twb inside the unzipped result.

                     

                    There is a twist, though.  The file location that you specify for saving the result must be one that is accessible to the user that the PostgreSQL database runs under!  And, of course, it should also be accessible to you, else you won't be able to see the file that is created.  Some kind of public share is a reasonable possibility.

                     

                    Note1: Obviously, the query I showed could be more sophisticated, searching by workbooks by name (or whatever) and joining to the repository_data table, but the briefer form I gave should be enough to illustrate the idea.

                     

                    Note2: The story is essentially identical, if you want to talk about datasources, rather than workbooks.  The fields are even named the same.

                     

                    Note3: Oh, and I think I forgot an important detail. I believe that the lo_export command can only be run by the database superuser.  This may (or may not) be a problem for you.

                     

                    I hope this helps.

                    • 9. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
                      Tamas Foldi

                      Just to comment Dan's comment

                      Dan Scott wrote:

                       

                       

                      It's trickier if you need to retrieve the twb file from Tableau Server, but it can be done easily enough, if you know how.  This kind of data is stored in the pg_largeobject system table.  If you look at the workbooks table, you will see two fields: repository_data_id and repository_extract_data_id. One or the other of these will be non-NULL (possibly both). For the workbook you want, pick whichever one is non-NULL (let's say whichever one it is has a value of 1234, just for convenience).  You can, then download the twb, or twbx like this (with your own folder/file location, of course):

                       

                      SELECT lo_export(repository_data.content, '\\\\myShare\\myFolder\\Blah\\myMadeUpFileName')

                      FROM repository_data

                      WHERE id = 1234;

                       

                      This SQL will cause either the twb, or the twbx, associated with the workbook to be saved into the specified file.  If it is a twb file, then you can just open it up and read it, but if that doesn't work, then it must be a twbx file, and you should unzip it, and then you will be able to locate the twb inside the unzipped result.

                       

                       

                      It's not so tricky anymore. You can mount your tableau repository as file system with tableaufs. You will see your repository as a file system location where you can read, search, edit and copy your tableau workbooks and data sources as ordinary files.

                       

                      Just an example for your use cases grepping connection objects from a published twbx file

                       

                      mount.PNG

                      Again, you can access published twb(x)/tds(x) files as local ones with full read/write access on your local PC. The files are not copied to your system but streamed directly from postgres. No caching just pure, real time file access to pg_large objects as files.

                       

                      Here is an another thread how to use it: Re: Editing workbook XML after publishing to Server

                       

                      Hope this helps

                      • 11. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
                        Tamas Foldi

                        Then let me show you something, you might like it as it was done with your twb gem:

                        twb gem.PNG

                        It analyze workbooks directly on the servers but this is a different thread

                        • 13. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
                          Dan Scott

                          Sweet!  I especially like that the information is accessed using the readonly user credentials.  No more need for superuser credentials.

                          • 14. Re: Tableau Server Postgres "workbooks" table to "datasources" / "data connections" table join
                            Matt Coles

                            How am I just finding out about tableaufs now? What a great idea, Tamas!

                             

                            Question: Will this allow for auto-extraction of "packaged" content such as .twbx and .tdsx files? Right now, the presence of that type of content in the PostgreSQL repository makes it impossible to run XML path searches directly for all content, as you would need to unzip the packaged content to extract the XML definitions. I'm wondering (hoping) that your TableauFS does some magic to automatically handle that extraction, making it easy to perform search operations without the rigamarole of unzipping?

                            1 2 Previous Next