Overview

In this lab, you work with Looker Studio to visualize a dataset for Aircraft movements. The data is from the Automatic Dependent Surveillance–Broadcast (ADS-B) system that aircraft use to broadcast their location and other data, enabling them to be more easily tracked. This is the same information that applications like "Flightradar24" use to visualize plane locations.

All aircraft have a unique ID hex value (icao) and this can be cross-referenced to a lookup table to establish airplane details, such as make, model, operator, etc.

You will be provided BigQuery tables of both historical ADS-B aircraft positional data, and of aircraft details. You then use Looker Studio to explore and visualize specific flights.

Key Concepts

• You are provided two sets of data to use in this part of the lab: aircraft details and ADS-B.
• The aircraft_details_matched table is a lookup table containing aircraft make, model, operator, etc.
• The aircraft_data_sample table contains ADS-B data collected by an ADS-B receiver located in the UK near Heathrow Airport. It has been filtered to allow it to be more easily cross referenced with the supplied details table. It contains timestamp, altitude, and positional data.
• Aircraft are identified by an icao value; this is a unique aircraft designator
• The icao number is provided in the Hex column in the aircraft_data_sample table, and the icao column in the aircraft_details_matched table
• Aircraft location is provided in BigQuery ST_GEOGPOINT POINT(lat, lon) format
• The plane altitude alt column units are in feet

Objectives

You practise data connection, data visualization, data blends, and interactive controls in Looker Studio in a sequence reflecting a real-world business report creation process.

Note: The lab dataset is a historical snapshot and the values would differ if you were to run the analysis on live data.

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.

What you need

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).
• Time to complete the lab.

Note: If you have a personal Google Cloud account or project, do not use it for this lab. Note: If you are using a Pixelbook, open an Incognito window to run this lab.

Log in to Google Cloud Console

1. Using the browser tab or window you are using for this lab session, copy the Username from the Connection Details panel and click the Open Google Console button.

Note: If you are asked to choose an account, click Use another account.

2. Paste in the Username, and then the Password as prompted.
3. Click Next.
4. Accept the terms and conditions.

Since this is a temporary account, which will last only as long as this lab:

• Do not add recovery options
• Do not sign up for free trials

5. Once the console opens, view the list of services by clicking the Navigation menu () at the top-left.

Task 1. Launch Looker Studio and create a report

1. In a new incognito browser tab, open Looker Studio using the URL below:

https://lookerstudio.google.com

2. Take a moment to get familiarized with the Looker Studio interface.

3. Verify that the account you are using in Looker Studio is the lab account supplied to you in this lab. If you aren't using the correct account, click the Avatar and select the correct account from the list.

   

4. Let's create a new report. In the upper-left corner of the interface, click + Create and select Report.

5. Looker Studio may ask you for some final information to finalize your account setup. If you instead see a blank report asking you to select a data source, skip to step 7. Otherwise, select your Country from the drop-down box, enter Dev for the company name, and select the I agree checkbox. Click Continue.

   

6. In the next updates to receive dialog, click No to all and then Continue.

7. After creating a new blank report, Looker Studio prompts you to set up the initial data connection. The data for this exercise is stored in two tables stored in Google Cloud's BigQuery data warehouse, so, scroll (if necessary) and click the BigQuery icon to select it as the data source connector. Alternatively, you could use search to locate BigQuery and then click the icon.

8. Authorize your temporary lab user account to allow it to connect to your Google Cloud project.

9. Now set the Google Cloud project containing your BigQuery source tables. Click My Projects and select your lab Project . Do not select Qwiklabs Resources.

10. In BigQuery, tables are grouped into datasets. Select the Looker Studio Lab dataset, the aircraft_data_sample table, and finally click Add.

11. In the confirmation pop-up box, click Add to Report.

Click Check my progress to verify the objective. Launch Looker Studio and create a report

Task 2. Clean up the default report (Optional)

When you first build a report and attach a new data source, Looker Studio auto-adds a table chart displaying data from the new data source. This may occasionally be useful, but in this particular instance the table doesn't help tell a meaningful story. That's something we need to address. If there is no auto-added table chart, move to Task 3 after you finish the first step here to change the report title.

For reference, the aircraft_data_sample table has the following fields:

Name	Description
Alt	Aircraft altitude in feet
date	Date of event
Hex	Aircraft icao number
pt	Geopoint location of aircraft
timestamp	When exactly this reading was taken

In the property windows, green fields are what Looker Studio considers to be dimensions, and blue fields are metrics. Looker Studio allows you to treat any dimension as a metric when creating charts.

In this task, you give your report a title and do a little cleanup up before building the dashboard in ernest.

1. At the top-left corner of Looker Studio, click the report title, which usually defaults to Untitled Report, to edit it. Change the report title to Flight Planning. This also becomes the name of the report file.

   

2. Remove the unhelpful table chart by selecting it, and at the top right of the chart, clicking the three dots, then Delete. Simply selecting the chart and pressing Delete will also work.

   

3. Static elements (text, images, lines, etc.) can frequently clarify the story being told by a particular report. Let's give the report a text title.

   From the Insert menu, scroll down and select Text. Place the text box in the top-left corner of your report and enter the text Flight Planning. Feel free to use the Text Properties panel on the right of the interface set a color and font of your choice. It's the title, so make it large enough to denote that.

Task 3. Add meaningful charts and labels to your dimensions and metrics

In this task, you use a table chart to display a list of raw flight data, including altitudes, timestamps, and airplane icao (Hex) identification numbers. You also gain experience configuring the data and style properties of a chart control.

1. Add a table to display raw flight data. Click Add a chart and select Table. Arrange the table so it sits under your title, is the full width of the report, and it stretches about halfway down the page.

   

2. In the chart's properties panel on the right of the interface, locate the Metric section. Looker Studio defaulted the metric to Record Count, but we don't need a metric for this chart. Hover over the Record Count metric and click X to remove it.

3. Just above the Metric property section, in the Dimensions panel, click + Add dimension, then select Alt (altitude). Notice how each dimension displays another column of data pulled from the BigQuery data source table in your chart.

   Instead of using the + Add dimension button to add columns to the chart, you can also simply drag fields from the Data column to the right of the properties pane and drop them in chart's Dimension list where you want them to go.

4. Repeat the above step, or use drag and drop, and add the timestamp dimension. If it's not already in the Dimension list, also add Hex (aircraft icao number).

5. Use drag the dimensions in the Dimension section to arrange them in Alt, timestamp, Hex order.

6. Return to the chart Properties panel, locate Sort and set the Alt dimension to sort descending. Verify that your chart is now sorted by descending altitude (remember, the value is in feet).

7. At the top of the properties panel, switch to the Style tab. Scroll to the Table Body section and deselect Row numbers. This removes the first column of row numbers from the chart.

8. Click the table and then expand the three dot More menu in its upper-right corner. Select Resize columns >> Fit to data.

9. You can now identify the highest altitude that has been captured. What value is it?

   

10. Click the Alt heading in the chart and switch the altitude sort to ascending order. Observe the null (missing) values. This is a result of some ADS-B messages not containing altitude data.

Task 4. Add a second dataset

The flight data we used in the last task had essential flight information (id number, altitude, and timestamp) but it did not provide any information about the aircraft itself (type, operator, etc.). In this task, you add a second data source and a second table chart to your report, this one containing raw information about the planes themselves.

1. To facilitate looking up more information about the aircraft, add another data connection to a new table in BigQuery. In the menu bar, click Add data to insert a new data source, and select BigQuery.

2. Again select the data source Google Cloud project by clicking My Projects and selecting the Project . Do not select Qwiklabs Resources.

3. Select the Looker Studio Lab dataset dataset followed by the aircraft_details_matched table, then click Add and finally, Add to Report.

4. That takes care of your report's access to the second table of data. Now, add another table chart to your report, this one filling the bottom half of the page. Click Add a chart and select Table. Position the new table under the table you created in the last task, but with enough space between them to allow you to add a title above the second table.

5. In the new table's Chart properties panel, ensure the data source (at the very top) is set to aircraft_details_matched.

6. Set the dimensions for the table to the following columns, in the following order:

Dimension
icao
operator
mdl
type
regid

7. Remove the Record Count in the Metric section.

8. Select the Style tab in the Properties panel and scroll to Table Body, and then deselect Row numbers to remove these from the chart, then use the chart's triple dot menu to fit the column sizes to the data.

9. To help identify the two tables, place a small text label above each one. Use Raw Flight Data for the top table and Raw Aircraft Details for the bottom. You may need to rearrange the two tables to make the space for the labels. Also, this time you might use the Text control icon on your Looker Studio toolbar.

   

10. Your completed report should resemble the following:

    

Click Check my progress to verify the objective. Add a second dataset

Task 5. Add and name pages in your report

One good way to split up a complex report is to break it into pages, each a logical group of charts. In this task, you add several pages to your report for use in later exercise steps.

1. Start by inserting a new page into your report. Use Page >> New Page. Looker Studio automatically switches your focus to the new page. You also now see the page numbers down the left side of the interface.

2. Repeat the above process to create one more pages. In the end, you should have a total of three pages.

3. Working with a report whose pages are simply numbered might not be ideal. To make your report paging clearer to the report reader, it's usually best to give each page a title. Let's set that up now.

   Open the Report Pages pane using Page >> Manage pages. Either using double-click, or the triple dot menu next to each page title in Report Pages, rename all the pages as follows:

   Page Number	Name
   1	Raw Data
   2	Blended Data
   3	Flight Operations

   

4. You can close the Report Pages pane when finished with the retitling. Now, if you mouse hover over the page numbers on the left of the interface, you see the new page titles as tool tips. Also, at the bottom of the report on the left is an > arrow. If you expand the left page navigation bar, you see the full titles.

Task 6. Blend your datasets

Blending datasets are the Looker Studio equivalent to relational database table joins. It allows you to combine (blend) data from multiple data sources into a single view. In this task, you blend your two datasets together and then visualize the results. Lastly, you use the new report page to answer some questions.

1. Select page 2 of your report (Blended Data). At the top of the report page, preferably in about the same spot as the title on page 1, add the title Flight Information using a text control. If you'd like to maintain consistency, if you copy the title off page one, and paste it into page two, it will appear at the same spot on the page using the same font configurations. You can then simply change what it reads.

2. Time to blend your two data sources together. Navigate to the Resource menu and select Manage blends. Then click + Add A blend. If you are prompted to take a tour of blending, click No thanks.

   

3. In the left hand panel, you see a drop box where you can select the first table of the blend. Set Table 1 to aircraft_data_sample.

4. If the Dimensions list contains any Invalid dimensions, remove them by hovering over them and clicking the X. Now, add the below dimensions to Table 1. You can either click + Add dimension or you can drag the dimensions from the Available Fields list for Table 1, and drop them in the Dimensions list for the blend.

   Dimension
   Hex
   Alt
   timestamp

5. Below the Dimensions list is a list of Metrics. If there are any metrics already in the selected blend Metrics list remove them. Then add the Record Count metric. Verify that what you are seeing for Table 1 resembles the following.

   

6. Add the second table to your blend. Click Join another table + and select aircraft_details_matched from the list.

7. Add the following dimensions to Table 2. Make sure you are working with Table 2.

   Dimension
   icao
   operator
   mdl
   type
   regid
   model_url

8. Add the Record Count metric to Table 2.

   If you look to the right of the UI, you see the full list of combined fields.

9. If you are familiar with the way joins work in SQL, then you likely have worked with the different types of joins (left, right, inner, etc.). Looker Studio blends offer the same standard join operators and conditions (join on) as SQL joins. Now let's configure your join.

   Click Configure join (positioned between the two tables), select Inner for the join operator, and set the Join conditions to Hex on Table 1 and icao on Table 2. Make sure the two fields are next to each other and that there aren't any red missing boxes. The end result should resemble the below.

   

10. When you are satisfied with the join configurations, click Save.

11. In the top-right corner, set the Blend name to Flights Complete.

12. Verify your complete configuration matches the following. Once you are satisfied, click Save, and then X Close the blend configuration page.

    

13. Time to put your blended data to work. Verify that you are on page 2, Blended Data, of your report. Add a table chart to the page and use the Chart properties Data source configuration to set the data source to Flights Complete. Arrange the table so it fills most of the page.

14. Set the dimensions in the table as follows.

    Dimension
    Hex
    operator
    mdl
    type
    regid

15. Set the metric to Record Count (Table 1) and wait while the chart updates. Once it does, take a few moments to examine the table then use it to answer the below questions:

    Questions

    • Which aircraft has the most data points (records) in your table chart?
    • What is the registration regid of the aircraft?
    • What happens to the table when you add the Alt dimension?
    • Why did this happen?

Bonus

• In a new browser tab, paste the following link into the address bar:

  https://www.flightradar24.com/

• Add the registration of one of the aircraft into the search box. If the aircraft isn't found, try a different regid.

• Where is the aircraft now?

• In the information box on the right, confirm that your Hex value matches the ICAO 24-bit Address shown.

Task 7. Custom parameters

The flight operations team has asked for a method to look up a single aircraft and to visualize its operations. In this task, you blend the two datasets together into a single joined data source which you display as both a table and as a map. You add an input field to allow the report viewer to enter the ID number for a particular plane.

1. Select page 3 of your report. Add the following title to the canvas Flight Operations using another text control.

2. To implement this request, add another data source, this one built on a custom query. In the menu bar, click Add data and select BigQuery.

3. Click Custom Query and select the Billing Project . Do not select Qwiklabs Resources.

4. You should see the right part of the interface now displays a query input window. Scroll the query input window and at the bottom, click + Add a parameter.

5. Set the parameter name to Aircraft_ID. Notice how a parameter id is created from the name. The complete dialog box should be as follows.

   

6. Click Ok. Investigate the completed parameter and note its SQL identifier.

7. Paste the following query into the Enter Custom Query box. Note the use of the above created parameter.

   SELECT * FROM `looker_studio_pwr.aircraft_data_sample` as T1 INNER JOIN `looker_studio_pwr.aircraft_details_matched` as T2 ON T1.Hex = T2.icao Where T1.Hex = @aircraft_id

   

8. Click Add to add it to the report.

9. Back on page 3, click Add a chart and select Table. Position your table towards the top of the canvas, but leave enough room for a text box we'll add in a few steps. Also, this ends up only displaying a single row, so it doesn't need to be tall.

10. In the Properties panel, ensure the data source is set to BiqQuery Custom SQL.

11. Set the dimensions in the table to:

    Dimension
    icao
    operator
    mdl
    type
    regid

12. Remove the Record Count metric.

13. Note your chart will read No Data. At this point, this is expected behavior, because you haven't provided a value for the empty query property.

    

14. To add a control to allow the user to set the aircraft ID, click Add a control and select Input Box. Add it to the top-right corner of the page, above the table.

15. In the Control properties panel, change the Control field to Aircraft_ID (scroll, it's at the bottom).

16. To see if the control works, in the report's Aircraft id input box, enter 402863 and press ENTER.

17. The query should run and now in the table you should see the record for a Private Piper PA28R-201 aircraft. Note, if you make the height of the table too short, then you won't actually be able to see the record.

18. Now let's map the aircraft's location data. Click Add a Chart and select Google Maps - Filled Map.

    

19. Position the chart to fit under your table and expand it to fill the rest of the page. Don't worry about the error for now.

20. In the map's properties, clean out the current misconfigurations by removing the Invalid dimension from the Fields list, and by removing Record Count from the Color metric. Now let's set the chart up the way we need. Under Geospatial field select pt, this controls the location of the dots tracking the plane paths. Then set Alt as the Color dimension (not the Color metric), to color code the dots to match their altitude.

21. Your page should look similar to the following:

Click Check my progress to verify the objective. Apply Custom parameters

Task 8. Filter your data

Your flight operations team is interested in filtering the operations dashboard by altitude. In this task, you add a slider to allow the operations team to filter the map view to only include flights in a particular altitude range.

1. Still on page 3 of your report, change the Aircraft_ID to 403321 and press ENTER. You might get an error saying Google Maps Cannot display. If this happens, refresh your browser.

2. If you get a warning about there being a max of 1500 data points, click Ok, got it.. Note: This might not appear the first time you change the report.

   

3. Add a slider control which should work nicely in filtering by an altitude range. Click Add a control >> Slider and set the Control field to Alt.

4. Let's do a quick test. Adjust the slider to have a maximum value of 2,000. Now you can see the aircraft movements around Heathrow Airport, but not the higher altitudes farther away from the airport.

5. That's odd. The minimum value is -100. Did the plane sink into the ground? Actually, this is a result of the altitude being measured by air pressure, rather than by something like GPS. In real life, weather factors would be used to adjust this value to a more accurate altitude approximation. Since negative values might confuse your report viewers, let's create a filter on the data source to exclude these values.

   Click Resource >> Manage filters >> + Add a filter.

6. Set the filter to Include Alt, the condition to Greater than (>), and set the value to 0. This excludes the negative values.

7. Name the filter Zero Altitude and ensure it's applied to the BigQuery Custom SQL data source. Lastly, Save the filter.

   

8. Select the Altitude slider and in the Properties panel, locate Filter. Click Add a Filter. From the filter picker, click Zero Altitude.

9. To test the new filtering in place, in the Aircraft_ID input box, enter 403321 and press ENTER. Observe that the plane's altitude slider now starts at 25.

10. To help make the use of the slider clear to the report user, give it a nice title. The title of a control like this is tied to the name you assign the field. The name of the field in the data source (column name) doesn't have to match the name you give the field in the Looker Studio.

    Using the slider control's properties panel, locate the Control field and click 123 next to Alt. Set the title for the slider to Altitude in feet.

    

Congratulations!

You have completed the lab to analyze data in Looker Studio. In this journey, you practised setting up data connections, creating and editing visualization, creating blends, and adding interactive controls in your report.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.