Blending example: classes, students, and grades

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.
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.

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.
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.

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