Overview
In a challenge lab you’re given a scenario and a set of tasks. Instead of following step-by-step instructions, you will use the skills learned from the labs in the course to figure out how to complete the tasks on your own! An automated scoring system (shown on this page) will provide feedback on whether you have completed your tasks correctly.
When you take a challenge lab, you will not be taught new Google Cloud concepts. You are expected to extend your learned skills, like changing default values and reading and researching error messages to fix your own mistakes.
To score 100% you must successfully complete all tasks within the time period!
This lab is recommended for students who have enrolled in the Prepare Data for Looker Dashboards and Reports skill badge. Are you ready for the challenge?
Setup
Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.
This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.
To complete this lab, you need:
- Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.
- Time to complete the lab---remember, once you start, you cannot pause a lab.
Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.
How to start your lab and sign in to Looker
When ready, click .
A new panel will appear with the temporary credentials that you must use for this lab.
If you need to pay for the lab, a pop-up will open for you to select your payment method.
Note your lab credentials in the left pane. You will use them to sign in to the Looker instance for this lab.
Note: If you use other credentials, you will get errors or incur charges.
Click Open Looker.
Enter the provided Username and Password in the Email and Password fields.
Important: You must use the credentials from the Connection Details panel on this page. Do not use your Google Cloud Skills Boost credentials. If you have your own Looker account, do not use it for this lab.
Click Log In.
After a successful login, you will see the Looker instance for this lab.
Challenge scenario
You are a data analyst intern for a private plane and helicopter rental company. For your first assignment, your new manager has tasked you with creating multiple dashboards to present at a company meeting. Specifically, you are required to use the provided FAA datasets to determine the best locations to set up new rental hubs.
Your manager wants the answer to the following questions:
Which states and cities have the most airports with heliports?
What is the facility type breakdown for the states with the most airports?
What are the airports and states with the highest percentage of flight cancellations with over 10,000 flights?
Where are the busiest, joint-use major airports that have control towers and what are their associated codes?
What are the origin and destination airports with the smallest average distance between them?
Task 1. Create Looks
Look #1: Most heliports by state
In this section, you will need to use the Airports dataset to build a visualization that answers the following question: Which states and cities have the most airports with heliports?
- Your visualization must have the following requirements:
A table with three columns: City, State, and Airports Count
Limit the results (rows) to the top 10 states
The Airports Count column should be in descending order (most to least)
The Facility Type should not be included in the visualization
Note: You will need to create a filter for the Facility Type.
- Save this visualization as a Look. Title it:
Top 10 Cities With Most Heliports. Your Look should resemble the following:
Look #2: Facility type breakdown
In this section, you will need to use the Airports dataset to build a visualization that answers the following question: What is the facility type breakdown for the states with the most airports?
- Your visualization must have the following requirements:
A table visualization with the Airports Count, State, and the corresponding Facility Types
Limit the results (rows) to the top 10 states
The Airports facility type column should be in descending order (most to least)
Note: You will need to use a Pivot.
- Save this visualization as a Look. Title it:
Facility Type Breakdown for Top 10 States. Your Look should resemble the following:
Look #3: Percentage cancelled
In this section, you will need to use the Flights dataset to build a visualization that answers the following question: What are the airports and states with the highest percentage of flight cancellations with over 10,000 flights?
- Your visualization must have the following requirements:
A table with three columns: Aircraft Origin City, Aircraft Origin State, and Percentage of Flights Cancelled
The Percentage of Flights Cancelled column must be created by a table calculation
A Flights Count filter set for > 10,000 Flights
The Cancelled Count and Flights Count should not be included in the visualization
The Percentage of Flights Cancelled column should be in descending order (most to least)
- For the table calculation, you can use the following formula. Be sure to name the calculation
Percentage of Flights Cancelled and for formatting, use Percent (3) so your work can be accurately graded.
${flights.cancelled_count}/${flights.count}
- Save this visualization as a Look. Title it:
States and Cities with Highest Percentage of Cancellations: Flights over 10,000. Your Look should resemble the following:
Look #4: Smallest average distance
In this section, you will need to use the Flights dataset to build a visualization that answers the following question: What are the origin and destination airports with the smallest average distance between them?
- Your visualization must have the following requirements:
A table with two columns: Origin and Destination, and Average Distance (Miles)
Select Average Distance field from a custom measure that calculates the average distance of flights.
- The custom measure should be named:
Average Distance (Miles)
An Average Distance (Miles) filter set for greater than 0.
The Average Distance (Miles) column should be in ascending order (least to most)
Limit the results (rows) to 9
- Save this visualization as a Look. Title it:
Top 9 Airports With Smallest Average Distance. Your Look should resemble the following:
Click Check my progress to verify the objective.
Create Looks
Check my progress
Task 2. Merge results
In this section, you will need to use both the Flights and Airports datasets to build a visualization that answers the following question: Where are the busiest, joint-use major airports that have control towers and what are their associated codes?
For this task, you will need to merge the two different datasets.
- Your visualization must have the following requirements:
A bar chart that includes the City, State, and Code, with the corresponding number of flights
Your Primary query must be from the Flights dataset, and include the Aircraft Origin City, Aircraft Origin State, Aircraft Origin Code, and Flights Count
The following Airports source query you will merge into must be from the Airports dataset, and include the State, City, and Code. Additionally in the Airports source query, you must use three filters on Control Tower, Is Major, and Joint Use. All of these should be true (yes)
Limit the results (rows) to the top 10 cities
Note: When you merge the results, review the dimensions that Looker used to match the queries. You should be merging Aircraft Origin City with Airports City, Aircraft Origin State with Airports State, and Aircraft Origin Code with Airports Code.
Save this visualization to a Dashboard. Title your visualization: Busiest, Major Joint-Use Airports with Control Towers.
Place this in a new Dashboard named Plane and Helicopter Rental Hub Details. Your visualization should resemble the following:
Click Check my progress to verify the objective.
Merge Results
Check my progress
Task 3: Save looks to a dashboard
In this section, you will need to add all of your created Looks to a Dashboard.
For each of the Looks you created, add them to the Plane and Helicopter Rental Hub Details Dashboard.
Verify the Dashboard has the four Looks you created, as well as the merged result visualization.
Note: Optionally, you can edit the layout and order of the visualization tiles.
Click Check my progress to verify the objective.
Save Looks to a Dashboard
Solution of Lab
New Solution 1
New Solution 2
Look 1:-
explore: +airports {
query: start_from_here{
dimensions: [city, state]
measures: [count]
filters: [airports.facility_type: "HELIPORT^ ^ ^ ^ ^ ^ ^ "]
}
}
Look 2:-
explore: +airports {
query: start_from_here{
dimensions: [facility_type, state]
measures: [count]
}
}
Look 3:-
explore: +flights {
query: start_from_here{
dimensions: [aircraft_origin.city, aircraft_origin.state]
measures: [cancelled_count, count]
}
}
Old Solution
Task 1. Create Looks
Look #1: Most heliports by state
In the Looker navigation menu, click Explore.
Under FAA, click Airports.
Under Airports > Dimensions, click City.
Under Airports > Dimensions, click State.
Under Airports > Measures, click Count.
Under Airports > Dimensions, click on the Filter button next to the Facility Type.
In the filter window, set the filter to: is equal to HELIPORT.
On the Data tab, change Row limit to YOUR LIMIT
Click Run
Click on Airports Count to sort the values in descending order.
Click the arrow next to Visualization to expand the window.
Change visualization type to Table.
Click Run.
Click on the settings gear icon next to Run, and select Save > As a Look.
Title the NAME YOUR LOOK.
Click Save.
Look #2: Facility type breakdown
In the Looker navigation menu, click Explore.
Under FAA, click Airports.
Under Airports > Dimensions, click State.
Under Airports > Measures, click Count.
Under Airports > Dimensions, click on the Pivot button next to the Facility Type.
On the Data tab, change Row limit to YOUR LIMIT
Click on Airports Facility Type to sort the values in descending order.
Click the arrow next to Visualization to expand the window.
Change visualization type to Table.
Click Run.
Click on the settings gear icon next to Run, and select Save > As a Look.
Title the NAME YOUR LOOK.
Click Save.
Look #3: Percentage cancelled
In the Looker navigation menu, click Explore.
Under FAA, click Flights.
Under Aircraft Origin > Dimensions, click City.
Under Aircraft Origin > Dimensions, click State.
Under Flights Details > Measures, click Cancelled Count.
Under Flights > Measures, click Count.
Under Flights > Measures, click on the Filter button next to the Count.
In the filter window, set the filter to: Flights Count is greater than 10000.
Click Run.
Next to Custom Fields, click + Add. Select Table Calculation.
Copy or Paste the following in Expression field:
${flights.cancelled_count}/${flights.count}
Name The Calculation Percentage of Flights Cancelled
Click Default Formatting to change the format to Percent (3)
Click on Percent Cancelled to sort the values in descending order.
Hover over the Flights Count column, and click the gear icon that appears on the right side.
Click Hide from Visualization.
Hover over the Cancelled Count column, and click the gear icon that appears on the right side.
Click Hide from Visualization.
Change visualization type to Table.
Click Run.
Click on the settings gear icon next to Run, and select Save > As a Look.
Title the type or paste the following:
States and Cities with Highest Percentage of Cancellations: Flights over 10,000
- Click Save.
Look #4: Smallest average distance
In the Looker navigation menu, click Explore.
Under FAA, click Flights.
Under Flights > Dimensions, click Origin and Destination.
Next to Custom Fields, click + Add. Select Custom Measure.
In Field to Measure, type or paste the following
Average Distance
Name The Custom Measure: Average Distance (Miles)
Click Save.
Hover over the Average Distance (Miles) column, and click the gear icon that appears on the right side.
Click Filter.
In the filter window, set the filter to: Average Distance (Miles) is greater than 0
Click on Average Distance (Miles) to sort the values in Ascending order.
On the Data tab, change Row limit to YOUR LIMIT.
Click the arrow next to Visualization to expand the window.
Change visualization type to Table.
Click Run.
Click on the settings gear icon next to Run, and select Save > As a Look.
Title the NAME YOUR LOOK.
Click Save.
Task 2. Merge results
In the Looker navigation menu, click Explore.
Under FAA, click Flights.
Under Aircraft Origin > Dimensions, click City.
Under Aircraft Origin > Dimensions, click State.
Under Aircraft Origin > Dimensions, click Code.
Under Flights > Measures, click Count.
On the Data tab, change Row limit to 10
Click Run.
In the top right pane of the Explore for your primary query, click Settings (Settings/gear).
Click Merge Results. This will open the Choose an Explore window.
In the Choose an Explore window, click Airports.
In the All Fields pane, click City, State and Code.
Under Airports > Dimensions, click on the Filter button next to the Control Tower (Yes / No).
In the filter window, set the filter to: Airports Control Tower (Yes / No) is Yes.
Under Airports > Dimensions, click on the Filter button next to the Is Major (Yes / No).
In the filter window, set the filter to: Airports Is Major (Yes / No) is Yes.
Under Airports > Dimensions, click on the Filter button next to the Joint Use (Yes / No).
In the filter window, set the filter to: Airports Joint Use (Yes / No) is Yes.
Click Run to see the results of the source query.
Click Save to merge the query into your primary query.
Click Run to view the results of your merged results.
Click the arrow next to Visualization to expand the window.
Change visualization type to Bar.
Click Run.
In the top right pane of the Explore for your Merged Results, click the gear icon.
Click Save to Dashboard.
For Title, type or paste
Busiest, Major Joint-Use Airports with Control Towers
Click New Dashboard.
For the dashboard name, type YOUR DASHBOARD NAME and click OK
Click Save to Dashboard.
Task 3: Save looks to a dashboard
In the Looker navigation menu, click Folders.
Select My folder.
You should see the Looks you just created.
Click on the YOUR DASHBOARD NAME.
Click on the settings gear icon next to Run, and select Save > To an existing dashboard.
Select the dashboard you previously created
Click Add Look to Dashboard.
For each of the Looks you created, add them to the YOUR DASHBOARD NAME.
