PMOs quest for a perfect data reporting tool
Logging data is easy. But building an automated report pipeline out of it needs a lot of work and a framework 🙂
At Capillary Technologies, we have evolved radically when it comes to reporting data within and outside the engineering org. The Program Management group here is responsible for generating some of these reports and metrics.
Till a while ago, we were complete rookies with pivot tables on numerous sheets and excels floating around. It worked fine till the overall org size was small. As we started growing, some of the apparent scale issues started surfacing. Tens of sheets, hundreds of views, and thousands of requests for data slicing started hitting us. For e.g. an Engineering Manager for a team is interested in his/her team’s data, whereas a Director of Engineering would want to see the same data for a group of teams. Similarly, CXOs would be interested in another view of the same data. To be honest, it was extremely exasperating to keep this running!
The problem
If you’re the custodian of data or these reports, you waste a lot of energy and time in gathering and presenting it, unless you have a robust pipeline built.
We already had a good export mechanism to the database(PostgreSQL in our case) from some of the common sources like Jira, site24x7, etc. We built these export frameworks via tools like stitch data or through custom API integration. The problem was around viewing and granting the end user the capability to slice and dice the data.
Our earlier choices of some of the tools didn’t pick up steam as login/SSO, costing, etc became a big adoption barrier. Also, tools that require a user to raise an access request have poor adoption unless managed well.
What did we do about it?
Capillary uses Google Workspace as a collaboration and productivity tool. And with this license, you also get Looker Studio(formerly Google Data Studio) for free. Looker is also available with a personal Gmail account. Since we already had the underlying data, it was just a matter of creating the connectors and then integrating a visualization tool. While it took some time to learn the tool, once we did, we never stopped. Not only did we migrate all our existing reports to Looker, but we also added newer integrations like NewRelic, Google Sheets, etc to our suite. Our quest for the right reporting tool had a face with Looker.
What did it change?
With the advent of Looker in our tool catalog, the sheer tenacity of the team in handling the volume of data and reports is now impressive. Whether via scheduled email reports or a report link for slicing and dicing the data, we decentralize the data in a way that benefits everyone. Engineers spent less time in mining and mincing the data used for reporting. Non-Engineering teams have a sneak peek at some of these data that were hindered to them until now. And that too at a diminutive cost. So we bettered at both, the cost and productivity front.
Benefits of Looker Studio
- Accessibility — Login/SSO and sharing; can be accessed/shared via the organization’s google credential or your personal Gmail account. The creator either needs to publish the link or add the user with a desired access privilege.
- Various dimension view capabilities; more than 25 charts and views with different map views as well
- Cross-filtering of data; easy to interact with various datasets and understand how one affects another.
- 100+ data sources including community sources; connect to hundred’s of available data sources or create your own
- Scheduled email reports
- Capabilities to embed the report
- Cost; free with a Google workspace license
Typical Data Flow
The below image represents the typical data flow diagram from various sources to the looker UI. We use Stitch data, Hevo Data, or a custom API integration for extraction.
Integrated tools
Below are a few tools for which we have a working integration with Looker studio. This helps our engineering teams and management quickly access the latest data with a desired view. They use this data to plot trends and draw inferences from it.
Reports on Looker
Below is some key report that has been hosted on Looker and our team uses them heavily to gather the team’s health.
- Sprint Velocity
- Bug Trends and other details
- SLA Reports
- Service Uptime Report
- Service APM Report
The below chart shows the historical bug trend for different products (value hidden). The same is pulled from the Postgresql database where Jira data is dumped every hour. You can filter the data basis different field values and even write calculated fields basis your own custom logic.
The below chart pulls the uptime data for our services hosted on the status page. The data snapshot is stored in the Postgresql table every night by inserting the site24X7 API response.
Quick Look
A quick look at how you can start by creating a looker dashboard from your google sheet.
Known Gaps in Looker Reports
Though the tool works like a charm most of the time, there are some teething issues here and there which need to be fixed. E.g. improper error messages, authentication issues at times, and a few performance issues while loading large sets of data. I hope the team is working on these with utmost sincerity and that users will have a much better experience in the coming future.
Summary
To all those who are using Google workspace and have a need for a data reporting platform, Looker is a great alternative to start with. You can build custom pipelines in hours and present data on the dashboard in minutes. And all this is at no additional cost 🤩. Do try this and share your experience in the comments.
