3 Replies Latest reply on Nov 25, 2014 1:44 PM by diego.medrano Branched from an earlier discussion.

    Pulling data from the PostgreSQL

    Chauncey Brandom

      We are pulling data from postgres using the grant select hack from 8 postgres tables.  Data is stored in our staging Data Warehouse schema using Informatica.  Our data warehouse is on Oracle Exadata.  


      As time has permitted we've worked so far on total user activity time and workbook rendering time along with smaller quick queries.  Quite a bit of time has been spent understanding http_actions.  Still learning, but have made good progress.


      Chauncey Brandom

      Brinks Inc.

        • 1. Re: Pulling data from the PostgreSQL
          Sunil Tikar

          could you please assist me on understanding what is "grant select hack" by

          which your are pulling data from postgresql


          Thanks & Regards,

          Sunil Tikar




          The contents of this email are the property of PNC. If it was not addressed to you, you have no legal right to read it. If you think you received it in error, please notify the sender. Do not forward or copy without permission of the sender. This message may be considered a commercial electronic message under Canadian law or this message may contain an advertisement of a product or service and thus may constitute a commercial electronic mail message under US law. You may unsubscribe at any time from receiving commercial electronic messages from PNC at http://pages.e.pnc.com/globalunsub/

          PNC, 249 Fifth Avenue, Pittsburgh, PA 15222; pnc.com

          • 2. Re: Pulling data from the PostgreSQL
            Chauncey Brandom

            The 'grant select hack' is the documented process of getting readonly access for the Tableau user.


            This published document provides step by step instructions on how to grant the Tableau user SELECT rights to all the tables and views in the postgres database.  The new readonly user accomplishes the same.  I strongly urge you to ONLY grant SELECT rights.  Anything else could allow someone else to do harm to your server.





            • 3. Re: Pulling data from the PostgreSQL

              Hey is the SQL that was posted. Due to issues with splitting, the other half of this convo is in a different thread (Pulling data from the PostgreSQL) but let's try to keep the discussing here.



              Here is the SQL for render times.  I've edited back to postgres field names.  We run this against our Oracle data warehouse.  You do need to have the link between http_requests.currentsheet and views.views_url working if using extracted data.  (views.repository_url, '/sheets'::text, ''::text) AS view_url).



              Select k.SITE_ID, k.SITE, k.PROJECT_NAME, k.WORKBOOK_NAME,
              MAX( k.Num_seconds)  as MAX_seconds,
              MIN( k.Num_seconds)  as MIN_seconds,
              AVG( k.Num_seconds)  as AVG_seconds,
                SUM(k.viewevents) as Times_Generated
              q.SITE_ID, w.PROJECT_ID, MAX(w.id) as WORKBOOK_ID, s.Name as SITE, p.name as PROJECT_NAME, w.name as WORKBOOK_NAME, q.VIZQL_SESSION,
              SUM((q.COMPLETED_AT - r.CREATED_AT)*24*60*60)  as Num_seconds, 1 as ViewEvents
                  r.SITE_ID,  r.VIZQL_SESSION, r.currentsheet, MIN(r.COMPLETED_AT) as Completed_AT
                  From HTTP_REQUESTS r
                  WHERE r.COMPLETED_AT  >= '01-OCT-14'
                  and r.ACTION = 'performPostLoadOperations' 
                  and r.VIZQL_SESSION is not null
                  GROUP BY r.SITE_ID,  r.VIZQL_SESSION, r.currentsheet) q
              INNER JOIN
              r.completed_at <=q.completed_at
                LEFT OUTER JOIN sites s ON q.site_id = s.id
                LEFT OUTER JOIN _views v ON q.currentsheet = v.view_url and
                  r.site_id = v.site_id
                LEFT OUTER JOIN workbooks w ON v.workbook_id = w.id
                LEFT OUTER JOIN projects p ON w.project_id = p.id
              GROUP BY q.SITE_ID, w.PROJECT_ID, s.Name, p.name, w.name, q.VIZQL_SESSION ) k

              Group By k.SITE_ID,k.SITE, k.PROJECT_NAME, k.WORKBOOK_NAME


              I hope this helps everyone.  Let me know of any errors in my adjustments.