9 Replies Latest reply on Jan 22, 2020 11:14 AM by Matt Coles

    how to find data source access (views) count from Repository

    ZHOU ZHANG

      Hi All,

       

      I want to create a custom admin view to show how popular the published data source by counting the number of access.

       

      the number is actually already shown on Data Source page (as below)

       

      how to get view counts.PNG

       

      May I know how to get the same from repository database? I tried historical_events but number seems not tally.

       

      thanks!

       

      Jimmy Zhang

        • 1. Re: how to find data source access (views) count from Repository
          Gareth  Blake-Coggins

          Hey Jimmy,

           

          I'm running a number of Custom Admin Reports on tableau - one which details all of the TS Data Connections (types, number of connections, workbooks missing data connections and so forth).

           

          Please find attached the custom SQL code used and a screen of how the dashboard looks and the data connections.

           

          You will need to have the Tableau Read Only Account enabled to set this up.Screenshot 2019-11-06 at 12.23.58.png

          Screenshot 2019-11-06 at 12.25.13.png

           

           

          SELECT

           

              -- All Datasources

              d.id                                        AS "Datasource ID" ,

              d.luid                                      AS "Datasource LUID" ,

              d.name                                      AS "Datasource Name" ,

              d.repository_url                            AS "Datasource Repository URL" ,

              d.owner_id                                  AS "Datasource Owner ID" ,

              d.project_id                                AS "Datasource Project ID" ,

              d.site_id                                   AS "Datasource Site ID" ,

              d.is_hierarchical                           AS "Datasource Is Hierarchical" ,

              d.is_certified                              AS "Datasource Is Certified" ,

              COALESCE(e_dc.has_extract,

                  d.data_engine_extracts)                 AS "Datasource Has Extract" ,

              d.incrementable_extracts                    AS "Datasource Incrementable Extracts" ,

              d.refreshable_extracts                      AS "Datasource Refreshable Extracts" ,

              d.data_engine_extracts                      AS "Datasource Data Engine Extracts" ,

              d.extracts_refreshed_at                     AS "Datasource Extracts Refreshed At" ,

              d.db_class                                  AS "Datasource DB Class" ,

              d.db_name                                   AS "Datasource DB Name" ,

              d.table_name                                AS "Datasource Table Name" ,

              CASE d.connectable

              WHEN true THEN false

              ELSE true

              END                                         AS "Is Embedded in Workbook" ,

              CASE

                  WHEN p_dc.dbclass = 'sqlproxy' THEN true

                  ELSE false

              END                                         AS "References Published Data Source" ,

              d.parent_workbook_id                        AS "Parent Workbook ID" ,

           

           

              -- "Underlying" Data Sources (e.g., if the data source in a Workbook points to a Tableau Server published datasource)

             

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.id

                  ELSE d.id

              END                                         AS "Datasource ID (underlying)" , -- represents the underlying datasource id. If a workbook connects to a published datasource, use that ID rather than the datasource id referenced by the workbook.

           

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.data_engine_extracts

                  ELSE d.data_engine_extracts

              END                                         AS "Datasource Data Engine Extracts (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.refreshable_extracts

                  ELSE d.refreshable_extracts

              END                                         AS "Datasource Refreshable Extracts (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.incrementable_extracts

                  ELSE d.incrementable_extracts

              END                                         AS "Datasource Incrementable Extracts (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.extracts_refreshed_at

                  ELSE d.extracts_refreshed_at

              END                                         AS "Datasource Extracts Refreshed At (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.extracts_incremented_at

                  ELSE d.extracts_incremented_at

              END                                         AS "Datasource Extracts Incremented At (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.name

                  ELSE d.name

              END                                         AS "Datasource Name (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.owner_id

                  ELSE d.owner_id

              END                                         AS "Datasource Owner ID (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.project_id

                  ELSE d.project_id

              END                                         AS "Datasource Project ID (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.db_class

                  ELSE d.db_class

              END                                         AS "Datasource DB Class (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.db_name

                  ELSE d.db_name

              END                                         AS "Datasource DB Name (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.table_name

                  ELSE d.table_name

              END                                         AS "Datasource Table Name (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.is_hierarchical

                  ELSE d.is_hierarchical

              END                                         AS "Datasource Is Hierarchical (underlying)" ,

              CASE p_dc.dbclass

                  WHEN 'sqlproxy' THEN p_ds.is_certified

                  ELSE d.is_certified

              END                                         AS "Datasource Is Certified (underlying)" ,

              p_ds.repository_url                         AS "Datasource Repository URL (underlying)"

           

          FROM datasources d  -- all datasources, published and embedded

              LEFT JOIN

                  (

                   SELECT

                       datasource_id ,

                       has_extract

                   FROM data_connections  -- used to obtain extract information on first-level datasources (e_dc = embedded data connections)

                   GROUP BY

                       datasource_id ,

                       has_extract

                  ) as e_dc

                      ON d.id = e_dc.datasource_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 for supplemental information

                  ON p_dc.dbname = p_ds.repository_url

                      AND p_ds.connectable = true

          • 2. Re: how to find data source access (views) count from Repository
            ZHOU ZHANG

            Hi Gareth,

             

            Thank you for the PSQL code, I am using the same as my starting point. What I have learnt from my study is Tableau historical event measures the published data sources and embeded data source separately. For published one, the event is 'access datasource', for embedded one, the event name is actually 'access views'. So for now I am using number of view access as an indicator for embedded data sources view evaluation. 

             

            By taking that assumption, there is actual a shortcut to get the number of views, from _views and _datasources two views I can directly get naccess and nviews as number of access.

             

            thanks!

             

            Jimmy

            • 3. Re: how to find data source access (views) count from Repository
              Matt Coles

              Two things:

               

              1. Use the TS Events data source to get this. It will save you all kinds of time.

              2. The numbers will not necessarily tally, because the Tableau Server version does not count connections to a data source from Tableau Desktop--it only counts connections created when a viz is loaded on Tableau Server.

              • 4. Re: how to find data source access (views) count from Repository
                Matt Coles

                Gareth  Blake-Coggins, thanks for sharing. The TS Data Connections data source will not show any information about usage access metrics, however. It can only answer questions about what workbooks reference what data sources, now how often they're accessed. TS Events is the right data source for that.

                • 5. Re: how to find data source access (views) count from Repository
                  ZHOU ZHANG

                  Thank you Matt!  Miss out such gem on GitHub!  Love to leverage on TS Events.

                  • 6. Re: how to find data source access (views) count from Repository
                    John Krieg

                    Matt,

                    This is not very helpful.  I went to GitHub and there are no instructions or way for me to see how to download the workbooks.

                    Where can I get the workbooks beside GitHub.  thank you for any help on this.

                     

                    Disregard.  I copied the html/xml(?) code into a text document and then renamed it to be a workbook.

                    • 7. Re: how to find data source access (views) count from Repository
                      Matt Coles

                      I'm sorry you found the reply unhelpful, John. GitHub, being oriented towards software developers, is a bit of a different environment than most members of the Tableau community are familiar with. This is a new page, so documentation is still pretty light at the moment. You can navigate it like this:

                       

                      1. Scroll down on the main page to the Compatibility grid.

                      2. Find the column that matches your version of Tableau Server.

                      3. Find the row for the data source you want to use, and right-click the link under the appropriate column. Select "Save Link As" and save the .twb to your computer.

                      4. Open the .twb in Tableau Desktop. When prompted to connect, point it at your Tableau Server PostgreSQL repository database (which should have had access enabled ahead of time).

                       

                      Hope that is helpful.

                      • 8. Re: how to find data source access (views) count from Repository
                        Michael Biediger

                        Hi Matt Coles,

                         

                        When we use TS Events and measure "number of events" for "Access Data Source," are we measuring the number of times a view has accessed the data source?

                        • 9. Re: how to find data source access (views) count from Repository
                          Matt Coles

                          That number will include anyone accessing a data source independently of a published dashboard, as well--connecting remotely from Tableau Desktop, jumping right into authoring a new workbook via Web Edit, connecting via Prep, etc.