Looker in the Limelight

Two major announcements hit headlines in June 2019: Salesforce acquired Tableau Software and Google acquired Looker.

Tableau is better known and more widely used for data visualization. Tableau Public’s wide availability and use combine with its year-over-year place in the Gartner Magic Quadrant for Analytics and Business Intelligence to give it high visibility.

Google’s acquisition of Looker has raised many questions. What are its features and functions? How will it become integrated into Google’s offerings? Will it become an effective competitor to Tableau?

Thomas Curian, CEO Google Cloud, has emphasized that Looker extends Google’s “business analytics offering with two important capabilities.” The first is “the ability to define business metrics once in a consistent way across data sources.” The second is that Looker “provides users with a powerful analytics platform” as well as an “embedded analytics product to collaborate on business decisions.”

At Excella, we’ve used Looker to develop enterprise scale dashboards and other visual tools. We have insight into its advantages and disadvantages, particularly in comparison to other widely used dashboard tools like Tableau and PowerBI. In this post, I’ll take a look at how Looker measures up to these expectations by examining Looker as a development platform. I’ll focus on three core elements:

1. LookML – Looker’s semantic layer, which is where business metrics are defined “once in a consistent way across data sources.” This is one of Looker’s biggest differentiators.
2. Dashboard Usability – Looker emphasizes out-of-the-box dashboard solutions. These can accelerate delivery. However, this emphasis has led to limited options for customization of the UI. Developers accustomed to working in Tableau or a custom web framework will find this limiting.
3. Looker Discourse and Community Support – For quick answers to developer questions, look to these community portals for feedback

LOOKML – SEMANTIC LAYER

The feature that differentiates Looker from other data visualization tools is LookML – Looker’s modeling language for defining dimensions, calculations, and relationships in a SQL database.

Developers use LookML to create a semantic layer that defines the business logic. This provides users access to the exact level of information they need. It is especially valuable for larger organizations with complex rules and security protocols governing their data, as these rules and protocols can be embedded into the layer. LookML takes advantage of the power and familiarity of SQL, while allowing developers to easily write reusable, efficient, version-controlled code.

What this means for the end user: Imagine you’re visualizing data on website traffic for a large e-commerce site. Each page view generates a row of data in a database and millions of views each day from around the world lead to millions of rows. You want a dashboard to help you pull out key metrics, like a count of unique visitors in the last week.

Most data visualization tools require a developer to query the data directly and create their own calculation to derive numbers like a count of unique users. This is not trivial. There are many ways “unique” could be defined. If three different developers are working on separate dashboards, there is a risk that they could all calculate it differently, leading to three different numbers and confusion for the end user.

Looker is designed to avoid this problem. The semantic layer provides a central source for all metrics, so individual developers have no need to create custom calculations to power the dashboard. Any developer presenting a metric – like unique visitors within a given period – will have the metric already calculated. This ensures that it will be consistent across dashboards. Additional calculations and transformations are possible, but the likelihood of consistency across the enterprise is much greater.

LookML uses a well-defined structure illustrated in the graphic below. The largest unit is a Project. Views, Models, and Explores are essential subcomponents.

Views are tables where dimensions/measures are defined. A view can be connected to an existing table in a database or you can create a custom derived table with SQL. When a view is based on a database table, Looker will automatically generate LookML code to define all its fields. Developers can quickly start customizing and creating the dimensions and measures they need.

Custom derived tables accelerate the development cycle by giving developers the ability to create new SQL tables on the fly, without the need for any database changes. This is extremely helpful when a visualization requires the data to be structured or aggregated differently than how it is set up in the database.

The example below shows the LookML for a view called “users,” which is connected to a database table of the same name. It contains some auto-generated dimensions that have been edited and a new custom measure.

Customizations you can make include:

• Add descriptions to each field so users know what the data means.
• Create custom dimensions with SQL, which allows for more flexibility in how the data is displayed.
• Define dates as dimension groups to make it easy to view data by year, month, day, etc.
• Add measures to aggregate the data in different ways.
• Define drill fields to give users the ability to drill into a data point.

view: orders {
   
   # Connect the view to an existing SQL table
   sql_table_name: database_name.users ;;
   
   # Use descriptions to define terms for users
   dimension: user_id {
      description: "Unique ID for each user that has ordered"
      type: number
      sql: ${TABLE}.user_id ;;
   }
   
   # Use SQL to customize dimensions and measures
   dimension: user_name {
      description: "User first and last name"
      type: string
      sql: CONCAT(${TABLE}.first_name, ' ', ${TABLE}.last_name);;
   }

   # Use dimension groups to create a set of time-based dimensions
   dimension_group: order_date {
      description: "The data an order was processed"
      type: time
      timeframes: [date, week, month, year]
      sql: ${TABLE}.order_date ;;
   }

   # Use measures to aggregate data (sum, count, avg, etc.)
   # Use drill fields to let users drill into individual records
   measure: user_distinct_count {
      label: "User Count"
      description: "Distinct count of users"
      type: count_distinct
      sql_distinct_key: ${user_id};;
      drill_fields: [user_id, user_name]
   }

}

Models

The model defines the database connection, specifies which views are included, and defines how those views are joined in explores. The explore in the example model below uses the order_items view as its base and joins it to two other views based on their ID fields. Within the model, you can also define access requirements based on user attributes and create datagroups that determine when data gets refreshed.

# Define the SQL database connection
connection: "connection_name"

# The model includes all views and one dashboard
include: "*.view.lkml"
include: "sales_dashboard.dashboard.lookml"

# Define an explore, based on a view.
# Specify the relationship and which field(s) to join on.
explore: order_items {
   join: orders {
      relationship: many_to_one
      sql_on: ${orders.id} = ${order_items.order_id} ;;
   }

   join: users {
      relationship: many_to_one
      sql_on: ${users.id} = ${orders.user_id} ;;
   }
}