Awesome!! No more hacks.
This is a great and much appreciated! Is anyone taking the entirety of the Postgres data and storing it offline in Hadoop, Teradata or the like for permanent storage/reporting?
Awesome. Was waiting for this for long time. Finally, i can go ahead and have an informatica flow to ship all the metrics to my own db and have my custom admin view on top of it.
Fantastic ! Can't wait to see all the clever ways to use this
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
HTTP_REQUESTS r ON q.VIZQL_SESSION=r.VIZQL_SESSION and
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.