Data Visualization with Looker: A Step-by-Step Guide

1. Introduction

One of the important stages of data analysis is to visualize the data effectively. Although it is great to understand a data set and get ideas with SQL queries, working on a data set with a visualization tool can give us a much different perspective and make our job easier. Visual tools such as charts and graphs are very effective for exploring data sets.

In this article, we will explore Looker, a data visualization tool that provides great convenience thanks to the many features it contains. Before proceeding through the application, we should discuss some terms and features within Looker.

2. Looker

Looker is a BI and big data analytics platform that allows users to perform real-time business analytics, explore and analyze data.

Looker is a browser-based cloud application; it can be accessed by logging in from the defined URL address via an internet browser. It is also possible to access via mobile application.

Let’s look into basic Looker features and concepts at this stage.

2.1 Role

Every user has a role in Looker. A role defines a user or group’s privileges for a set of models in Looker. A user can have multiple roles. There are admin, developer, user, and viewer roles. Roles are assigned by the user with the Admin role.

Admin: Admin role is assigned to users who are responsible for the general administration of Looker instances. This role is used for system configuration, user management, permissions, and other administrative tasks. Admin role provides access to everything and management rights.

Developer: Developer role is assigned to users who create and manage LookML projects. These users can define data models, dimensions, and other LookML objects. They can also create and manage reporting environments.

User: User role is assigned to general users who have the right to view and filter reports, and perform basic analysis tasks. These users cannot create reports or make any changes.

Viewer: Viewer role is assigned to users who are authorized just to view reports. These users do not have permission to make changes to data or create reports.

2.2 LookML

It is a Looker-based language used to create semantic data models. LookML establishes and defines dimensions, calculations, and data relationships in a data set. It is independent of the SQL language but offers the opportunity to use any SQL statement as a capsule.

2.3 Model

It is a customized LookML portal for databases. There can be more than one model within a LookML project. Each model can present different data to different users.

2.4 Explore

Explore is the view from which users can run their queries. We can think of it as “FROM” in an SQL statement. We can work on a data set via Explore.

2.5 View

View displays the list of fields (dimensions, measures) in the dataset and their connection to a base or derived table. The view usually references our underlying database post-connection but can also represent the derived table optionally. A view can be combined with another view, or a “join” structure can be established between them. It will be sufficient to define it in the model file in such a case.

2.5.1 Dimension

In Looker, dimension, defines the dimensions within our dataset. It can be listed and grouped within the view file and used for filtering. A new dimension can also be created using the custom dimensions in the data set.

2.5.2 Measure

Measure refers to the measurements in the data set. For example, we can use COUNT, AVG, MIN, MAX, or SUM functions. Measure can be used to filter grouped values.

3. Hands-on Example

In this section, we will explore the fundamentals of Looker with a practical example.

We will use the data set consisting of the data obtained through the web scraping in my previous article that is in BigQuery. This way, we will demonstrate the end-to-end process of data collection, creation, and visualization stages.

3.1 Connection

In order to create charts from data on Looker and visualize them on a dashboard, we first need to connect our BigQuery table to the Looker project.

1. For the database connection, we open the “Admin” section from the “Main Menu” panel and proceed to the “Connections” page under “Database”.
2. On the Connections page, we click the “Add Connection” button. We name our connection from the “Name” section. In the “Dialect” section, we select “Google BigQuery Standard SQL” and with this selection, other parameters that need to be filled in appear at the bottom.
3. We enter the Google Cloud Platform project ID in the “Billing Project ID” section.
4. We add the Dataset ID where our table is located in the “Dataset” section.
5. A service account is required for Looker to connect to our BigQuery table. To do this, creating a service account for Looker on the Service Accounts page on IAM & Admin in our GCP project is necessary. This service account should be assigned the “BigQuery Job User”, “BigQuery Data Viewer” and “BigQuery Data Editor” roles in the IAM portal.

6. After completing these steps, we go to the Service Account and download the key file in JSON format by clicking on the “Add Key” button in the “Key” section.

7. We upload this JSON Key from the “Upload File” section by clicking the “Service Account” button in the “Authentication” section on Looker.

8. After this step, we can test our connection with the “Test” button and if there is no problem, we can create the connection with the “Connect” button.

3.2 LookML Project

After creating the link, we can move from the“Main Menu” section to the “Projects”section. We will create a LookML project with the “New LookML Project” button on the Projects page.

“Development Mode” must be turned on while these operations are carried out. Additionally, your account must have a “Developer” or “Admin” role.

1. To create LookML, we must add our project name to the “Project Name” section on the page that opens. It should also be noted that the project name used should not contain a “-” character.
2. In the “Starting Point” section, we leave the default option “Generate Model from Database Schema”.
3. In the “Connection” section, we select the connection option we created.
4. In the “Build Views From” section, “All Tables” is selected as default. If you want to transfer only one table, use the “Single Table” option and enter the table name in the “Table name” section.

5. Afterward, we create the LookML project by clicking on “Create Project”.

The created project is listed under “Main Menu”. When you click on it, the LookML project opens.

3.3 Git Integration

Looker offers a Git service to save and track changes and manage file versions. The LookML project connects with the Git repository and can work with Git if the user’s “Development Mode” is active.

Looker supports the following Git platforms:

• GitHub
• GitHub Enterprise
• GitLab
• Bitbucket
• BitBucket Server
• Phabricator Diffusion
• Google Cloud Source
• Gerrit
• Any Git server that allows authentication via SSH keys

To connect to Git, we can either use the “Configure Git” button on the right or the “Configure Git” button in the “Settings” section.

When we proceed with this button, a new page will open. We need to write the URL of the Git repo here.

I preferred to proceed with Cloud Source Repository here. To do this, I created a repository from the Source Repository page in the Google Cloud Platform project.

On the next page, we are asked for “username” and “password”. We copy the “Clone with command line” section in the window that opens with the “Clone” button on the Cloud Source Repository page in our Google Cloud Platform project and we paste it into the desired URL section without “git clone” on Looker. We select the Source Repository for the “Git Hosting Service” section and proceed.

After this process, we copy the SSH key from the SSH Key for “test” section in the window that opens, go back to the Cloud Source Repository page and click the “Manage SSH Keys” button in More Items at the top right.

In the new window, we click on the “Register SSH Key” button, give a unique name to the key we copied and complete the registration process.

3.4 Explore View

After completing the steps until this stage, we can start creating a few graphs on the data set. We can open the file of our table in the “Views” folder in the LookML project we created. If necessary, we can add custom dimension and measure parameters to the view file. In addition, if necessary, we can perform “JOIN” operations between the tables in the “view” within the data set we are connected to. We can also define it in the “model” file.

We can create the chart from the visualization section with the Explore Forex Rate option in the View file.

On the left of the new screen that opens, there are the Dimensions and Measures fields in our data. We can start visualizing by making a selection in this area.

For our first example, we can display the latest value of “Euro”. For this, we choose the “Forex Selling” dimension metric.

After this selection, when we click the “Run” button at the top right, the result data will appear at the bottom. To see the most up-to-date result for the last day, we can go to the “Date Date” metric in the “All Fields” section. We click on the “Filter by field” button on “Date”.

After this process, a filter will appear in the “Filters” section. Here, we can set the filters as we want. We adjust the filter to bring in the last hour’s data so that we can only see the information related to “EURO” and get the most up-to-date result.

After this process, we go to the “Visualization” section. We can choose the chart we want for visualization.

By selecting “Single Value” in the panel seen in the screenshot, we select the chart that shows only values. Then, when we click “Run” again, the “Single Value” visual will appear in the “Visualization” section.

We can edit the graph from the “Edit” section. With the update we make in the “Value Format” section in “Style”, we add the “Euro” sign and change the format, and we can also track the change in the visual.

When we are done with the graph, we can click on the “Explore actions” section at the top right.

Here we have various options:

Save: In the window opened with the Save button, we can create a new dashboard with the “As a new dashboard” option and save our chart in it. We can also add to a previously created dashboard with the “To an existing dashboard” option. With the “As a Look” option, we can save our work as Look and open it when necessary, perform operations, and add it to a different dashboard.

Download: Download option enables downloading in different formats and content selections.

Send: Send option allows sending the report to supported addresses.

Share: Share option allows copying the URL for sharing.

Embed URL: Embed URL option allows secure embedding of authenticated content.

Get LookML: With the Get LookML option, we see three different content types: Dashboard, Aggregate table, and Derived table. We can copy the content to these and use them in a different folder in the LookML project.

Merge Results: Merge Results option provides the opportunity to combine the obtained result with another result in a common area.

In this section, we create a dashboard from “As a new dashboard”, name it and save it.

We can access the Looks and Dashboard sections with “Shared Folders” under “Folders” in the “Main Menu” section.

Let’s create another example: a visual in table format where we can see “Daily Maximum-Minimum Values”. Again, we switch to the Explore section with “Explore Forex Rate” via View. From Field, we select “Currency Code” as the dimension. Using the Date filter, we add a filter for the last 1 day. However, we must use the MAX and MIN functions this time to see the highest and lowest values during the day. We will use the “Custom Fields”feature under “All Fields” on the left side.

“Add” button in the “Custom Fields” section provides 3 different options:

• Custom Dimension
• Custom Measure
• Table Calculation

Since we will create a ““measure” here, we click on the ““Custom Measure” option.

In the next window, we first select the field to measure. Afterward, we must select “Measure type”. We can also create a measure using a ready type. We select “Max” and save it by giving it a name.

We follow the same process for different fields using the “MIN” function. The results are listed under “Custom Fields”. We select the fields we will use in the list.

In the Visualization section, we select “Table” and run it with the “RUN” button.

After running it, we can see our results and make various adjustments from the “Edit” section. This time, we can also save the work done in the dashboard we created before.

3.5 Dashboard

We can view the Looks we have prepared on the Dashboard. We can access it under the “Folders” file in the Dashboard that we created from the Explore section. We can design the Looks we import, add different filters, and create different things with the features offered by Looker.

To edit the Dashboard, we click on the “Dashboard Actions” button at the top right and click on the “Edit dashboard” option.

By clicking the “Edit dashboard” button, we can rearrange the Looks on the Dashboard as we want.

When editing the Dashboard, 3 different options are available at the top menu: “Add”, “Filters”, “Settings”. When we click on the “Add” button, there are 4 different options:

Visualization: When we click on this button, the user is offered the LookML project selection and then the Explore page opens with the view selection. Thus, the user can create Looks and add them to the Dashboard screen from here.

Text: When we click on this button, a text panel opens. The text created here can be placed anywhere on the Dashboard by the user.

Markdown: With this button we can create“Title”, “Subtitle” or “Body” and place it on the Dashboard.

Button: With this option, a button is added to the Dashboard screen. The button can be named and redirected to a URL.

To add the Kartaca logo to the Dashboard panel, we select the “Markdown” option from the “Add” section and add the “Karaca” logo to body section.

Then, we click on the “Button” option from the “Add” section and write the Kartaca website address here.

When we want to add filters to the graphics (Look) we created, we click on the “Filters” option and then click on the “Add Filters” option from the opened panel.

With this option, the LookML view in the Dashboard appears.

Here, we need to select the field we want to add a filter to. Let’s make an example of a filter on “Date”.

On the screen that opens, we can name the filter with “Title”. We can select the filter type with“Control”. In this example, we select “Date Range”. We can select the default value of the filter with the “Configure Default Value” option. We can add our filter with the “Add” button.

Added filters are listed under the “Filters” panel on the Dashboard screen.

If we want a filter to affect a certain graphic on the Dashboard, we can go to the filter editing panel with the “Edit” button on the filter and select the graphic we want from the “Tiles To Update” section on this screen.

After these edits, we can save the updates with “Save”.

For example, if we want to add a scheduler, we must select the “Dashboard actions” section in the Dashboard panel, but the “Schedule delivery” option is not enabled.

This is because “Development Mode” is enabled in the project. Therefore, we exit this mode with “Exit Development Mode”. When we try again afterward, we expect Schedule Delivery to be activated.

When we click on the “Schedule Delivery” button, a panel opens. By choosing from here, we create a job that periodically sends an email in PNG format every morning at 09.00.

On this screen, we can check the Schedule Delivery job with the “Test now” button and then save it. In addition to email, sending can also be done via supported systems such as Slack.

4. Conclusion

Looker is a tool with many features. In this article, I covered connecting Looker to a dataset on BigQuery and general visualization processes with Looker. I hope this article has helped provide practical insights into Looker-related concepts and basic operations.