Looker for visualization in GCP - Mini Project: Build & Apply

Looker for Visualization

📖 Scenario: You work as a data analyst at a retail company. Your manager wants you to create a simple sales dashboard using Looker to visualize monthly sales data. You will set up the data, configure a Looker view, create a LookML model, and finally build a Looker dashboard to display the sales trends.

🎯 Goal: Build a Looker project that includes a LookML view for sales data, a model to connect the view, and a dashboard that visualizes monthly sales totals.

📋 What You'll Learn

Create a LookML view file named sales.view.lkml with the sales data fields

Create a LookML model file named retail.model.lkml that includes the sales view

Define a measure for total sales in the view

Create a dashboard in Looker that shows monthly sales totals using the defined measure

💡 Why This Matters

🌍 Real World

Looker is widely used in companies to create interactive dashboards that help teams understand business data and make informed decisions.

💼 Career

Knowing how to build LookML views, models, and dashboards is essential for data analysts and business intelligence professionals working with Google Cloud Platform.

1

Create the LookML view for sales data

Create a LookML view file named sales.view.lkml with a dimension month of type string and a measure total_sales of type sum on the field sales_amount.

GCP

# Define the sales view with month dimension and total_sales measure
# Your code here

Hint

Use view to define the sales data structure. Add a dimension for month and a measure for summing sales_amount.

2

Create the LookML model to include the sales view

Create a LookML model file named retail.model.lkml that includes the sales view by adding it to the explore section.

GCP

view: sales {
  dimension: month {
    type: string
    sql: ${TABLE}.month ;; 
  }

  measure: total_sales {
    type: sum
    sql: ${TABLE}.sales_amount ;; 
  }
}

# Add the explore section to include the sales view
# Your code here

Hint

Add an explore block with the name sales to include the sales view in the model.

3

Create a Looker dashboard to visualize monthly sales

In Looker, create a dashboard named Monthly Sales Dashboard. Add a tile that uses the sales explore, selects the month dimension and the total_sales measure, and displays the data as a line chart.

GCP

# In Looker UI, create a dashboard named 'Monthly Sales Dashboard'
# Add a tile with 'sales' explore, select 'month' and 'total_sales', and choose line chart
# Your code here

Hint

Use the Looker UI to create the dashboard and add a tile with the sales explore selecting the month and total_sales fields. Choose line chart visualization.

4

Finalize the Looker project with dashboard filters

Add a filter to the dashboard tile to allow filtering sales by month. This filter should be named month_filter and connected to the month dimension in the sales explore.

GCP

# Add a dashboard filter named 'month_filter' linked to the 'month' dimension
# Your code here

Hint

Use the Looker dashboard filter feature to add a filter named month_filter that controls the month dimension in your sales explore.

Practice

1. What is the main purpose of Looker in data analysis?

easy

A. To write complex SQL queries manually

B. To create visual reports from raw data without coding

C. To store large amounts of raw data

D. To replace databases with spreadsheets

Solution

1. Step 1: Understand Looker's role

   Looker is designed to turn raw data into visual reports easily.

2. Step 2: Compare options

   Only To create visual reports from raw data without coding describes Looker's main function correctly; others describe unrelated tasks.

3. Final Answer:

   To create visual reports from raw data without coding -> Option B

4. Quick Check:

   Looker = Visual reports without coding [OK]

Hint: Looker = easy visuals from data, no coding needed [OK]

Common Mistakes:

• Thinking Looker stores data like a database
• Confusing Looker with manual SQL writing
• Assuming Looker replaces spreadsheets

2. Which syntax correctly defines a measure in LookML?

easy

A. measure total_sales { sum: sales }

B. measure total_sales = sum(${TABLE}.sales)

C. measure: total_sales { type: sum; sql: ${TABLE}.sales ;; }

D. measure: total_sales = sum(sales)

Solution

1. Step 1: Recall LookML measure syntax

   Measures use the format: measure: name { type: ...; sql: ... ;; }

2. Step 2: Check each option

   measure: total_sales { type: sum; sql: ${TABLE}.sales ;; } matches correct LookML syntax with colons and braces; others do not.

3. Final Answer:

   measure: total_sales { type: sum; sql: ${TABLE}.sales ;; } -> Option C

4. Quick Check:

   LookML measure syntax uses colons and braces [OK]

Hint: LookML uses colons and braces for measures [OK]

Common Mistakes:

• Using equal signs instead of colons
• Omitting braces {} around measure definition
• Incorrect SQL syntax inside measure

3. Given this LookML snippet, what will the measure average_price calculate?

measure: average_price {
  type: average
  sql: ${TABLE}.price ;; }

medium

A. Sum of all prices

B. Count of price entries

C. Maximum price value

D. Average of the price column

Solution

1. Step 1: Identify measure type

   The measure type is 'average', which calculates the mean value.

2. Step 2: Understand SQL reference

   The SQL references the price column, so it averages all prices.

3. Final Answer:

   Average of the price column -> Option D

4. Quick Check:

   Measure type 'average' = mean value [OK]

Hint: Measure type 'average' means mean value [OK]

Common Mistakes:

• Confusing average with sum or count
• Ignoring the measure type keyword
• Assuming it returns max value

4. You wrote this LookML measure but get an error:

measure: total_revenue {
  type: sum
  sql: ${revenue} ;; }

What is the likely cause?

medium

A. Missing ${TABLE} prefix before revenue

B. Incorrect measure type 'sum'

C. Extra semicolon after sql statement

D. Measure name cannot have underscores

Solution

1. Step 1: Check SQL reference format

   LookML requires ${TABLE}.column to reference table columns properly.

2. Step 2: Identify missing prefix

   The sql references ${revenue} without ${TABLE}., causing error.

3. Final Answer:

   Missing ${TABLE} prefix before revenue -> Option A

4. Quick Check:

   Column refs need ${TABLE}. prefix [OK]

Hint: Always prefix columns with ${TABLE}. in LookML SQL [OK]

Common Mistakes:

• Omitting ${TABLE}. before column names
• Thinking measure type 'sum' is invalid
• Assuming underscores are not allowed in names

5. You want to create a dashboard showing total sales and average order value per region. Which LookML approach is best?

hard

A. Define measures for total sales and average order value, then create an explore with region dimension

B. Write raw SQL queries for each metric and paste results manually into dashboard

C. Create separate dashboards for sales and orders without linking regions

D. Use only dimensions without measures to keep dashboard simple

Solution

1. Step 1: Identify needed metrics and grouping

   You need total sales and average order value as measures, grouped by region dimension.

2. Step 2: Use LookML best practice

   Defining measures and an explore with region dimension allows dynamic, reusable dashboards.

3. Step 3: Evaluate other options

   The other options do not provide integrated, maintainable visualization.

4. Final Answer:

   Define measures for total sales and average order value, then create an explore with region dimension -> Option A

5. Quick Check:

   Measures + dimension + explore = best dashboard [OK]

Hint: Use measures + dimension + explore for dashboards [OK]

Common Mistakes:

• Manually copying SQL results instead of using LookML
• Separating dashboards without common grouping
• Ignoring measures and using only dimensions