# Prepare Data for Looker Dashboards and Reports: Challenge Lab - GSP346 ## **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](https://www.cloudskillsboost.google/course_templates/628) 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: ![Top Count Cities With Most Heliports look](https://cdn.qwiklabs.com/b0Le7QQytAGQ5mEJ8%2FHjooHdQHGGtjuMwKELL4Rcbx0%3D align="left") ### 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: ![Facility Type Breakdown for Top Count States look](https://cdn.qwiklabs.com/Jzus0okehmHpVVrzQWlcJj1tzyPQScfJDUGVo%2FE1MzE%3D align="left") ### 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. ```apache ${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: ![States and Cities with Highest Percentage of Cancellations: Flights over 10,000 look](https://cdn.qwiklabs.com/YTDkSOGRYqOhlVziEItT5pCOB%2BlFFQhF1c1Pt34av7I%3D align="left") ### 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: ![Top Count Airports With Smallest Average Distance look](https://cdn.qwiklabs.com/nas7XRKkMPNDJ5p2irs9p94ecu5WdzE0vm5o1ITzKu0%3D align="left") 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](https://docs.looker.com/exploring-data/exploring-data/merged-results) 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 ### New Solution 1 %[https://youtu.be/ZCm7KPiieGY] ### New Solution 2 %[https://youtu.be/cBvcjJufN90] ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1758357391644/be6b724e-e24f-4b93-89ff-a9875ae15195.png align="center") **Look 1:-** ```apache explore: +airports { query: start_from_here{ dimensions: [city, state] measures: [count] filters: [airports.facility_type: "HELIPORT^ ^ ^ ^ ^ ^ ^ "] } } ``` **Look 2:-** ```apache explore: +airports { query: start_from_here{ dimensions: [facility_type, state] measures: [count] } } ``` **Look 3:-** ```apache explore: +flights { query: start_from_here{ dimensions: [aircraft_origin.city, aircraft_origin.state] measures: [cancelled_count, count] } } ``` --- ### Old Solution %[https://www.youtube.com/watch?v=nqm6FQTOICI&ab_channel=QUICKGCPLAB] --- ### 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**: ```apache ${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: ```plaintext 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 ```plaintext 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 ```apache 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 **dashbo**ard you **previously created** 6. Click **Add Look to Dashboard**. 7. For each of the **Looks you created**, add them to the **YOUR DASHBOARD NAME**.