7 Replies Latest reply on Mar 18, 2018 6:38 PM by Judi Stewart

    Need to find usage of embedded data sources

    SYED REHMAN

      Hi experts,

      I am trying to develop a multi-purpose meta data explorer - one of it's usage if to be able to search any Database/Server on our tableau server and find how many workbooks connecting to these databases and what is usage.

       

      I am able to do this for Shared/Published data sources but am stuck retrieving same information for Embedded data sources.

       

      Please see attached workbook in which first DS DataSourceStatistics is giving correct results, however, if I replace that with WorkbookStatistics then don't see correct results. right now only different in 2 queries is

       

       

               LEFT JOIN "public"."data_connections" "data_connections" ON ("datasources"."id" = "data_connections"."owner_id" AND "data_connections"."owner_type" = 'Datasource'

       

      OR

       

       

               LEFT JOIN "public"."data_connections" "data_connections" ON ("datasources"."id" = "data_connections"."owner_id" AND "data_connections"."owner_type" = 'Workbook'

       

      anyhelp in this will be appreciated

        • 1. Re: Need to find usage of embedded data sources

          Hey Syed,

           

          We're going through and cleaning up some older community posts without a response. Are you still looking for help with this question? If so, someone like Toby Erkson or Matt Coles could probably help. If you've found a solution, please share for other users to find!

           

          -Diego

          • 2. Re: Need to find usage of embedded data sources
            Matt Coles

            Funny that Diego should ping us on this right now, I'm busy building a data connection that attempts to make answer questions like this easier for my TC 16 session. The approach I am going to take will be to start with

             

            data_connections

                inner join to a Custom SQL for

                      (Datasources (all)

                           left joined to Datasources (published only)

                      )

                 left join to workbooks

                 then Sites, Users, and all that stuff for additional info.

             

            That way, you can answer questions about what workbooks and/or datasources connect to what DBs with a single query. Rather than having to say, "What workbooks connect to this SQL Server?" "Okay, now what published Data Sources connect to this SQL Server?" "Okay, now what workbooks connect to those published datasources?", you can answer your one question all at once. Plus setting row-level, user-based permissions are much simpler.

             

            I'm still hacking around, but I can share more as I get it figured out if you like.

            1 of 1 people found this helpful
            • 3. Re: Need to find usage of embedded data sources

              This is awesome, thanks for sharing!

              • 4. Re: Need to find usage of embedded data sources
                Toby Erkson

                I created this:  Server admin reports for workbooks, data connections, data sources, and their schedules

                 

                I think what Matt is creating will be better so I'm looking forward to it, it should be great (no pressure Matt ).

                • 5. Re: Need to find usage of embedded data sources
                  Matt Coles

                  It's pretty similar actually, Toby! The only real semantic difference is the additional layer of a Datasource (in a workbook) pointing to a published Datasource (on Tableau Server). The challenge is to make it understandable and easy to use. So I'm hiding all but the most necessary fields, beautifying those I'm keeping, commenting everything, and building out needed calcs in advance. Do you want me to send it to you, once it's less crappy and confusing? I'd love feedback.

                  1 of 1 people found this helpful
                  • 6. Re: Need to find usage of embedded data sources
                    Derek Cyr

                    HI Matt,

                     

                    I came across this post and was curious if you had made progress with this and perhaps could share it.  What I am envisioning is a subscription to a viz based on this work that effectively lists any "embedded" data sources to be sent to an administrator type person that could help publish any embedded data sources and remove them.  Eventually, the subscription could be set to only be shown when not empty...

                    • 7. Re: Need to find usage of embedded data sources
                      Judi Stewart

                      I believe what you need to do is this:

                       

                      LEFT JOIN "public"."data_connections" "data_connections" ON ("datasources"."id" = "data_connections"."datasource_id" AND "data_connections"."owner_type" = 'Workbook'

                       

                      The owner_id field on  data_connection where the owner_type is 'Workbook' points to the parent workbook that the data source is connected to - this is the same as the parent_workbook_id field on the datasources table.

                       

                      On a data_connection of type 'Datasource', the owner_id points to the PUBLISHED data source that owns the connection.

                       

                      To get the workbook name/details you then need to

                       

                      LEFT JOIN workbooks ON workbooks.id = data_connections.owner_id

                       

                      or you could use the workbooks view _workbooks if all you need is the name.