Overview
Looker is a modern data platform in Google Cloud that lets you analyze and visualize your data interactively. You can use Looker to do in-depth data analysis, integrate insights across different data sources, build actionable data-driven workflows, and create custom data applications.
So far you have created dimensions and measures, explored and joined views, and learned how to use derived tables - it's time to learn about filters. In this lab you will learn how to take your Explores to the next level by filtering them.
Task 2. Add an always_filter
training_ecommerce.model
connection: "bigquery_public_data_looker"
# include all the views
include: "/views/*.view"
include: "/z_tests/*.lkml"
include: "/**/*.dashboard"
datagroup: training_ecommerce_default_datagroup {
# sql_trigger: SELECT MAX(id) FROM etl_log;;
max_cache_age: "1 hour"
}
persist_with: training_ecommerce_default_datagroup
label: "E-Commerce Training"
explore: order_items {
always_filter: {
filters: [order_items.status: "Complete", users.country: "USA"]
}
join: users {
type: left_outer
sql_on: ${order_items.user_id} = ${users.id} ;;
relationship: many_to_one
}
join: inventory_items {
type: left_outer
sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;
relationship: many_to_one
}
join: products {
type: left_outer
sql_on: ${inventory_items.product_id} = ${products.id} ;;
relationship: many_to_one
}
join: distribution_centers {
type: left_outer
sql_on: ${products.distribution_center_id} = ${distribution_centers.id} ;;
relationship: many_to_one
}
}
explore: events {
join: event_session_facts {
type: left_outer
sql_on: ${events.session_id} = ${event_session_facts.session_id} ;;
relationship: many_to_one
}
join: event_session_funnel {
type: left_outer
sql_on: ${events.session_id} = ${event_session_funnel.session_id} ;;
relationship: many_to_one
}
join: users {
type: left_outer
sql_on: ${events.user_id} = ${users.id} ;;
relationship: many_to_one
}
}
Task 3. Add a sql_always_where filter
training_ecommerce.model
connection: "bigquery_public_data_looker"
# include all the views
include: "/views/*.view"
include: "/z_tests/*.lkml"
include: "/**/*.dashboard"
datagroup: training_ecommerce_default_datagroup {
# sql_trigger: SELECT MAX(id) FROM etl_log;;
max_cache_age: "1 hour"
}
persist_with: training_ecommerce_default_datagroup
label: "E-Commerce Training"
explore: order_items {
sql_always_where: ${created_date} >= '2021-01-01' ;;
join: users {
type: left_outer
sql_on: ${order_items.user_id} = ${users.id} ;;
relationship: many_to_one
}
join: inventory_items {
type: left_outer
sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;
relationship: many_to_one
}
join: products {
type: left_outer
sql_on: ${inventory_items.product_id} = ${products.id} ;;
relationship: many_to_one
}
join: distribution_centers {
type: left_outer
sql_on: ${products.distribution_center_id} = ${distribution_centers.id} ;;
relationship: many_to_one
}
}
explore: events {
join: event_session_facts {
type: left_outer
sql_on: ${events.session_id} = ${event_session_facts.session_id} ;;
relationship: many_to_one
}
join: event_session_funnel {
type: left_outer
sql_on: ${events.session_id} = ${event_session_funnel.session_id} ;;
relationship: many_to_one
}
join: users {
type: left_outer
sql_on: ${events.user_id} = ${users.id} ;;
relationship: many_to_one
}
}
Task 4. Add a sql_always_having filter
training_ecommerce.model
connection: "bigquery_public_data_looker"
# include all the views
include: "/views/*.view"
include: "/z_tests/*.lkml"
include: "/**/*.dashboard"
datagroup: training_ecommerce_default_datagroup {
# sql_trigger: SELECT MAX(id) FROM etl_log;;
max_cache_age: "1 hour"
}
persist_with: training_ecommerce_default_datagroup
label: "E-Commerce Training"
explore: order_items {
sql_always_having: ${order_item_count} = 1 ;;
join: users {
type: left_outer
sql_on: ${order_items.user_id} = ${users.id} ;;
relationship: many_to_one
}
join: inventory_items {
type: left_outer
sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;
relationship: many_to_one
}
join: products {
type: left_outer
sql_on: ${inventory_items.product_id} = ${products.id} ;;
relationship: many_to_one
}
join: distribution_centers {
type: left_outer
sql_on: ${products.distribution_center_id} = ${distribution_centers.id} ;;
relationship: many_to_one
}
}
explore: events {
join: event_session_facts {
type: left_outer
sql_on: ${events.session_id} = ${event_session_facts.session_id} ;;
relationship: many_to_one
}
join: event_session_funnel {
type: left_outer
sql_on: ${events.session_id} = ${event_session_funnel.session_id} ;;
relationship: many_to_one
}
join: users {
type: left_outer
sql_on: ${events.user_id} = ${users.id} ;;
relationship: many_to_one
}
}
Task 5. Add a conditionality_filter
training_ecommerce.model
connection: "bigquery_public_data_looker"
# include all the views
include: "/views/*.view"
include: "/z_tests/*.lkml"
include: "/**/*.dashboard"
datagroup: training_ecommerce_default_datagroup {
# sql_trigger: SELECT MAX(id) FROM etl_log;;
max_cache_age: "1 hour"
}
persist_with: training_ecommerce_default_datagroup
label: "E-Commerce Training"
explore: order_items {
conditionally_filter: {
filters: [created_date: "3 years"]
unless: [users.id, users.state]
}
join: users {
type: left_outer
sql_on: ${order_items.user_id} = ${users.id} ;;
relationship: many_to_one
}
join: inventory_items {
type: left_outer
sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;
relationship: many_to_one
}
join: products {
type: left_outer
sql_on: ${inventory_items.product_id} = ${products.id} ;;
relationship: many_to_one
}
join: distribution_centers {
type: left_outer
sql_on: ${products.distribution_center_id} = ${distribution_centers.id} ;;
relationship: many_to_one
}
}
explore: events {
join: event_session_facts {
type: left_outer
sql_on: ${events.session_id} = ${event_session_facts.session_id} ;;
relationship: many_to_one
}
join: event_session_funnel {
type: left_outer
sql_on: ${events.session_id} = ${event_session_funnel.session_id} ;;
relationship: many_to_one
}
join: users {
type: left_outer
sql_on: ${events.user_id} = ${users.id} ;;
relationship: many_to_one
}
}