System and Content usage monitoring is necessary and part of day to day job for any tool administration. In the current trend, organizations are opting to switch their BI reporting tools or interested in having multiple BI tools according to their needs. When a new tool is introduced for their users, management always wanted to observe how their users are adopting to the new tool by comprehensive usage metrics and how system is responding with performance auditing. We can use these metrics also to identify data security and access breaches if any!
Looker has out of the box capabilities for most of Audit needs. In this blog, we discuss Looker’s out of the box features for content and system usage metrics and monitoring.
Looker admins and users should have see_system_activity permission to see Looker instance’s Usage panel, which connects directly to Looker’s underlying application database, called i__looker.
The i__looker database stores information about your Looker instance, including all Looks and dashboards saved on your instance, user information, and 90 days of historical queries:
Looker users with access to Usage can see below sections, with dashboards
1.User Activity
2.Content Activity
3.Performance Audit
4.Usage
All of these sections provides various level of information, explained below. The Looks further can be explored as we need.
1.User Activity :
Total Users, Users by Type, Weekly Querying Users and many.
2.Content Activity :
· Total Dashboards, Total Looks, Popular Explores, Explores by User Group, Dashboard Usage
· Unused Content, Unused Explores and many.
3.Performance Audit :
· Queries executed Last 30 days and variance with past period,% of Results fetched from cache
· Queries issued by Source (Source being action/visual which generates/triggers query.Ex:SQL Runner,Look..) ,Avg Run time by Source, Errors by Source
· Hourly Schedules, Hourly PDT(Persistent Derived Tables) builds, Schedule Failure ,PDT Overview and Exceptions and many.
4.Usage :
· Active users per day(last 2 weeks), top users(last 30 days),Query run time performance pivoted by seconds bucket
· List of Public Looks, list of top Looks, list of top dashboards,
· Scheduled Plans performance, Scheduled Plan status and many.
In addition to above metrics, we have options to further explore usage metrics using below URL’s
The History Explore
The History Explore includes information about each query run on your Looker instance in the last 90 days. Looker automatically truncates its data every 90 days.
https://<my.looker.com>/explore/i__looker/history , replace <my.looker.com> with the address of your Looker instance
history explore has so much information, interested columns being query.sql_text, query.model.query.explore, history.runtime_in_seconds.
One of the use case being
What Is the Average Runtime for Different Models?
https://<my.looker.com>/explore/i__looker/history?fields=query.model,history.average_runtime&f[history.result_source]=query&sorts=history.average_runtime+desc&limit=500&column_limit=50&query_timezone=America%2FLos_Angeles&vis=%7B%7D&filter_config=%7B%22history.result_source%22%3A%5B%7B%22type%22%3A%22%3D%22%2C%22values%22%3A%5B%7B%22constant%22%3A%22query%22%7D%2C%7B%7D%5D%2C%22id%22%3A0%2C%22error%22%3Afalse%7D%5D%7D&origin=share-expanded
we can build similar queries for Most Popular dashboards, unused Explore in last 90 days, unused fields in last 90 days and so on.
Unused content – https://<my.looker.com>/browse/unused
Looker providers similar features to explore
Dashboards – https://<my.looker.com>/explore/i__looker/dashboard
Users – https://<my.looker.com>/explore/i__looker/user
Looks – https://<my.looker.com>/explore/i__looker/look
Conclusion
Looker provides out of the box analytics for our Looker instance Usage and Performance monitoring. We have observed some of the features available in the area, please explore your interest in Looker guide https://docs.looker.com/admin-options/server/usage .
Read more about similar Self Service BI topic here and learn more about Visual BI Solutions Microsoft Power BI offerings here.
