The preparation work for this chapter consists of the following steps:

1. In your BigQuery environment in the bigquery-public-data list, scroll to find thelook_ecommerce dataset (https://console.cloud.google.com/marketplace/product/bigquery-public-data/thelook-ecommerce) (Figure 2.1).
2. Click on Copy in the top-right of the window above the dataset description (Figure 2.1).

Figure 2.1 – BigQuery dataset

3. Click Create to create a new dataset that will ‘receive’ the copy of thelook_ecommerce dataset. Give your future dataset a name (it can be the exact same name - thelook_ecommerce, choose US multiregion, and create the dataset. You might be requested to activate the Data Transfer API.
4. Once the dataset is copied to your newly created dataset, you will see it appear under your project name in the BigQuery Explorer (Figure 2.2).

Figure 2.2 – BigQuery tables...

When you create your LookML project and choose Generate Model from Database Schema, Looker detects the views (tables) and the view fields (columns) automatically. It usually adds three parameters to every column: name (dimension:), data type (time, string, number, yes/no, tier, date, etc.), and SQL (by default, it references the table and the name of the column in this table); ${field_name} references a specific field within a LookML file. Sometimes, Looker adds a primary_key parameter, if it detects a field that can be potentially a primary key field; it is important for join operations (many to many, many to one, one to many, etc.). By default, Looker creates all the fields (columns) as dimensions (qualitative data) and adds some measures.

Our job is to enrich and complete this data by adding the parameters to our existing dimensions and measures, and by creating new dimensions and measures.

How do we know what is needed? It is something to do before...

Measures in Looker are aggregations of one or more dimensions (or unique attributes of the data) such as a count or average. They are used to calculate key performance indicators (KPIs) and help business users analyze data using different aggregated attributes. Three of the most common measure types are sum, average, and count. Measures represent the quantitative information in Looker.

By combining measures and dimensions, you can create visualizations and dashboards that help you understand both the quantitative and qualitative aspects of your business.

When you create your LookML project and choose Generate Model from Database Schema, Looker will automatically create Count measures for your tables (views). In Your LookML environment, go and check what are the measures that were created in your views. For example, the following measures in distribution_centers.view might not be relevant for your data exploration and might not make sense at all:

Everything you do in LookML is aimed at making your data clean, complete, well organized, and ready to be used. There are numerous ways to describe and organize your data in LookML. In this section, we will go through some of the parameters to do so.

Getting ready

Let’s continue working in order_items.view. Make sure you have it open in your LookML environment. When working with the How to do it... section, try all the provided parameters in your environment. Don’t forget to click Save Changes to see how it all looks in the Explore tab.

How to do it...

The different parameters you can use to organize your dimensions and measures in views (Figure 2.15) and make it clear and accessible are the following:

• Comments: The comments in LookML start with #. The automatic comments are added by Looker when the views and model were created in your project in Database Schema. Review the comments and make some changes. Add your comment...

There are two main types of filters in LookML:

• Explore-level filters: These let you restrict the data that you are viewing to rows of interest. Any field in your Looker instance can become a filter.
• Field-level filters: These filters are applied to a specific field in the view and can be used to restrict the data that is available for that field in all Explores that use the field.

In this section, we will concentrate on the filters used in the LookML views.

Getting ready

For the examples in this section, we will work with inventory_items.view in the LookML project environment.

How to do it...

The different filter-related parameters that can be used in the views are the following:

• Can filter: The can_filter parameter lets you prohibit a dimension or measure from being used as a filter.
• Skip drill filter: If skip_drill_filter is yes, this dimension will not be included in drill filters.
• Filters: filters...

In this section, we will work on our model file. Every LookML project can contain multiple model files if needed. Model files define Explores and their relationships to other views. Models can be placed in the Models section of the Looker IDE. The model’s name is taken from the filename and must be unique across your instance.

Getting ready

In this section, we will work on our unique lb_thelook_ecommerce.model model file.

In your LookML project in the left panel, click on models and then click on lb_thelook_ecommerce.model. Our existing model contains multiple parameters and Explores. Explores can be declared (created) based on one view or multiple views joined.

How to do it...

1. To create an Explore based on one view (for example, distrubution_centers.view), the syntax is simple:

   explore: distribution_centers {}

2. In the preceding case, the Explore name equals the base view name. In a Looker Explore, the base view is the initial data...

In the model file created in your LookML project, there are some standard parameters added, such as the following:

• connection: This parameter defines the database/data warehouse connection to be used for this model.
• include: This defines what view files can be used in this model to build the Explores. In our case, we include all the views.
• datagroup: This assigns a caching policy for Explores. Datagroup can also be used to regenerate PDTs.
• persist_with: To use the datagroup caching policy as the default for Explores in a model, you can use the persist_with parameter at the model level, and specify the datagroup name.

In this section, we’ll review some advanced Model parameters and what they are used for.

How to do it…

Let’s add a few advanced Model parameters to our model:

1. label: The label parameter changes the way that this model will appear in the Explore menu. Unless specified, the label...

Your LookML project can contain multiple different files. We already worked with view and model files, and we could see what the folders are for (models and views folders). You can create a new folder or file by pressing + (Figure 2.19).

Figure 2.19 – LookML files

Let’s add some more elements to enrich our LookML project.

Getting ready

Make sure you are in your LookML project environment with Development Mode activated (see the blue bar at the top of your Looker web page).

How to do it...

Let’s add some information about our LookML project:

1. Click on the README.md file – in there, you will see some explanations about Looker; keep it as it is.
2. Let’s add another document file to add some information about our e-commerce project – click the + sign near File Browser and click on Create Document.
3. Name the document AboutThisProject and click Create.
4. Click Edit...

Geodata in your tables gives you the possibility to create map chart visualizations. In this section, we will have the opportunity to use the things we previously learned in combination with some new elements related to geodata.

Getting ready

You will need to download the following file to your computer, then drag and drop this file in your LookML environment (as shown in the previous section): communes.topojson - https://github.com/PacktPublishing/Business-Intelligence-with-Looker-Cookbook/blob/main/communes.topojson. This file contains the coordinates of the French communes – cities, towns, villages – local administrative units.

To visualize the geodata in a map chart, you need to include at least one of the following fields in your query: a dimension based on latitude and longitude data, a dimension with a map layer assigned to it, or a ZIP code dimension.

Note

According to the documentation, a ZIP code dimension automatically...

There are multiple ways to reuse the elements in LookML, such as the following:

• include
• extends
• ${}
• Refinements
• Looker Blocks

We’ve already used $ quite a lot to reference the existing dimensions and measures.

We have also used the include parameter, which specifies the LookML files that will be available to a model, a view, and an Explore. If you want to use or reference a LookML file within another file, you must add it with the include parameter.

Let’s review some other techniques you can use to reuse elements in Looker.

Getting ready

Make sure you are in your LookML environment, and Development Mode is activated.

How to do it...

The extends parameters can be used both for views and Explores. For example, you can use the extends parameter in views to reuse the content and settings of another view, with the ability to override any settings that you want.

We can learn how to do that by following...