Establishing advanced BI capabilities with Google Looker: A step by step tutorial

Modern business intelligence (BI) tools provide users with a wide range of data consolidation, preparation, querying, and reporting capabilities that empower them to make better business decisions. However, the adoption process is non trivial even for the leading BI solutions.

In this article, we walk through the typical adoption process for Looker, the flagship BI toolkit that forms part of the Google Cloud suite, and discuss step by step how to use its powerful BI capabilities.

Data is usually spread across multiple data sources. This is not ideal since isolated data isn’t well suited to analysis and needs to be prepared – cleaned, transformed, and merged. This process can be very time consuming, time that could be much better used in actual data analysis to generate insights.

Looker solves this issue with LookML in addition to having many out of the box connectors that can be used across different data sources. LookML is essentially a language for describing dimensions, aggregates, calculations, and data relationships in a SQL database. Looker uses a model written in LookML to construct SQL queries against a particular database. One great benefit of this is that LookML models and views can be shared across multiple Looker projects and used for a variety of different purposes.

Another problem with the traditional approach is when someone is looking to perform data analysis, they need to have access to specific data sources and be able to write SQL queries. Looker solves this problem by enabling dashboards and ad-hoc queries to be created with just a few clicks, and zero knowledge of SQL. It’s also worth noting that Looker still allows users to run custom queries.

As mentioned earlier, the raw datasets are not ready for analysis. Looker simplifies the process of onboarding new data sources as LookML projects can be automatically generated. In most cases this will already be enough to start generating insights but of course, auto-generated models, views, and dimensions can be further refined to meet specific needs.

In the traditional data analytics world, SQL skills are essential as they’re one of the most common ways to explore data. However, this requires having access to all the required data sources as well as having high level SQL skills in order to be able to write complex queries.

Looker allows end users to construct queries, select required columns, group and calculate aggregates, and filter by date ranges etc. All with just a few clicks on the UI.

For example, using an e-commerce dataset, you can calculate order count per brand and category. The generated SQL query can then be viewed, manually validated, and updated as necessary.

Looker has a wide variety of different visuals (charts, maps, tables, etc.) that can be combined into dashboards. Let’s take a look at some of these dashboard examples, which can easily be produced by using a variety of included dashboard filters:

In most cases, we don’t want to just build a dashboard. The ultimate goal is to be able to react to key events. For example, we want to be notified when the number of total sales drops below a given threshold. Looker can either send email alerts or, as it comes with Slack integration out of the box, alerts can be configured to be sent to a specific Slack channel. Alerts can be event-based (e.g. when total_sales < 1000) or schedule-based (sent each hour, day, month, etc.)

An interesting way of integrating Looker into your existing applications is embedding data visualizations as iframes. This means you can embed individual looks, explores, or whole dashboards.

You have the option to embed Looker and let each user log in to the Looker instance in order to get access to the analytics therein. However, this integration method is far from seamless as you will have to manage separate user accounts and their access rights both in a Looker instance and your application. This results in the end users being forced to memorize and enter yet another password. Fortunately, there is a more user-friendly choice, Single Sign-On.

Unlike the architecture pattern generally known as Single Sign-On, the Looker single sign-on should more appropriately be named External Sign-On. It essentially allows you to delegate user authentication from Looker to your application. The delegation requires the Looker instance admin to set up a shared secret that is used by the application to cryptographically sign an elaborate embed URL. Among other parameters that pass variables, specify access rights for the session, select a model, etc, you can specify an internal Looker route to a predefined visualization. This way, instead of managing user accounts Looker only authenticates your application by checking the signature in the embed URL.

The obvious security concern around malicious users trying to share the iframe’s URL with unauthorized parties is overcome by the fact that such a URL is for one-time use only. The Looker instance will not return any HTTP document if an already used URL is accessed again.

What makes it a single sign-on of sorts, is the fact that you only need to pass a signed URL once in a session. All subsequent requests from the same browser (or another application capable of sending HTTP-cookies) can simply address unsigned Looker URLs like /embed/looks/1, which shortens overall response times.

Another use case for Looker is to fetch its analytical insights as data via its REST API. You can then use it at your convenience to visualize in your UI or trigger actions based on set values.

Data asset management and the process of getting actionable insights from data is becoming more and more challenging for modern enterprises. Building a data lake or data warehouse is not sufficient to provide the necessary toolset for businesses to make full use of their data. The right toolset for businesses means self-service, easy-to-onboard, self-explanatory tooling with minimal technical skills required. This then facilitates true easy operation of data.

By taking advantage of modern business intelligence software you can now start generating insights from data without needing applied SQL skills. Looker follows this path, leveraging LookML and providing the seamless ability to onboard data, build dashboards of varying complexity, and establish a flexible security policy. It’s also worth mentioning that Looker has out of the box integration with major data warehouses on the market, making it easy to connect to your data sources so you can start true data discovery.