Looker in the Limelight

by Haley Baldwin

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

Gartner_BI_2019.jpg

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.

lookml_hierarchy_diagram.png

Views

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} ;;
   }
}

Explores

Explores create a single Look (i.e. a visualization); Looks are combined to create a Dashboard. Once explores have been defined in the model, they become accessible to users through a graphical interface. To create a Look, users use Looker's graphical interface. Looker's documentation provides clear instructions.

A Look created in the graphical interface gets turned into a SQL query. Under the Data tab you can see the SQL query Looker generated, which is helpful for debugging and quality checks.

Looker’s default visualization types cover the basics (Table, Bar, Line, Scatterplot, Pie, Map, etc.) but don’t allow for much customization. The Map type is especially limiting if you need to display anything beyond basic geographical data.

For more advanced or custom charts, there is a Custom Visualization feature in Beta that allows Developers to create custom JavaScript visualizations (or utilize Looker’s pre-built JavaScript visualizations called Viz Blocks). This opens up a lot more possibilities for displaying your data, but you’ll need to create custom code and rely on the community for support. Looker’s support team won’t support your custom JavaScript. To take advantage of it, you’ll need proficient knowledge of JavaScript.

USABILITY

One of the disadvantages of Looker is the lack of UI customization. Looker is designed to generate dashboards based primarily on a set of chart templates. Other visualization tools grant more drag-and-drop flexibility to developers, allowing for customization of marks that can be used to generate novel and interesting visualizations.

In our User Experience testing, users of Looker dashboards have shared some common complaints:

  • The location and functionality of the filters bar is not intuitive.
  • It is easy to get lost when navigating between dashboards.
  • The Explore interface is not as intuitive as drag-and-drop visualization tools.
  • Difficulty changing a dashboard’s look and feel to provide consistency in data visualization styling.

Customizing isn’t impossible in Looker, but it requires working outside of the GUI. To customize the appearance of dimensions and measures, developers need to get creative with SQL and HTML at the view level. In order to do more customization of the look and feel, Looker visuals can be embedded into websites using an HTML iframe. With this approach, developers can customize the front-end to meet the end users’ needs and use Looker as the engine for modeling and data analytics.

Looker has limits, but they’ve been improving their visualization options with each release. Recent ones have added new chart types (Box Plot, Waterfall, Word Cloud) and enhanced the customization options available for existing chart types. Expect this trend to continue.

LOOKER DOCS, DISCOURSE & LIVE SUPPORT

Looker has a robust library of documentation that covers writing LookML, creating dashboards, using the API, and more. If you have an issue that is not addressed in the documentation, you can also turn to Looker Discourse—the community forum for Looker users. It’s the best place to share questions and make requests for product features.

The forum has become very active in recent years, and the addition of a Community Manager in 2019 has greatly increased the forum’s usefulness. Questions are regularly answered by other users or by Looker representatives who frequent the forum. The page has become a great place to learn tips and tricks from others.

One recent thread (“Looker Life Hacks”) discussed many helpful shortcuts and customization methods. There’s also a biweekly blog called “The Podium” that rounds up the best posts and spotlights important community members.

Looker also provides a Live Support feature that lets you message with a Looker representative at any time. It has been invaluable to have immediate access to an expert at the moment I have a question. They assist with troubleshooting, finding documentation, and reporting bugs.

CONCLUSION

Looker is an incredibly powerful data analytics platform. While tools like Tableau have an advantage because of their beautiful, interactive visualizations, Looker’s LookML is a unique modeling language that allows developers to create readable, reusable data models with built-in version control. Looker also has excellent resources for documentation and customer support. Looker’s main limitations come from the inability to customize visuals and dashboards to the degree you might expect. But it can be an extremely powerful tool for organizations that value data governance over complex visualizations.