Model Management Flashcards
What are the key types of files in a looker project?
Model files, view files and other files
Other files include: manifest file, read_me files, tests, dashboards, spec files (data_groups)
What are the 3 main components of a view?
Data source, dimensions, and measures.
Additional components include, source, filters, templated filters, parameters, and field sets
What are the 3 main components of a model?
A model file specifies a database connection and the set of Explores that use that connection. A model file also defines the Explores themselves and their relationships to other views.
[connection, file path for look files and explores]
In our projects the file paths generally include
- data_groups
- view names
- manifest files
- sometimes explores are included as their own files in a project but must be referenced in the explore
How is git used to back up Looker Info?
Each project is its on repository (repo). Users check out their own branch. Depending on team structure you can have pull requests that must be approved by other team members
In a model what parameter is used to select the file paths for a project, explore ect.
include: “filepath/*”
Example Include : “*.view.lkml”
What is an explore and where do they generally live in a project?
An explore normally lives in a model lkml file.
An explore is a collection of data and how users will create looks and dashboards.
Name the 7 Key types (groups) of parameters for an explore
Structural Parameters
- Extensions
- Fields
- Tags (what are these?)
Display Parameters
- name
- description
- display group
Filter Parameters
- access filters
- always filter
- case_sensitive (how does this work?)
- conditionally_filter (What is this?)
- sql_always_having
- sql_always_where
Join Parameters (Between Views)
- Left
- Always join
Query Parameters
- TBD (learn more here)
Aggregate Table Parameters
- aggregate_table:table_name
- query
- materialization
Refinement Parameters
- explore: +base_explore{}
Explore parameters:
This parameter allows you to build upon the content and settings from another Explore, using the other Explore as a starting point
Parameter Name: Extends
Parameter Group: Structural Parameters
Example Code:
Explore: example {
extends: [explore1, explore2, …]
}
- Note in the base explore you must define a view_name. This is what you will reference in the extends parameter
https: //docs.looker.com/reference/explore-params/extends
Explore parameters:
This parameter forces the explore to be a library code. To use anything in this explore you will need to create a new explore that extends this base explore
Parameter Name: Extends
extension: required
Parameter Group: Structural Parameters
Example Code
Explore: example_explore {
extension: required
}
Explore parameters:
This is the parameter you can select to chose the base view for an explore. If this parameter is not included the default value for this parameter will be the name of the explore
Parameter Name: From
Parameter Group: Structural Parameters
Example code:
explore: explore_name {
from: view_name
}
Note for performance it is best to start at the lowest level of granularity. Goal is as many to one joins as possible in an explore for speed.
Explore parameters:
This is a specific set of fields that you want to include in the explore.
Bonus: what is the default value of this parameter?
Parameter Name: Fields
Parameter Group: Structural Parameters
Example Code:
explore: explore_name {
fields:[field_names, -joined_view_name.unwanted_field]
}
BONUS:
Default is ALL_FIELDS*
Note All Caps and * required
Explore parameters:
These are a new meta data field you can use to add information to an explore. This information is not available in looker IDE, but can be accessed with an API call.
Parameter Name: Tags
Parameter Group: Structural Parameters
Example code:
explore: explore_name {
tags: [Tag1, Tag2]
}
Explore parameters:
This parameter is used to give users a brief explanation of your explore. Users can see this explanation when they mouse over the little i
Parameter name: description
Parameter group: Structural Parameters
Example code:
explore: example_explore {
description: “This is the most excellent explore you will ever use built by the best analyst in the Universe”
}
Explore parameters:
This parameter is used to hide an explore from looker’s IDE. This is very helpful when you are developing an explore or have an base explore (library code) that can only be used if extended
Parameter name: hidden
Parameter group: structural parameters
Example code:
explore: example_explore {
hidden: yes
}
Type: yes | no
Model parameters:
This parameter will set the name of the model. Note this can be helpful as the default grouping of the explore navigation menu is by model name
Parameter: label
Parameter group: display parameters
Example code:
connection: “snowflake”
include: “/*.lkml”
label: “best model ever”
Explore parameters:
This parameter will set the the name of the explore in the navigation menu and all places a user sees the name of a explore displayed
Parameter: label
Parameter group: display parameters
Example code:
explore: explore_name {
label: “best model ever”
}
Explore parameters:
You can use this parameter to limit access to scoped data in your explore based on a user attribute. The user will still be able to access the dimension, or measure but only see values that correspond to the values stored in their user attribute.
i.e. CSMs will only be able to see their region AMER High Touch
Parameter: access filter
Parameter group: Filter Parameters
Requires two values
- field: customers.name
- user_attribute:
These are predefined by an administrator
NOTE: by default they are case sensitive
Example:
access_filter: {
field: fully_scoped_field
user_attribute: user_attribute_name
}
}
Explore parameters:
You can use this parameter to add the same filter to every explore. This filter will always start with the default value but users can change this default value. Users cannot remove this filter.
Parameter: awlays_filter
Parameter group: Filter parameters
always_filter: {
filters: [view_name.field_name: “filter expression”, …]
}
filters: [order.id: “123”, customer.id: “789”, customer.age: “>=18”]
Explore parameters
You can use this parameters to select if filters will be case sensitive. (Changes where like, or where = to where ilike)
explore: explore_name {
– Filter Parameters
case_sensitive: yes/no
}
Explore parameters
This is a filter that will only be applied if a certain field or combinations of fields are not included in the explore.
Parameter: conditionally_filter
Parameter Group: Filters
Parmeters required:
- filters: []
- unless: []
Example:
explore: order {
conditionally_filter: {
filters: [id: "123", customer.city: "Chicago"]
unless: [date, customer.date]
}
Explore Parameters
This will add an always having condition into your sql. Be careful with these. If a measure is not included in your explore the grouping may behave in ways you don’t expect. Also you may need to combine this with an always_join to ensure the users don’t get an error when they use the explore.
Parameter: sql_always_having
Parameter group: Filters
Example
explore: explore_name {
sql_always_having: ${orders.revenue} >= 100 ;;
}
This will add an always add a having condition into your sql for every part of the explore.
Note: use carefully it will always join in a view that includes the field for the the always having. It may behave in ways you don’t expect depending on a grouping function you include.
if you use Raw SQL you might need to include an always_join to make sure the view you need is always part of the sql statement. If you use a named measure Looker will always include the correct table
Explore Parameters
This parameter will always add a specific where clause to your queries. It is helpful like the always filter, but the end user cannot change the value of this where.
Parameter: sql_always_where
Parameter group: Filters
explore: explore_name {
sql_always_where: ${created_date} >= ‘2017-01-01’::DATE ;;
}
SQL Recap
What is the difference between having and where in Queries
Having condition applies to the aggregate group of data Sum(field), Count(*)
Where applies to the row level of the data
Explore Parameters
How do we combine different views in an explore using a key, foreign key?
What are the key parameters that make this work?
https://docs.looker.com/reference/explore-reference
Parameter: JOIN
Parameter Group: Joins
Key Parameters:
- join
- from (optional if different name than join)
- type (Join type. If inner or outer might be a good idea to make this an always join)
- sql_on: Join conditions ON
- relationship: is this a one-to-many.
- foreign_key: you use the foreign key to join to the primary key of the target table. You must have defined the primary key in the table you are join to
Explore: {
# Join_Parameters
join: view_name{
from: view
type: left_outer
sql_on: {view.field} = {view2.field};;
relationship: many_to_one
foreign_key: ${view.name}
}
}
Explore Parameters:
You use this parameter to ensure you always join several key tables together. This is important if you have an join that limits the data (inner, outer, cross etc) you should be using in the explore
Parameter: always_join
Parameter Group: Joins
Explore: {
always_join: [view_name1, view_name2, view_name3]
}
Note you must already have the joins defined in the explore
How do you use the query thing in explores?
Not sure how to use this quite yet. To follow up with Jennifer and team.
https://docs.looker.com/reference/explore-params/query
Explore:
Query Parameters:
Query:
Explore Parameters:
You can use this parameter with an aggregate query to improve the speed of your explore. This parameter requires you to take an explore / view and aggregate it to a level higher (day - > week). This will be a materialized view that is saved in memory, making queries execute much faster.
Parameter: aggregate_table
Parameter Group: Other
This is a big section that will be covered on its own.
High level it requires
- query: {
dimensions: [dimension1, dimension2, … ]
measures: [measure1, measure2, … ]
sorts: [field1: asc, field2: desc, … ]
filters: [field1: “value1”, field2: “value2”, … ]
timezone: timezone
}
materialization: {
datagroup_trigger: datagroup
}
For incremental builds
incrament_key
incrament_offset
Explore Parameters:
This is a quick way to refine an existing explore. Note this is not an extends
Parameter explore: +{
}
Parameter Group: Other
Explore Parameters:
This parameter is used in conjunction with the refine for explores or views, so that there can be no further refinements of this view or explore
Paramater: final
Parameter Group: Other
Type: yesno
example
explore: +orders {
label: “Orders Information”
final:yes
}
Explore Parameters:
What is the difference between refines and extends?
Refining an object adds a layer of modifications to the object, but, unlike extending, refining doesn’t make multiple copies of the object. The idea is to build upon a base object without modifying its original LookML.
Thus if you do not rename the refines, you will be able to access your new work in any place you see the refines.
Explore Parameters:
You use this parameter when you want to give an explore a different name, but start with a specific view and reference that view name in all future joins. This is very helpful if you pull in measures from one view into another view.
view_name:
Example
view_name: d_date_extended
If view_name is omitted, Looker will assume that the underlying view name is the same as the Explore name. Typically view_name is only used if you need to create multiple Explores from the same view, and reference the specific name of that view in the join path
Explore Parameters:
You use this parameter in a join to define in the looker IDE what the view name will be. If this same parameter is included in a view in the dimension or measure it will be superseded by this lower level.
Parameter: view_label
type: string
example
explore: example_explore {
join: new_view {
sql_on: ${somestuff} = ${somestuff}
view _label: “1. Show Me First”
}
}
What is a looker project?
A Looker project is a collection of LookML files that tell Looker how to connect to your database, how to query your data, and how to control the user interface’s behavior
