4 Replies Latest reply on Jun 13, 2019 1:04 AM by Donna Coles

    Get Favorites from Tableau Server Repository

    Robert Janezic

      Hello All,

       

      So I'm able to get the view/workbooks that people favorited. But now I need a way to get the URL info related to the workbooks/views URL but I've been having a hard time. I know that info is in other tables I just can't figure out the correct joins. Help would be greatly appreciated.

       

      Thank You

        • 1. Re: Get Favorites from Tableau Server Repository
          Patrick Van Der Hyde

          Hello Robert,

           

          I have moved this post to the Server Administration group where more Tableau Server Administrators are exposed to the question and may assist. 

           

          Patrick 

          • 2. Re: Get Favorites from Tableau Server Repository
            Donna Coles

            Hi Robert

             

            I use the following query to get a list of favourites per user which includes a URL

             

            SELECT row_number() over (partition by su.name order by position) as "Position", su.name as "Username", su.email as "Email",ali.useable_type as "Type",ali.usedobj_name as "Favourite",

              CASE WHEN ali.useable_type = 'View' THEN 'views/' || v.repository_url

            WHEN ali.useable_type = 'Workbook' THEN 'workbooks/' || w.repository_url

            END AS "Url"

              FROM asset_lists al

              JOIN asset_list_items ali ON ali.asset_list_id = al.id

              JOIN system_users su ON su.id = al.owner_id

              LEFT OUTER JOIN views v ON v.id = ali.useable_id AND ali.useable_type = 'View'

              LEFT OUTER JOIN workbooks w ON w.id = ali.useable_id AND ali.useable_type = 'Workbook'

             

            This generates data like :

            I think at some point from v8 to v10 the URL format changed a bit, so I've also then created a calc field in my data source to modify the URL when the string containts /sheet/

            I use this data source on our internal company landing page - we're currently running v10.1.1.  There's a possibility the postgres db may have changed since then so I can't vouch whether the query still works, but hopefully it gives you an idea of where to start.

             

            Donna

             

            PS - think the NULL rows are favourites where the view/workbook has since ben deleted from the sever - I filter these out in my Tableau view.

            • 3. Re: Get Favorites from Tableau Server Repository
              chaitanya dasari

              I think bold one in the query is right join

               

              SELECT row_number() over (partition by su.name order by position) as "Position", su.name as "Username", su.email as "Email",

              ali.useable_type as "Type",ali.usedobj_name as "Favourite",

               

               

                CASE WHEN ali.useable_type = 'View' THEN 'views/' || v.repository_url

               

               

              WHEN ali.useable_type = 'Workbook' THEN 'workbooks/' || w.repository_url

               

               

              END AS "Url"

               

               

                FROM asset_lists al

               

               

               

                JOIN asset_list_items ali ON ali.asset_list_id = al.id

               

               

                JOIN users u on u.id=al.owner_id

                JOIN system_users su ON su.id = u.system_user_id

               

               

                LEFT OUTER JOIN views v ON v.id = ali.useable_id AND ali.useable_type = 'View'

               

               

                LEFT OUTER JOIN workbooks w ON w.id = ali.useable_id AND ali.useable_type = 'Workbook'

              • 4. Re: Get Favorites from Tableau Server Repository
                Donna Coles

                Hi Chitanya

                Thanks for updating.  Techically, based on the schema I've just looked at, you're correct, though in practice I've found the (user) id and the system_user_id in the users table seem to match (certainly appears to be the case for our 2000+ users).

                Regards

                Donna