ChaiyoGCP AI/ML Skill Badges Build LookML Objects in Looker #2 | by Aueaphum Aueawatthanaphisut | Jun, 2024

Looker is a contemporary information platform in Google Cloud that permits you to analyze and visualize your information interactively. You should use Looker to do in-depth information evaluation, combine insights throughout completely different information sources, construct actionable data-driven workflows, and create customized information functions.

In Looker, dimensions are distinctive attributes of the info that make it easier to to explain information. For instance, the town and elevation of an airport could also be completely different dimensions inside an airport dataset. Measures are aggregations of a number of dimensions (or distinctive attributes of the info) reminiscent of a depend or common. Measures allow you to calculate your Key Efficiency Indicators (KPIs) and assist what you are promoting customers analyze information utilizing completely different aggregated attributes.

On this lab, you learn to create several types of dimensions and measures in LookML as a Looker developer. You additionally learn to modify fashions of Explores, that are information views that function the inspiration for self-service exploration by the enterprise customers in Looker.

For this lab, a mission referred to as qwiklabs-ecommerce has been created for you in LookML. This mission relies on a mock e-commerce dataset that can allow you to create enterprise KPIs utilizing dimensions and measures created utilizing LookML. You may be taught extra about LookML modeling within the Looker documentation.

On this lab, you learn to:

• Modify an current LookML mission (qwiklabs-ecommerce) printed by a Looker admin.
• Create several types of dimensions and measures in LookML to deal with what you are promoting customers’ questions.
• Check your LookML modifications in growth mode.
• Use the Discover interface to view the scale and measures that you’ve created within the modified LookML mission.

Earlier than you click on the Begin Lab button

Learn these directions. Labs are timed and you can’t pause them. The timer, which begins whenever you click on Begin Lab, exhibits how lengthy Google Cloud sources will probably be made obtainable to you.

This hands-on lab enables you to do the lab actions your self in an actual cloud surroundings, not in a simulation or demo surroundings. It does so by supplying you with new, momentary credentials that you simply use to check in and entry Google Cloud in the course of the lab.

To finish this lab, you want:

• Entry to a normal web browser (Chrome browser beneficial).

Observe: Use an Incognito or personal browser window to run this lab. This prevents any conflicts between your private account and the Scholar account, which can trigger further costs incurred to your private account.

• Time to finish the lab — -remember, when you begin, you can’t pause a lab.

Observe: If you have already got your individual private Google Cloud account or mission, don’t use it for this lab to keep away from further costs to your account.

1. When prepared, click on

A brand new panel will seem with the momentary credentials that you should use for this lab.

If you might want to pay for the lab, a pop-up will open so that you can choose your fee methodology.

2. Observe your lab credentials within the left pane. You’ll use them to check in to the Looker occasion for this lab.

Observe: In case you use different credentials, you’ll get errors or incur costs.

3. Click on Open Looker.

4. Enter the offered Username and Password within the E mail and Password fields.

Vital: You need to use the credentials from the Connection Particulars panel on this web page. Don’t use your Google Cloud Abilities Increase credentials. When you have your individual Looker account, don’t use it for this lab.

5. Click on Log In.

After a profitable login, you will notice the Looker occasion for this lab.

In Looker, a dimension is a group-able area and can be utilized to filter question outcomes. It may be:

• An attribute, which has a direct affiliation to a column in an underlying desk
• A reality or numerical worth
• A derived worth, computed primarily based on the values of different fields in a single row

For instance, dimensions for a Merchandise view would possibly embrace product title, product mannequin, product colour, product worth, product created date, and product end-of-life date.

Dimensions allow you to create buckets of information factors to research your KPIs utilizing completely different attributes. You may create several types of dimensions like time, numeric, yesno and string to slice and cube your information.

On this part, you’ll create a brand new dimension named age_tier primarily based off of the age dimension. This dimension will record ranges of ages. You’ll do that by including a dimension that teams particular person ages into the next age group tiers: 18, 25, 35, 45, 55, 65, 75, 90.

1. First, on the underside left of the Looker Consumer Interface, click on the toggle button to enter Improvement mode.

2. Click on the Develop tab after which choose the qwiklabs-ecommerce LookML mission.

3. As soon as you’re within the qwiklabs-ecommerce mission, click on the arrow subsequent to views to see a listing of view names.

4. Click on customers.view.

5. In customers.view, find the dimension for age. Your file ought to resemble the next:

Dimension fields, reminiscent of age correspond to your underlying database desk, or computed values primarily based on different dimensions.

The editor offers you ideas as you kind, however in the event you get caught or have to see a listing of various parameters and their attributes, you’ll be able to all the time seek advice from the Fast Assist menu on the right-hand facet of the IDE.

6. On a brand new line below the dimension for age, begin by defining a brand new dimension for age_tier utilizing the next code:

dimension: age_tier {
}

7. Subsequent, you’ll add the dimension kind. This dimension kind is tier, so you will add that right here:

dimension: age_tier {
kind: tier
}

8. Subsequent, you’ll add the particular tiers for the dimension. On this case, you’ll group the tiers first by 18 and youthful, then by increments of 10 years:

dimension: age_tier {
kind: tier
tiers: [18, 25, 35, 45, 55, 65, 75, 90]
}

9. Subsequent, outline the type parameter. This parameter is restricted to the tier kind dimension and modifications the way in which tiers seem within the UI. On this case, you need the type to be integer:

dimension: age_tier {
kind: tier
tiers: [18, 25, 35, 45, 55, 65, 75, 90]
type: integer
}

10. Lastly, you’ll add the SQL parameter. The SQL parameter tells Looker find out how to write the SQL for queries customers run. For this dimension, you’re telling the SQL parameter to tug from the pre-existing age area:

dimension: age_tier {
kind: tier
tiers: [18, 25, 35, 45, 55, 65, 75, 90]
type: integer
sql: ${age} ;;
}

Your file ought to now resemble the next:

view: customers {
sql_table_name: `cloud-training-demos.looker_ecomm.customers`
;;
drill_fields: [id]
dimension: id {
primary_key: sure
kind: quantity
sql: ${TABLE}.id ;;
}
dimension: age {
kind: quantity
sql: ${TABLE}.age ;;
}
dimension: age_tier {
kind: tier
tiers: [18, 25, 35, 45, 55, 65, 75, 90]
type: integer
sql: ${age} ;;
}
dimension: metropolis {
kind: string
sql: ${TABLE}.metropolis ;;
}
dimension: nation {
kind: string
map_layer_name: international locations
sql: ${TABLE}.nation ;;
}
dimension_group: created {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.created_at ;;
}
dimension: electronic mail {
kind: string
sql: ${TABLE}.electronic mail ;;
}
dimension: first_name {
kind: string
sql: ${TABLE}.first_name ;;
}
dimension: gender {
kind: string
sql: ${TABLE}.gender ;;
}
dimension: last_name {
kind: string
sql: ${TABLE}.last_name ;;
}
dimension: latitude {
kind: quantity
sql: ${TABLE}.latitude ;;
}
dimension: longitude {
kind: quantity
sql: ${TABLE}.longitude ;;
}
dimension: state {
kind: string
sql: ${TABLE}.state ;;
map_layer_name: us_states
}
dimension: traffic_source {
kind: string
sql: ${TABLE}.traffic_source ;;
}
dimension: zip {
kind: zipcode
sql: ${TABLE}.zip ;;
}
measure: depend {
kind: depend
drill_fields: [id, last_name, first_name, events.count, order_items.count]
}
}

Now that you simply completed including a brand new dimension, you’ll be able to check to verify it’s working correctly.

11. Click on Save Modifications after which click on the Validate LookML button on the highest proper of the IDE to run a LookML code validation.

12. You may shortly go to the Discover by clicking the caret subsequent to the file title on the prime of the IDE after which deciding on Discover Order Gadgets.

This file menu will solely present views which have an Discover outlined within the LookML.

13. Subsequent, navigate to your new dimension below Customers > Dimensions > Age Tier.

14. Add the Age and the Age Tier dimensions and click on Run. You may see that every age falls into the right tier:

15. Now take away the Age dimension and add the Rely measure and hit Run once more. The outcomes are exhibiting what you need. Looker has counted the completely different ages and grouped them into the suitable tiers. Success!

Create a brand new dimension for electronic mail supply

On this part, you’ll create a brand new dimension named is_email_source primarily based off of the traffic_source dimension. This dimension will decide whether or not the site visitors supply that introduced in a given person was through electronic mail.

1. Navigate again to the qwiklabs-ecommerce mission and open customers.view file.
2. Find the dimension for traffic_source. Your file ought to resemble the next:

3. On a brand new line below the dimension for site visitors supply, begin by defining a brand new dimension for is_email_source utilizing the next code:

dimension: is_email_source {
}

4. Subsequent, add the kind parameter. Since it is a boolean categorization, you’ll use the yesno kind:

dimension: is_email_source {
kind: yesno
}

5. Lastly, add the SQL parameter. For this dimension, you’re telling the SQL parameter to tug from the pre-existing traffic_source area the place the worth equals “E mail”.

6. You’ll want to use double citation marks ("") when defining “E mail” to make sure correct syntax:

dimension: is_email_source {
kind: yesno
sql: ${traffic_source} = "E mail" ;;
}

Your file ought to now resemble the next:

Now that you simply completed including a brand new dimension, you’ll be able to check to verify it’s working correctly.

7. Click on Save Modifications after which click on the Validate LookML button on the highest proper of the IDE to run a LookML code validation.

8. Click on the caret subsequent to the file title on the prime of the IDE after which choose Discover Order Gadgets:

9. Subsequent, navigate to your new dimension below Customers > Dimensions > Is E mail Supply (Sure / No).

10. Add the Is E mail Supply dimension and the Rely measure and click on Run. The outcomes are exhibiting the quantity of customers that had been introduced in through electronic mail or not. Success!

Create a brand new dimension for delivery days

On this part, you’ll create a brand new dimension named delivery days that calculates the variety of days between the order ship date and the order created date throughout the order_items view.

1. Navigate again to the qwiklabs-ecommerce mission and open the order_items.view file.
2. Find the dimension group for shipped. Your file ought to resemble the next:

3. On a brand new line below the dimension group for shipped, outline a brand new dimension for shipping_days utilizing the next code:

dimension: shipping_days {
}

4. Subsequent, add the kind parameter. For this dimension, you’ll be utilizing the quantity kind:

dimension: shipping_days {
kind: quantity
}

5. Lastly, add the SQL parameter. For this dimension, you’re telling the SQL parameter to run a DATE_DIFF function on the shipped_date and created_date dimensions. DAY is used right here because the offered interval you wish to be calculating:

dimension: shipping_days {
kind: quantity
sql: DATE_DIFF(${shipped_date}, ${created_date}, DAY);;
}

Your file ought to now resemble the next:

6. Now that you simply completed including a brand new dimension, you’ll be able to check to verify it’s working correctly. Click on Save Modifications after which click on the Validate LookML button on the highest proper of the IDE to run a LookML code validation.

7. Click on the caret subsequent to the file title on the prime of the IDE after which choose Discover Order Gadgets.

8. Subsequent, navigate to your new dimension below Order Gadgets > Dimensions > Delivery Days.

9. Add the Delivery Days dimension and the Order Rely measure and click on Run. The outcomes are exhibiting the depend of orders with their respective delivery days. Success!

10. Navigate again to the order_items.view file.

Commit modifications and deploy to manufacturing

1. Click on Validate LookML after which click on Commit Modifications & Push.
2. Add a commit message and click on Commit.
3. Lastly, click on Deploy to Manufacturing.

Click on Examine my progress to confirm the target.

A measure is a area that makes use of a SQL mixture operate, reminiscent of COUNT, SUM, AVG, MIN, or MAX. Any area computed primarily based on the values of different measure values can also be a measure. Measures can be utilized to filter grouped values. For instance, measures for a Gross sales view would possibly embrace whole gadgets bought (a depend), whole sale worth (a sum), and common sale worth (a mean).

The conduct and anticipated values for a area rely on its declared kind, reminiscent of string, quantity, or time. For measures, sorts embrace mixture features, reminiscent of sum and percent_of_previous. For particulars, seek advice from dimension types and measure types.

Measure fields are used to mixture values for a number of rows. On this part, you’ll create a brand new measure named count_distinct_orders that calculates the distinct variety of orders throughout the order_items view.

1. Navigate again to the qwiklabs-ecommerce mission and open order_items.view file.
2. In order_items.view, find the measure for order_item_count.
3. On a brand new line below the measure for order_item_count, begin by defining a brand new measure for count_distinct_orders utilizing the next code:

measure: count_distinct_orders {
}

Observe: Be sure to interchange the default measure title (order_count) with count_distinct_orders.

4. Subsequent, add the kind parameter. For this measure, you’ll be utilizing the count_distinct kind. The kind count_distinct calculates the variety of distinct values in a given area. It makes use of SQL’s COUNT DISTINCT operate:

measure: count_distinct_orders {
kind: count_distinct
}

5. Lastly, add the SQL parameter. For this measure, you’re telling the SQL parameter to tug from the pre-existing order_id area:

measure: count_distinct_orders {
kind: count_distinct
sql: ${order_id} ;;
}

Your file ought to now resemble the next:

view: order_items {
sql_table_name: `cloud-training-demos.looker_ecomm.order_items`
;;
drill_fields: [order_item_id]
dimension: order_item_id {
primary_key: sure
kind: quantity
sql: ${TABLE}.id ;;
}
dimension_group: created {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.created_at ;;
}
dimension_group: delivered {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.delivered_at ;;
}
dimension: inventory_item_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.inventory_item_id ;;
}
dimension: order_id {
kind: quantity
sql: ${TABLE}.order_id ;;
}
dimension_group: returned {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.returned_at ;;
}
dimension: sale_price {
kind: quantity
sql: ${TABLE}.sale_price ;;
}
dimension_group: shipped {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.shipped_at ;;
}
dimension: shipping_days {
kind: quantity
sql: DATE_DIFF(${shipped_date}, ${created_date}, DAY);;
}
dimension: standing {
kind: string
sql: ${TABLE}.standing ;;
}
dimension: user_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.user_id ;;
}
measure: average_sale_price {
kind: common
sql: ${sale_price} ;;
drill_fields: [detail*]
value_format_name: usd_0
}
measure: order_item_count {
kind: depend
drill_fields: [detail*]
}
measure: count_distinct_orders {
kind: count_distinct
sql: ${order_id} ;;
}
measure: order_count {
kind: count_distinct
sql: ${order_id} ;;
}
measure: total_revenue {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd
}
measure: total_revenue_from_completed_orders {
kind: sum
sql: ${sale_price} ;;
filters: [status: "Complete"]
value_format_name: usd
}
# ----- Units of fields for drilling ------
set: element {
fields: [
order_item_id,
users.last_name,
users.id,
users.first_name,
inventory_items.id,
inventory_items.product_name
]
}
}

Now that you simply completed including a brand new measure, you’ll be able to check to verify it’s working correctly.

6. Click on Save Modifications after which click on the Validate LookML button on the highest proper of the IDE to run a LookML code validation.

7. Click on the caret subsequent to the file title on the prime of the IDE after which choose Discover Order Gadgets.

8. Beneath Order Gadgets > Measures, click on Rely Distinct Orders.

9. Click on Run to see the values within the new measure. You may affirm that your new measure is working correctly.

On this part, you’ll create a brand new measure named total_sales that calculates whole gross sales utilizing the sale_price dimension.

1. Navigate again to the qwiklabs-ecommerce mission and open order_items.view file.
2. In order_items.view, find the measure for order_item_count.
3. On a brand new line below the measure for order_item_count, begin by defining a brand new measure for total_sales utilizing the next code:

measure: total_sales {
}

4. Add the kind parameter. Right here you’ll be utilizing sum:

measure: total_sales {
kind: sum
}

5. Add the SQL parameter. For this measure, you’re telling the SQL parameter to tug from the pre-existing sale_price area:

measure: total_sales {
kind: sum
sql: ${sale_price} ;;
}

6. Lastly, you’ll add the value_format_name. The value_format_name parameter allows you to format information values utilizing codecs constructed into Looker or your individual customized, reusable codecs. Right here, since you’re calculating sale worth you’ll use US {dollars} (usd_0):

measure: total_sales {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd_0
}

Your file ought to now resemble the next:

view: order_items {
sql_table_name: `cloud-training-demos.looker_ecomm.order_items`
;;
drill_fields: [order_item_id]
dimension: order_item_id {
primary_key: sure
kind: quantity
sql: ${TABLE}.id ;;
}
dimension_group: created {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.created_at ;;
}
dimension_group: delivered {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.delivered_at ;;
}
dimension: inventory_item_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.inventory_item_id ;;
}
dimension: order_id {
kind: quantity
sql: ${TABLE}.order_id ;;
}
dimension_group: returned {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.returned_at ;;
}
dimension: sale_price {
kind: quantity
sql: ${TABLE}.sale_price ;;
}
dimension_group: shipped {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.shipped_at ;;
}
dimension: shipping_days {
kind: quantity
sql: DATE_DIFF(${shipped_date}, ${created_date}, DAY);;
}
dimension: standing {
kind: string
sql: ${TABLE}.standing ;;
}
dimension: user_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.user_id ;;
}
measure: average_sale_price {
kind: common
sql: ${sale_price} ;;
drill_fields: [detail*]
value_format_name: usd_0
}
measure: order_item_count {
kind: depend
drill_fields: [detail*]
}
measure: total_sales {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd_0
}
measure: count_distinct_orders {
kind: count_distinct
sql: ${order_id} ;;
}
measure: order_count {
kind: count_distinct
sql: ${order_id} ;;
}
measure: total_revenue {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd
}
measure: total_revenue_from_completed_orders {
kind: sum
sql: ${sale_price} ;;
filters: [status: "Complete"]
value_format_name: usd
}
# ----- Units of fields for drilling ------
set: element {
fields: [
order_item_id,
users.last_name,
users.id,
users.first_name,
inventory_items.id,
inventory_items.product_name
]
}
}

Now that you simply completed including a brand new measure, you’ll be able to check to verify it’s working correctly.

7. Click on Save Modifications after which click on the Validate LookML button on the highest proper of the IDE to run a LookML code validation.

8. Click on the caret subsequent to the file title on the prime of the IDE after which choose Discover Order Gadgets.

9. Beneath Order Gadgets > Measures, click on Complete Gross sales.

10. Click on Run to see the values within the new measure.

11. Navigate again to the order_items.view file.

Commit modifications and deploy to manufacturing

1. Click on Validate LookML after which click on Commit Modifications & Push.
2. Add a commit message and click on Commit.
3. Lastly, click on Deploy to Manufacturing.

Click on Examine my progress to confirm the target.

Superior measures allow you to create further customized metrics from dimensions which might be outlined externally to the present view file. You may as well create particular forms of filtered measures by offering particular filtering situations.

On this part, you’ll create a brand new superior measure named total_sales_email_users that calculates whole gross sales for solely these customers who got here to the web site through the e-mail site visitors supply.

1. Navigate again to the qwiklabs-ecommerce mission and open order_items.view file.
2. In order_items.view, find the measure for order_item_count.
3. On a brand new line below the measure for order_item_count, begin by defining a brand new measure for total_sales_email_users utilizing the next code:

measure: total_sales_email_users {
}

4. Subsequent, add the kind. For this since we’re calculating whole gross sales, we are going to use sum:

measure: total_sales_email_users {
kind: sum
}

5. Add the SQL parameter. For this measure, you’re telling the SQL parameter to tug from the pre-existing sale_price area:

measure: total_sales_email_users {
kind: sum
sql: ${sale_price} ;;
}

Lastly, you’ll add the filters parameter. To use a filter on to a measure, as a substitute of filtering the whole question, we will add a filters parameter to the LookML definition of a measure. This may apply the filter, within the type of a CASE WHEN assertion, to the measure within the generated SQL, versus making use of a world WHERE clause to the whole question.

As such, as a substitute of eradicating rows from a question after it’s aggregated, a filtered measure will solely mixture these rows that meet the required situations. This method permits us to look at subsets of a inhabitants vs. different subsets or the entire.

6. Add the next filter parameter. Right here you’re utilizing the is_email_source dimension you created earlier throughout the customers.view file:

measure: total_sales_email_users {
kind: sum
sql: ${sale_price} ;;
filters: [users.is_email_source: "Yes"]
}

Observe: You possibly can additionally reference the traffic_source dimension as a substitute, utilizing the next code.

measure: total_sales_email_users {
kind: sum
sql: ${sale_price} ;;
filters: [users.traffic_source: "Email"]
}

Your file ought to now resemble the next:

view: order_items {
sql_table_name: `cloud-training-demos.looker_ecomm.order_items`
;;
drill_fields: [order_item_id]
dimension: order_item_id {
primary_key: sure
kind: quantity
sql: ${TABLE}.id ;;
}
dimension_group: created {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.created_at ;;
}
dimension_group: delivered {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.delivered_at ;;
}
dimension: inventory_item_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.inventory_item_id ;;
}
dimension: order_id {
kind: quantity
sql: ${TABLE}.order_id ;;
}
dimension_group: returned {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.returned_at ;;
}
dimension: sale_price {
kind: quantity
sql: ${TABLE}.sale_price ;;
}
dimension_group: shipped {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.shipped_at ;;
}
dimension: shipping_days {
kind: quantity
sql: DATE_DIFF(${shipped_date}, ${created_date}, DAY);;
}
dimension: standing {
kind: string
sql: ${TABLE}.standing ;;
}
dimension: user_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.user_id ;;
}
measure: average_sale_price {
kind: common
sql: ${sale_price} ;;
drill_fields: [detail*]
value_format_name: usd_0
}
measure: order_item_count {
kind: depend
drill_fields: [detail*]
}
measure: total_sales_email_users {
kind: sum
sql: ${sale_price} ;;
filters: [users.traffic_source: "Email"]
}
measure: total_sales {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd_0
}
measure: count_distinct_orders {
kind: count_distinct
sql: ${order_id} ;;
}
measure: order_count {
kind: count_distinct
sql: ${order_id} ;;
}
measure: total_revenue {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd
}
measure: total_revenue_from_completed_orders {
kind: sum
sql: ${sale_price} ;;
filters: [status: "Complete"]
value_format_name: usd
}
# ----- Units of fields for drilling ------
set: element {
fields: [
order_item_id,
users.last_name,
users.id,
users.first_name,
inventory_items.id,
inventory_items.product_name
]
}
}

Now that you simply completed including a brand new measure, you’ll be able to check to verify it’s working correctly.

7. Click on Save Modifications after which click on the Validate LookML button on the highest proper of the IDE to run a LookML code validation.

8. Click on the caret subsequent to the file title on the prime of the IDE after which choose Discover Order Gadgets.

9. Beneath Order Gadgets > Measures, click on Complete Gross sales E mail Customers.

10. Click on Run to see the values within the new measure.

On this part, you’ll create a brand new superior measure named percentage_sales_email_source that calculates the share of gross sales which might be attributed to customers coming from the e-mail site visitors supply.

1. Navigate again to the qwiklabs-ecommerce mission and open order_items.view file.
2. In order_items.view, find the measure for order_item_count.
3. On a brand new line below the measure for order_item_count, begin by defining a brand new measure for percentage_sales_email_source utilizing the next code:

measure: percentage_sales_email_source {
}

4. Subsequent, add the kind. For this since we’re calculating whole gross sales, we are going to use quantity:

measure: percentage_sales_email_source {
kind: quantity
}

5. Subsequent add the value_format_name parameter. Since you’re calculating a proportion, you need to use percent_2:

measure: percentage_sales_email_source {
kind: quantity
value_format_name: percent_2
}

6. Add the SQL parameter. For this measure, you’re telling the SQL parameter to tug from the pre-existing total_sales_email_users area and dividing by the total_sales:

Observe: When creating proportion measures, it’s typically helpful to be sure to will not be dividing by zero within the proportion calculation. This may be completed by means of the NULLIF SQL operate.

measure: percentage_sales_email_source {
kind: quantity
value_format_name: percent_2
sql: 1.0*${total_sales_email_users}
/ NULLIF(${total_sales}, 0) ;;
}

view: order_items {
sql_table_name: `cloud-training-demos.looker_ecomm.order_items`
;;
drill_fields: [order_item_id]
dimension: order_item_id {
primary_key: sure
kind: quantity
sql: ${TABLE}.id ;;
}
dimension_group: created {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.created_at ;;
}
dimension_group: delivered {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.delivered_at ;;
}
dimension: inventory_item_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.inventory_item_id ;;
}
dimension: order_id {
kind: quantity
sql: ${TABLE}.order_id ;;
}
dimension_group: returned {
kind: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.returned_at ;;
}
dimension: sale_price {
kind: quantity
sql: ${TABLE}.sale_price ;;
}
dimension_group: shipped {
kind: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.shipped_at ;;
}
dimension: shipping_days {
kind: quantity
sql: DATE_DIFF(${shipped_date}, ${created_date}, DAY);;
}
dimension: standing {
kind: string
sql: ${TABLE}.standing ;;
}
dimension: user_id {
kind: quantity
# hidden: sure
sql: ${TABLE}.user_id ;;
}
measure: average_sale_price {
kind: common
sql: ${sale_price} ;;
drill_fields: [detail*]
value_format_name: usd_0
}
measure: order_item_count {
kind: depend
drill_fields: [detail*]
}
measure: percentage_sales_email_source {
kind: quantity
value_format_name: percent_2
sql: 1.0*${total_sales_email_users}
/ NULLIF(${total_sales}, 0) ;;
}
measure: total_sales_email_users {
kind: sum
sql: ${sale_price} ;;
filters: [users.traffic_source: "Email"]
}
measure: total_sales {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd_0
}
measure: count_distinct_orders {
kind: count_distinct
sql: ${order_id} ;;
}
measure: order_count {
kind: count_distinct
sql: ${order_id} ;;
}
measure: total_revenue {
kind: sum
sql: ${sale_price} ;;
value_format_name: usd
}
measure: total_revenue_from_completed_orders {
kind: sum
sql: ${sale_price} ;;
filters: [status: "Complete"]
value_format_name: usd
}
# ----- Units of fields for drilling ------
set: element {
fields: [
order_item_id,
users.last_name,
users.id,
users.first_name,
inventory_items.id,
inventory_items.product_name
]
}
}

Your file ought to now resemble the next:

Now that you simply completed including a brand new measure, you’ll be able to check to verify it’s working correctly.

7. Click on Save Modifications after which click on the Validate LookML button on the highest proper of the IDE to run a LookML code validation.

8. Click on the caret subsequent to the file title on the prime of the IDE after which choose Discover Order Gadgets.

9. Beneath Order Gadgets > Measures, click on Share Gross sales E mail Supply.

10. Click on Run to see the values within the new measure. Success!

11. Navigate again to the order_items.view file.

Commit modifications and deploy to manufacturing

1. Click on Validate LookML after which click on Commit Modifications & Push.
2. Add a commit message and click on Commit.
3. Lastly, click on Deploy to Manufacturing.

Click on Examine my progress to confirm the target.

On this lab, you realized find out how to create several types of dimensions and measures in LookML as a Looker developer. You additionally realized find out how to modify fashions of Explores, that are information views that function the inspiration for self-service exploration by the enterprise customers in Looker, and create superior measures with filters.

…helps you take advantage of Google Cloud applied sciences. Our classes embrace technical abilities and finest practices that will help you rise up to hurry shortly and proceed your studying journey. We provide basic to superior stage coaching, with on-demand, reside, and digital choices to fit your busy schedule. Certifications make it easier to validate and show your talent and experience in Google Cloud applied sciences.

Handbook Final Up to date April 22, 2024

Lab Final Examined Could 18, 2023

Copyright 2024 Google LLC All rights reserved. Google and the Google emblem are logos of Google LLC. All different firm and product names could also be logos of the respective firms with which they’re related.

navigate_beforePreviousNextnavigate_next

Thanks a lot on your consideration

Aueaphum Aueawatthanaphisut

aueawatth.aue@gmail.com