GSP983

Overview

Looker provides the ability for non-developer users to create and utilize ad hoc fields for richer data analysis. This is done by creating custom measures, custom dimensions, table calculations and using custom groupings to narrow down data to match specific conditions.

Using ad hoc fields gives non-developers the ability to create new fields, as opposed to regular fields, which require that you have development permissions and understand LookML allowing them to have more flexibility in finding data they are looking for independently.

Objectives

In this lab, you will learn how to:

• Create custom measures based on an existing dimension
• Create custom groupings
• Adding filters to custom measures
• Use table calculations on numeric fields without writing Looker functions or operators

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.

Task 1. Create a custom measure

In this section, you will create a custom measure based on an existing dimension.

Using the dimension's three-dot Options menu

1. First, on the bottom left of the Looker User Interface, click the toggle button to enter Development mode.

2. Click the Explore tab and then select E-Commerce Training > Order Items.

3. Expand the Inventory Items section.

4. Click More () next to the Cost dimension to review the custom fields you can add.

5. Select the Aggregate > Average custom field to create a measure that calculates the average of an order item’s cost.

The suggested functions vary based on the type of dimension you’ve chosen (such as number, text, and date). If you select count, a count distinct is executed in the generated SQL. Your new field is automatically added to the query.

6. Expand the Custom Fields section to see your new field.

7. As with other fields, you can click a custom field’s name to add or remove it from a query. You can also hover over the field to reveal more options available for that field, including clicking its Filter by field icon to use it as a filter in a query.

Using the Custom Fields section

If you want to define a format or a filter for your custom measure while creating it, start with the + Add button on the Custom Fields section.

1. Expand the Custom Fields section.

2. Click + Add and then select Custom Measure.

3. Set the Field to measure to Inventory Items > Cost.

4. Set the Measure type to Average.

5. Set the Name to Average of Cost.

6. On the Field details tab set the following:

• Format: U.S. Dollars
• Decimals: 2

7. Click Save.

Task 2. Create a custom grouping

The Group custom field type lets you create ad hoc custom groups for dimensions and custom dimensions without needing to use logical functions in Looker expressions or needing to develop CASE WHEN logic in sql parameters or type: case fields.

This can be helpful when you want to assign fixed labels or category names to values that match specific conditions, for example, by grouping specific states or countries into regions or order costs into categories.

1. You should now see two Average of Cost custom fields in the Custom Fields section. Select just the bottom one to add it to the query.

2. Expand the Inventory Items section and select the Product Name dimension to add it to the query.

3. Expand the Users section and select the Filter by field icon for the Country dimension and set the drop down to is equal to and the value to USA.

4. Next, click More () next to State under the Users section and select Group.

5. In the Group By State dialog, set the Add group name to Pacific Northwest.

6. Click the is any value input box and add Oregon, Idaho and Washington to the list independently.

7. Check the Group remaining values checkbox then click Save.

8. Select the newly create State Groups custom field to add it to the Explore and click Run in the top right of the page.

The result will be a view of the average cost of various product names grouped by Pacific Northwest and Other.

Task 3. Adding a filter to a custom measure

Now that you have a view of the average cost of various product names grouped by Pacific Northwest and Other, say you wanted to only view products with an average cost greater than $200 (USD).

To enhance the current view do the following:

1. Under the Custom Fields section select the Filter by field icon for the bottom Average of Cost under Measures.

2. Set the drop down to is greater than and set its value to 200.

3. Re-run the query by clicking on the Run button in the top right of the page to view the results.

You will now see a filtered view with only the average of cost products greater than $200 (USD).

Click Check my progress to verify the objective. Create a custom measure

Task 4. Using table calculations

Say you wanted to understand the count of orders for each item in the view from the last section. To filter the view further you could add the Order Count and use the Percentage of Column quick calculation to get an idea of how popular a product is by the State Groups custom field created earlier in this lab.

1. Under Order Items section, click Order Count to add it to the view.

2. In the data view, click Settings () on the Order Count column.

3. Click Calculations > % of column.

A new table calculation column with the percentage of Order Count should be populated into the Explore.

4. Click Run at the top right hand side of the page. This will re-run the previous query and the result will display the order item count and percentage of the table that each row represents in the resulting view. This will give an analyst insights into what product is popular by the State Grouping group created earlier.

Click Check my progress to verify the objective. Use the table calculations

Congratulations!

In this lab you learned how to define and read core LookML terms and concepts. You then learned how to organize and understand the main LookML structures and hierarchy, created a view, added dimensions and measures to it, and joined the view to an existing Explore.

Next steps / learn more

• LookML quick reference
• LookML terms and concepts
• Looker Community
• Additional LookML basics

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated July 25, 2022

Lab Last Tested April 20, 2022

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