Data Studio is now Looker Studio. Looker Studio is still free, with the same features you already know. Looker Studio Pro offers improved asset management for enterprises, new team collaboration capabilities, and access to technical support. Learn more.

Blending example: classes, students, and grades

Learn how to solve a classic data blending use case.

Suppose you are a school administrator and that you store information about the classes offered, the students who enroll in those classes, and the grades they receive in each class. You can use Looker Studio to track and visualize this information by using data blending.

See a demonstration report

Copy and edit a live demonstration report to see how blending works.

(This report is only available in English.)

Questions to answer

This example answers the following questions you might have about the data:

  • Which students took which classes, and what grade did each student receive in each class?
  • What was the highest grade received in each class?
  • Which student received the highest grade in each class?

Sample data

Here's the data used in the examples.

Classes

class_id

class_name

c1

Underwater basket weaving

c2

Home fusion made easy

c3

How to train an attack iguana

c4

Learn SQL for fun and profit

 

Students

student_id

student_name

s1

Brett

s2

Rick

s3

Susanna

s4

Jennifer

 

Grades

student_id

class_id

grade

s1

c1

2

s2

c1

99

s3

c1

65

s4

c1

3

s2

c2

38

s3

c2

88

s4

c2

48

s1

c3

7

s4

c3

32

s1

c4

94

s2

c4

63

s3

c4

75

s4

c4

20

 

Follow along with the examples in this article by making a copy of this data using Google Sheets

Setup

The first step is to connect to your data by creating data sources in Looker Studio. The demonstration report uses Sheets for the underlying data, but you could just as easily store this data in a database, such as BigQuery or MySQL.

  1. Create a new report.
  2. Add 3 data sources corresponding to the sample data:
    1. Classes
    2. Students
    3. Grades

Learn how to create and edit data sources.

Question 1: Students, classes, and grades received

"Which students took which classes, and what grade did each student receive in each class?"

To answer this question:

  1. Create a new blend.
  2. Add a Grades table, with dimensions:
    1. student_id
    2. class_id
    3. grade
  3. Add a Students table, with dimensions:
    1. student_id
    2. student_name
  4. Add a Classes table, with dimensions:
    1. class_id
    2. class_name
  5. Join Grades to Students with student_id.
  6. Join Grades to Classes with class_id.
  7. Set both join operations to left outer.Example blend configuration showing left outer join between Grades and Students and Students Classes.
  8. Save the blend and close the editor.
  9. Add a table to the report with the student_name, class_name, and grade fields.
  10. Sort the table by student_name, descending.

Your table should look like this:

student_name

class_name

grade

Brett

Underwater basket weaving

2

Brett

How to train an attack iguana

7

Brett

Learn SQL for fun and profit

94

Jennifer

Underwater basket weaving

3

Jennifer

Home fusion made easy

48

Jennifer

How to train an attack iguana

32

Jennifer

Learn SQL for fun and profit

20

Rick

Underwater basket weaving

99

Rick

Home fusion made easy

38

Rick

Learn SQL for fun and profit

63

Susanna

Underwater basket weaving

65

Susanna

Home fusion made easy

88

Susanna

Learn SQL for fun and profit

75

 

Question 2: Highest grade by class

"What was the highest grade received in each class?"

To answer this question:

  1. Use the same blend as in Question 1.
  2. Add a table to the report
  3. Add class_name as a dimension and grade as a metric.
  4. Edit the grade field:
    1. Change the name to max_grade.
    2. Set the aggregation to MAX.
  5. Sort the table by max_grade, descending.

Example table configuration using blended data as the data source, with class_name as a dimension and max_grade as a metric.

Your table should look like this:

class_name max_grade
Underwater basket weaving

99

Learn SQL for fun and profit

94

How to train an attack iguana

32

Home fusion made easy

88

Question 3: Highest grade by student by class

"Which student received the highest grade in each class?"

To answer this question:

  1. Create a new blend.
  2. Add a Classes table, with dimensions:
    1. class_id
    2. class_name
    3. Add a Grades table, name it Grades 1, and add dimension class_id.
    4. Add grade as a metric, and set the aggregation to MAX.
    5. Rename grade to max_grade.
  3. Add the Grades table again, name it Grades 2, and add dimensions:
    1. student_id
    2. class_id
    3. grade
  4. Add a Students table, with dimensions:
    1. student_id
    2. student_name
  5. Use left outer for each join.
  6. Join Classes to Grades with class_id.
  7. Join Grades to Grades 2 with:
    1. class_id = class_id
    2. max_grade = grade
  8. Join Grades 2 to Students with student_id.Example blend configuration showing how to join Classes to Grades 1, Grades 1 to Grades 2, and Grades to Students.
  9. Save the blend and close the editor.
  10. Add a table to the report with the student_name, class_name, and max_grade fields.

Example table configuration using blended data as the data source, with student_name, class_name as a dimension and max_grade as a metric, sorted by max_grade.

Your table should look like this:

student_name

class_name

max_grade

Rick

Underwater basket weaving

99

Brett

Learn SQL for fun and profit

94

Susanna

Home fusion made easy

88

Jennifer

How to train an attack iguana

32

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
true
true
102097
false
false