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

Before you click the Start Lab button

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

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

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

3. Click Open Looker.

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

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

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

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

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

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

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

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

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

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

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

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

2. Save this visualization to a Dashboard. Title your visualization: Busiest, Major Joint-Use Airports with Control Towers.

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

1. For each of the Looks you created, add them to the Plane and Helicopter Rental Hub Details Dashboard.

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

---

Task 1. Create Looks

Look #1: Most heliports by state

1. In the Looker navigation menu, click Explore.

2. Under FAA, click Airports.

3. Under Airports > Dimensions, click City.

4. Under Airports > Dimensions, click State.

5. Under Airports > Measures, click Count.

6. Under Airports > Dimensions, click on the Filter button next to the Facility Type.

7. In the filter window, set the filter to: is equal to HELIPORT.

8. On the Data tab, change Row limit to YOUR LIMIT

9. Click Run

10. Click on Airports Count to sort the values in descending order.

11. Click the arrow next to Visualization to expand the window.

12. Change visualization type to Table.

13. Click Run.

14. Click on the settings gear icon next to Run, and select Save > As a Look.

15. Title the NAME YOUR LOOK.

16. Click Save.

Look #2: Facility type breakdown

1. In the Looker navigation menu, click Explore.

2. Under FAA, click Airports.

3. Under Airports > Dimensions, click State.

4. Under Airports > Measures, click Count.

5. Under Airports > Dimensions, click on the Pivot button next to the Facility Type.

6. On the Data tab, change Row limit to YOUR LIMIT

7. Click on Airports Facility Type to sort the values in descending order.

8. Click the arrow next to Visualization to expand the window.

9. Change visualization type to Table.

10. Click Run.

11. Click on the settings gear icon next to Run, and select Save > As a Look.

12. Title the NAME YOUR LOOK.

13. Click Save.

Look #3: Percentage cancelled

1. In the Looker navigation menu, click Explore.

2. Under FAA, click Flights.

3. Under Aircraft Origin > Dimensions, click City.

4. Under Aircraft Origin > Dimensions, click State.

5. Under Flights Details > Measures, click Cancelled Count.

6. Under Flights > Measures, click Count.

7. Under Flights > Measures, click on the Filter button next to the Count.

8. In the filter window, set the filter to: Flights Count is greater than 10000.

9. Click Run.

10. Next to Custom Fields, click + Add. Select Table Calculation.

11. Copy or Paste the following in Expression field:

${flights.cancelled_count}/${flights.count}

12. Name The Calculation Percentage of Flights Cancelled

13. Click Default Formatting to change the format to Percent (3)

14. Click on Percent Cancelled to sort the values in descending order.

15. Hover over the Flights Count column, and click the gear icon that appears on the right side.

16. Click Hide from Visualization.

17. Hover over the Cancelled Count column, and click the gear icon that appears on the right side.

18. Click Hide from Visualization.

19. Change visualization type to Table.

20. Click Run.

21. Click on the settings gear icon next to Run, and select Save > As a Look.

22. Title the type or paste the following:

States and Cities with Highest Percentage of Cancellations: Flights over 10,000

23. Click Save.

Look #4: Smallest average distance

1. In the Looker navigation menu, click Explore.

2. Under FAA, click Flights.

3. Under Flights > Dimensions, click Origin and Destination.

4. Next to Custom Fields, click + Add. Select Custom Measure.

5. In Field to Measure, type or paste the following

Average Distance

6. Name The Custom Measure: Average Distance (Miles)

7. Click Save.

8. Hover over the Average Distance (Miles) column, and click the gear icon that appears on the right side.

9. Click Filter.

10. In the filter window, set the filter to: Average Distance (Miles) is greater than 0

11. Click on Average Distance (Miles) to sort the values in Ascending order.

12. On the Data tab, change Row limit to YOUR LIMIT.

13. Click the arrow next to Visualization to expand the window.

14. Change visualization type to Table.

15. Click Run.

16. Click on the settings gear icon next to Run, and select Save > As a Look.

17. Title the NAME YOUR LOOK.

18. Click Save.

Task 2. Merge results

1. In the Looker navigation menu, click Explore.

2. Under FAA, click Flights.

3. Under Aircraft Origin > Dimensions, click City.

4. Under Aircraft Origin > Dimensions, click State.

5. Under Aircraft Origin > Dimensions, click Code.

6. Under Flights > Measures, click Count.

7. On the Data tab, change Row limit to 10

8. Click Run.

9. In the top right pane of the Explore for your primary query, click Settings (Settings/gear).

10. Click Merge Results. This will open the Choose an Explore window.

11. In the Choose an Explore window, click Airports.

12. In the All Fields pane, click City, State and Code.

13. Under Airports > Dimensions, click on the Filter button next to the Control Tower (Yes / No).

14. In the filter window, set the filter to: Airports Control Tower (Yes / No) is Yes.

15. Under Airports > Dimensions, click on the Filter button next to the Is Major (Yes / No).

16. In the filter window, set the filter to: Airports Is Major (Yes / No) is Yes.

17. Under Airports > Dimensions, click on the Filter button next to the Joint Use (Yes / No).

18. In the filter window, set the filter to: Airports Joint Use (Yes / No) is Yes.

19. Click Run to see the results of the source query.

20. Click Save to merge the query into your primary query.

21. Click Run to view the results of your merged results.

22. Click the arrow next to Visualization to expand the window.

23. Change visualization type to Bar.

24. Click Run.

25. In the top right pane of the Explore for your Merged Results, click the gear icon.

26. Click Save to Dashboard.

27. For Title, type or paste

Busiest, Major Joint-Use Airports with Control Towers

28. Click New Dashboard.

29. For the dashboard name, type YOUR DASHBOARD NAME and click OK

30. Click Save to Dashboard.

Task 3: Save looks to a dashboard

1. In the Looker navigation menu, click Folders.

2. Select My folder.

You should see the Looks you just created.

3. Click on the YOUR DASHBOARD NAME.

4. Click on the settings gear icon next to Run, and select Save > To an existing dashboard.

5. Select the dashboard you previously created

6. Click Add Look to Dashboard.

7. For each of the Looks you created, add them to the YOUR DASHBOARD NAME.