Looker and Looker Studio on GCP Explained

Looker and Looker Studio sound like two editions of one product. They are two products with different purposes, and one of them has just changed its name: Google's documentation now says that Looker Studio is called Data Studio (Google Cloud: Data Studio documentation). This guide uses the new name and mentions the old one where it helps.

The short version: Data Studio is for building reports. Looker is for making sure the reports agree.

Two products

Data Studio

Data Studio turns a data source into charts with a drag and drop editor. You connect to BigQuery, a spreadsheet or a database, drop fields onto a canvas, add filters for viewers and share a link. For a first dashboard on Google Cloud, nothing is faster.

Three things are worth knowing before you rely on it.

Freshness is a setting. Data Studio keeps query results in memory and serves them again until a freshness threshold passes. For BigQuery the default is 12 hours, and it can be set to every 1 to 50 minutes or every 1 to 12 hours (Google Cloud: manage data freshness). A dashboard that looks stale is often doing what it was told.

Fresh data costs queries. When the stored result expires, the report queries BigQuery again, and those queries are billed like any others. See BigQuery cost optimization.

Sharing is by link and by person. Organisation-wide control comes with Data Studio Pro, the paid version, which adds permissions through IAM, team workspaces, a mobile app and support (Google Cloud: analyze data with Data Studio).

Looker

Looker starts from a different idea. Before anyone builds a chart, an analyst describes the data in LookML, Looker's modelling language: which tables exist, how they join, what the dimensions are and how each measure is calculated. Business users then pick dimensions and measures in an interface called an Explore, and Looker generates the SQL and runs it against the database (Google Cloud: introduction to LookML).

A measure in LookML looks like this:

view: invoice_line {
  dimension: quantity {
    type: number
    sql: ${TABLE}.Quantity ;;
  }
  measure: revenue {
    type: sum
    sql: ${TABLE}.UnitPrice * ${TABLE}.Quantity ;;
    value_format: "0.00"
  }
}

That definition lives in a file, in Git, reviewed like code. Every dashboard that shows revenue uses it, and changing it changes them all.

On Google Cloud, Looker is offered as Looker (Google Cloud core): instances hosted by Google and managed from the console, in Standard, Enterprise and Embed editions sold by annual contract. The Standard edition is for up to 50 internal platform users (Google Cloud: Looker (Google Cloud core) overview).

What a shared model prevents

The script builds a miniature semantic model in Python, to show the idea. It holds one definition of the joins and of each measure. A user asks for names, and the model writes and runs the SQL. The data is the Chinook sample database of music sales (Chinook database), in SQLite.

Then it does what happens without a model: two reports each write their own SQL for the word revenue.

It is a toy model. It is not Looker or LookML.

import sqlite3

db = sqlite3.connect("../../sqlds/Chinook_Sqlite.sqlite")

# One model: where the data is, how it joins, and what each business term means.
MODEL = {
    "from": ("InvoiceLine AS line "
             "JOIN Invoice AS inv ON inv.InvoiceId = line.InvoiceId "
             "JOIN Track AS track ON track.TrackId = line.TrackId "
             "JOIN Genre AS genre ON genre.GenreId = track.GenreId"),
    "dimensions": {"country": "inv.BillingCountry", "genre": "genre.Name"},
    "measures": {"revenue": "ROUND(SUM(line.UnitPrice * line.Quantity), 2)",
                 "orders": "COUNT(DISTINCT inv.InvoiceId)"},
}


def ask(dimensions, measures, limit=3):
    """A user picks names; the model writes the SQL."""
    columns = [f"{MODEL['dimensions'][d]} AS {d}" for d in dimensions]
    columns += [f"{MODEL['measures'][m]} AS {m}" for m in measures]
    sql = f"SELECT {', '.join(columns)} FROM {MODEL['from']}"
    if dimensions:
        sql += f" GROUP BY {', '.join(dimensions)} ORDER BY {measures[0]} DESC LIMIT {limit}"
    print(f"{' and '.join(measures)} by {dimensions[0]}" if dimensions else " and ".join(measures))
    for row in db.execute(sql):
        print("  " + "".join(f"{v:<10}" if isinstance(v, str) else
                             f"{v:>10.2f}" if isinstance(v, float) else f"{v:>10}" for v in row))


ask(["country"], ["revenue", "orders"])
ask(["genre"], ["revenue"])
ask([], ["revenue", "orders"])

# Two reports that each wrote their own SQL for the same word, revenue.
by_invoice = db.execute("SELECT ROUND(SUM(Total), 2) FROM Invoice").fetchone()[0]
joined = db.execute("SELECT ROUND(SUM(inv.Total), 2) FROM Invoice AS inv "
                    "JOIN InvoiceLine AS line ON line.InvoiceId = inv.InvoiceId").fetchone()[0]
print()
print(f"report A, SUM(Total) on invoices:              {by_invoice:>10.2f}")
print(f"report B, SUM(Total) after joining the lines:  {joined:>10.2f}   ({joined / by_invoice:.1f} times too high)")

revenue and orders by country
  USA           523.06        91
  Canada        303.96        56
  France        195.10        35
revenue by genre
  Rock          826.65
  Latin         382.14
  Metal         261.36
revenue and orders
     2328.60       412

report A, SUM(Total) on invoices:                 2328.60
report B, SUM(Total) after joining the lines:    20848.62   (9.0 times too high)

• Three questions, one definition. Revenue by country, by genre and in total all come from the same expression. The total, 2,328.60, matches the invoices in report A.
• Report B is nine times too high. It joined invoices to their lines and summed the invoice total. An invoice with nine lines was counted nine times. The SQL is valid and the number is wrong.
• Nothing warns you. The query runs, the chart draws, and the figure is plausible to anyone who does not know the real one.

This mistake has a name, a fan-out, and it is the commonest way two dashboards come to disagree. Looker's documentation notes that its sum measures are calculated properly even when a query's joins contain fan-outs (Google Cloud: LookML measure types). The deeper point is not that feature. It is that a measure defined once cannot be defined differently somewhere else.

Speed with BigQuery

Dashboards send many small queries, and people expect them to answer in a second or two. Three things help, with either tool:

• Point dashboards at reporting tables, not raw ones. A partitioned, pre-aggregated table answers faster and costs less. See materialized views in BigQuery.
• BI Engine is an in-memory layer in BigQuery that caches the data dashboards use most. It works through the BigQuery API, so it serves Looker, Data Studio and other BI tools alike (Google Cloud: introduction to BI Engine).
• Choose freshness honestly. A daily table does not need a dashboard that refreshes every minute.

For dashboards that must show the last few seconds, see building a real-time dashboard with GCP.

Choosing

1. One team, its own reports, a handful of dashboards? Data Studio.
2. Reports disagreeing about the same metric? Fix the definitions first. Put them in the warehouse as views or modelled tables, so every tool reads the same numbers. See Dataform vs dbt.
3. Many teams exploring the same data, needing the same definitions and controlled access? Looker.
4. Analytics embedded in your own application? Looker, in the edition built for it.

Step 2 is the one people skip. A semantic model does not have to be bought. A well-named reporting table with one row per order and one revenue column is a shared definition too, and Data Studio on top of it will agree with itself. Looker earns its price when the questions outgrow what a few fixed tables can answer.

Common mistakes

• Defining the same metric separately in ten reports.
• Joining a header table to its lines and summing the header.
• Pointing a dashboard at a raw, unpartitioned table.
• Setting one-minute freshness on a dashboard nobody watches live.
• Buying a BI platform to fix a modelling problem in the warehouse.
• Searching for Looker Studio documentation and concluding the product has gone. It has been renamed.

Dashboard reporting and KPI analysis are taught, with Power BI, in the Data Analytics course.

Sources

• Data Studio documentation (Google Cloud Documentation) — Google Cloud
• Analyze data with Data Studio (Google Cloud Documentation) — Google Cloud
• Manage data freshness (Google Cloud Documentation) — Google Cloud
• Introduction to LookML (Google Cloud Documentation) — Google Cloud
• Measure types (Google Cloud Documentation) — Google Cloud
• Looker (Google Cloud core) overview (Google Cloud Documentation) — Google Cloud
• Introduction to BI Engine (Google Cloud Documentation) — Google Cloud
• Chinook Database (GitHub, lerocha) — Luis Rocha