1 2 Previous Next 17 Replies Latest reply on Aug 2, 2016 6:09 PM by John Kuo

    Joining Tableau Server Usage to Extracts

    Luke Brady

      After several days of researching and writing SQL against the Tableau Server Postgres DB I finally came up with a query to join Tableau Server Usage to Extracts.  This will help you see which of your Extracts are being used and which are not.  If a large Extract is not used it can be a candidate for deletion (main reason I wrote this query).  After much Googling I was not able to find someone who had actually done this, in a single query, before so I am sharing it here.  Any feedback / corrections are welcome.  If you find this useful please comment so below.  I plan on joining this with the HTTP_REQUESTS data I am already saving off on a nightly basis.

       

      For an Updated List of joins - please see my other Tableau Server Postgres Post.

       

       

      SELECT * FROM
      /*
      Copyright (C) 02/29/2016, Luke Brady, Cerner Corporation
      This program is free software: you can redistribute it and/or modify
      it under the terms of the GNU General Public License as published by
      the Free Software Foundation, either version 3 of the License, or
      (at your option) any later version.
      
      
      This program is distributed in the hope that it will be useful,
      but WITHOUT ANY WARRANTY; without even the implied warranty of
      MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
      GNU General Public License for more details.
      
      
      You should have received a copy of the GNU General Public License
      along with this program.  If not, see <http://www.gnu.org/licenses/>.
      
      
      Joins used from:
      http://onlinehelp.tableau.com/current/server/en-us/help.htm#data_dictionary.html
      */
      (
        (
          (
            SELECT
            D5.ID AS "A_WB_ID",
            D4.caption AS "Caption (Conn WB)",
            D4.created_at AS "Date - Created At (Conn WB)",
            D4.dbclass AS "DB Class (Conn WB)",
            D4.dbname AS "DB Name (Conn WB)",
            D4.has_extract AS "Has Extract (Conn WB)",
            D4.id AS "ID - (Conn WB)",
            D4.keychain AS "Keychain (Conn WB)",
            D4.luid AS "LUID (Conn WB)",
            D4.name AS "Name (Conn WB)",
            D4.owner_id AS "ID - Owner (Conn WB)",
            D4.owner_type AS "Owner Type (Conn WB)",
            D4.password AS "Password (Conn WB)",
            D4.port AS "Port (Conn WB)",
            D4.server AS "Server (Conn WB)",
            D4.site_id AS "ID - Site (Conn WB)",
            D4.tablename AS "Table Name (Conn WB)",
            D4.updated_at AS "Date - Updated At (Conn WB)",
            D4.username AS "Username (Conn WB)",
            D5.created_at AS "Date - Created At (WB DS)",
            D5.id AS "ID - (WB DS)",
            D5.name AS "Name (WB DS)",
            D5.owner_id AS "ID - Owner (WB DS)",
            D5.owner_name AS "Owner Name (WB DS)",
            D5.project_id AS "ID - Project (WB DS)",
            D5.project_name AS "Project Name (WB DS)",
            D5.site_id AS "ID - Site (WB DS)",
            D5.size AS "Size (WB DS)",
            D5.system_user_id AS "ID - System User (WB DS)",
            D5.updated_at AS "Date - Updated At (WB DS)",
            D5.view_count AS "View Count (WB DS)",
            D5.workbook_url AS "Workbook URL (WB DS)"
            FROM
            DATA_CONNECTIONS D4
            INNER JOIN _WORKBOOKS D5 ON D5.ID = D4.OWNER_ID--Joins to DATA_CONNECTIONS with respect to Workbooks
            WHERE D4.OWNER_TYPE = 'Workbook'
          ) AS A
          LEFT JOIN (
            SELECT
            D2.asset_key_id AS "Asset Key (DS)",
            D2.connectable AS "Connectable (DS)",
            D2.content_version AS "Content Version (DS)",
            D2.created_at AS "Date - Created (DS)",
            D2.data_engine_extracts AS "Data Engine Extracts (DS)",
            D2.db_class AS "DB Class (DS)",
            D2.db_name AS "DB Name (DS)",
            D2.description AS "Description (DS)",
            D2.document_version AS "Document Version (DS)",
            D2.embedded AS "Embedded (DS)",
            D2.extracts_incremented_at AS "Date - Extracts Incremented At (DS)",
            D2.extracts_refreshed_at AS "Date - Extracts Refreshed At (DS)",
            D2.first_published_at AS "Date - First Published At (DS)",
            D2.id AS "ID - (DS)",
            D2.incrementable_extracts AS "Incrementable Extracts (DS)",
            D2.is_hierarchical AS "Is Hierarchical (DS)",
            D2.lock_version AS "Lock Version (DS)",
            D2.luid AS "LUID (DS)",
            D2.name AS "Name (DS)",
            D2.owner_id AS "ID - Owner (DS)",
            D2.project_id AS "ID - Project (DS)",
            D2.refreshable_extracts AS "Refreshable Extracts (DS)",
            D2.repository_data_id AS "ID - Rep Data (DS)",
            D2.repository_extract_data_id AS "ID - Rep Extract Data (DS)",
            D2.repository_url AS "Repository URL (DS)",
            D2.revision AS "Revisions (DS)",
            D2.site_id AS "ID - Site (DS)",
            D2.size AS "Size (DS)",
            D2.state AS "Sate (DS)",
            D2.table_name AS "Table Name (DS)",
            D2.updated_at AS "Date - Updated At (DS)",
            D4.caption AS "Caption (Conn DS)",
            D4.created_at AS "Date - Created At (Conn DS)",
            D4.dbclass AS "DB Class (Conn DS)",
            D4.dbname AS "DB Name (Conn DS)",
            D4.has_extract AS "Has Extract (Conn DS)",
            D4.id AS "ID - (Conn DS)",
            D4.keychain AS "Keychain (Conn DS)",
            D4.luid AS "LUID (Conn DS)",
            D4.name AS "Name (Conn DS)",
            D4.owner_id AS "ID - Owner (Conn DS)",
            D4.owner_type AS "Owner Type (Conn DS)",
            D4.password AS "Password (Conn DS)",
            D4.port AS "Port (Conn DS)",
            D4.server AS "Server (Conn DS)",
            D4.site_id AS "ID - Site (Conn DS)",
            D4.tablename AS "Table Name (Conn DS)",
            D4.updated_at AS "Date - Updated At (Conn DS)",
            D4.username AS "Username (Conn DS)"
            FROM
            DATASOURCES D2
            INNER JOIN DATA_CONNECTIONS D4 ON D2.ID = D4.OWNER_ID--Joins to DATASOURCES with respect to Datasources
            WHERE D4.OWNER_TYPE = 'Datasource'
          ) B
          ON A."DB Class (Conn WB)" = B."DB Class (Conn DS)"
          AND A."Name (Conn WB)" = B."Name (Conn DS)"
        ) C
        LEFT JOIN (
          SELECT
          D1.created_at AS "Date - Created At (Extract)",
          D1.datasource_id AS "ID - Datasource (Extract)",
          D1.descriptor AS "Descriptor (Extract)",
          D1.id AS "ID - (Extract)",
          D1.updated_at AS "Date - Updated At (Extract)",
          D1.workbook_id AS "ID - Workbook (Extract)"
          FROM
          EXTRACTS D1
        ) D ON D."ID - Datasource (Extract)" = C."ID - (DS)"
      ) E
      LEFT JOIN (
        SELECT
        D5.ID AS "F_WB_ID",
        D5.created_at AS "Date - Created At (WB)",
        D5.id AS "ID - (WB)",
        D5.name AS "Name (WB)",
        D5.owner_id AS "ID - Owner (WB)",
        D5.owner_name AS "Owner Name (WB)",
        D5.project_id AS "ID - Project (WB)",
        D5.project_name AS "Project Name (WB)",
        D5.site_id AS "ID - Site (WB)",
        D5.size AS "Size (WB)",
        D5.system_user_id AS "ID - System User (WB)",
        D5.updated_at AS "Date - Updated At (WB)",
        D5.view_count AS "View Count (WB)",
        D5.workbook_url AS "Workbook URL (WB)",
        D6.domain_id AS "ID - Domain (http User)",
        D6.domain_name AS "Domain Name (http User)",
        D6.domain_short_name AS "Domain Short Name (http User)",
        D6.friendly_name AS "Friendly Name (http User)",
        D6.id AS "ID - (http User)",
        D6.licensing_role_id AS "ID - Licensing Role (http User)",
        D6.licensing_role_name AS "Licensing Role Name (http User)",
        D6.login_at AS "Date - Login At (http User)",
        D6.name AS "Name (http User)",
        D6.site_id AS "ID - Site (http User)",
        D6.system_user_id AS "ID - System User (http User)",
        D7.action AS "Action (http)",
        D7.completed_at AS "Date - Completed At (http)",
        D7.controller AS "Controller (http)",
        D7.created_at AS "Date - Created At (http)",
        D7.currentsheet AS "Current Sheet (http)",
        D7.http_referer AS "http Referer (http)",
        D7.http_request_uri AS "http request URI (http)",
        D7.http_user_agent AS "http user agent (http)",
        D7.id AS "ID - (http)",
        D7.port AS "Port (http)",
        D7.remote_ip AS "Remote IP (http)",
        D7.session_id AS "Session ID (http)",
        D7.site_id AS "ID - Site (http)",
        D7.status AS "Status (http)",
        D7.user_cookie AS "User Cookie (http)",
        D7.user_id AS "ID - User (http)",
        D7.user_ip AS "User IP (http)",
        D7.vizql_session AS "VizQL Session (http)",
        D7.worker AS "Worker (http)",
        D8.caption AS "Caption (Views)",
        D8.created_at AS "Date - Created At (Views)",
        D8.id AS "ID - (Views)",
        D8.index AS "Index (Views)",
        D8.name AS "Name (Views)",
        D8.owner_id AS "ID - Owner (Views)",
        D8.owner_name AS "Owner Name (Views)",
        D8.site_id AS "ID - Site (Views)",
        D8.title AS "Title (Views)",
        D8.view_url AS "View URL (Views)",
        D8.workbook_id AS "ID - Workbook (Views)",
        D9.domain_id AS "ID - Domain (WB Owner)",
        D9.domain_name AS "Domain Name (WB Owner)",
        D9.domain_short_name AS "Domain Short Name (WB Owner)",
        D9.friendly_name AS "Friendly Name (WB Owner)",
        D9.id AS "ID - (WB Owner)",
        D9.licensing_role_id AS "ID - Licensing Role (WB Owner)",
        D9.licensing_role_name AS "Licensing Role Name (WB Owner)",
        D9.login_at AS "Date - Login At (WB Owner)",
        D9.name AS "Name (WB Owner)",
        D9.site_id AS "ID - Site (WB Owner)",
        D9.system_user_id AS "ID - System User (WB Owner)"
        FROM
        _WORKBOOKS D5
        INNER JOIN _VIEWS D8 ON D8.WORKBOOK_ID = D5.ID--Views contained in a Workbook
        INNER JOIN http_requests D7 ON D7.CURRENTSHEET = D8.VIEW_URL--Usage
        INNER JOIN _users D6 ON D6.ID = D7.USER_ID--Who hit the View
        INNER JOIN _users D9 ON D5.OWNER_ID = D9.ID--Workbook Owner
        ) F
      ON F."F_WB_ID" = E."A_WB_ID"
      
        1 2 Previous Next