Understanding Google Looker: 4 Critical Aspects

Having the right visualization platform can help your business grow as making the right decisions becomes easier due to appropriate insights derived from what you see.

• Google Looker is one of the key Business Intelligence tools in the marketplace that can be leveraged to improve the efficiency of operations and steer business growth.
• This blog post aims at introducing you to a veritable visualization tool known as Google Looker that allows you to create fascinating dashboards which can be shared across your organization to quickly gain insights from your datasets through the efficient analysis that the use of  Google Looker guarantees you.

What is Google Looker?

Looker Data Sciences Incorporated is a computer software company that was founded in the United States of America with its headquarters in Santa Cruz, California. It was started in January 2012 by Lloyd Tabb and Ben Porterfield and was acquired by Google in 2019 making it part of the Google Cloud Platform.

Key Features of Google Looker

The key features of Looker are as follows:

• Create Custom Applications: Users can build custom applications to provide tailored visualization experiences to different organizations according to their needs.
• Support for Advanced Integrations: Platforms like Google BigQuery, Snowflake, AWS Redshift along with 50+ SQL dialects are supported using various Connectors and Integrations. 
• Latest BI Tools: Robust support for creating Real-Time Dashboards is provided and support for the latest Business Intelligence tools is present to improve reporting. 
• Support for Advanced Hosting: To ensure data reliability and safety, multiple Cloud-based platforms including Google Cloud Platform (GCP), and Amazon Web Services(AWS) are supported. 
• Looker ML Functionality: This feature is also known as LookML and is used to describe the measures and dimensions of all the projects stored and being analyzed on Looker.

When or When not to Use Google Looker?

When you are in need of customized visuals, a single source of truth, the best customer support, and collaborative dashboards, you can choose Looker. Since it’s completely browser-based, it reduces the possible confusion of your team. The pricing customization option will help you to get the necessary options alone for your company’s analytics goals.

In case you are already a Microsoft ecosystem user, Power BI you suitable option. Using another tool will lead to unwanted confusion and leads to additional costs. For an additional intuitive platform, you can choose tableau over Looker.

What is LookML?

LookML is a lightweight programming language used by Looker for the description of dimensions, aggregates, calculations, and showing data relations in a SQL database. It does this by constructing SQL queries against a particular database.

A typical LookML project will consist of a model, view, and dashboard files that are controlled via a git repository. Information about the tables to be used and how they are to be joined is found on the model files while the view files hold information about how to calculate data about each table or multiple tables.

LookML is very insightful and easy as it does all of this by separating structures from content making the query structure independent of the query content, therefore, focusing only on the content needed and not the complexities of the SQL structure. Below is a code sample of a LookML project showing a store that has a model file and two view files.

######################################
# FILE: ecommercestore.model.lkml    #
# Define the explores and join logic #
######################################
connection: order_database
include: "*.view.lkml"
explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.id} ;;
  }
}
 
##########################################################
# FILE: orders.view.lkml                                 #
# Define the dimensions and measures for the ORDERS view #
##########################################################
view: orders {
  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }
  dimension: customer_id {      # field: orders.customer_id
    sql: ${TABLE}.customer_id ;;
  }
  dimension: amount {           # field: orders.amount
    type: number
    value_format: "0.00"
    sql: ${TABLE}.amount ;;
  }
  dimension_group: created {                # generates fields:
    type: time                              # orders.created_time, orders.created_date
    timeframes: [time, date, week, month]   # orders.created_week, orders.created_month
    sql: ${TABLE}.created_at ;;
  }
  measure: count {             # field: orders.count
    type: count                # creates a sql COUNT(*)
    drill_fields: [drill_set*] # list of fields to show when someone clicks 'ORDERS Count'
  }
  measure: total_amount {
    type: sum
    sql: ${amount} ;;
  }
  set: drill_set {
    fields: [id, created_time, customers.name, amount]
  }
}
 
#############################################################
# FILE: customers.view.lkml                                 #
# Define the dimensions and measures for the CUSTOMERS view #
#############################################################
view: customers {
  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }
  dimension: city {                    # field: customers.city
    sql: ${TABLE}.city ;;
  }
  dimension: state {                   # field: customers.state
    sql: ${TABLE}.state ;;
  }
  dimension: name {
    sql: CONCAT(${TABLE}.firstname, " ", ${TABLE}.lastname) ;;
  }
  measure: count {             # field: customers.count
    type: count                # creates a sql COUNT(*)
    drill_fields: [drill_set*] # fields to show when someone clicks 'CUSTOMERS Count'
  }
  set: drill_set {                     # set: customers.drill_set
    fields: [id, state, orders.count]  # list of fields to show when someone clicks 'CUSTOMERS Count'
.

Understanding SQL Generation with Google Looker

Looker formulates SQL queries based on a LookML project that describes the relationship that exists between tables and columns in a database, it generates the SQL queries and then submits them against a database connection. In LookML, every parameter has a level of control of how Looker generates SQL, by altering the structure, its contents, or behavior of the query. Below is an example of a generated SQL statement on Looker.

SELECT
   <dimension>, <dimension>, …
   <measure>, <measure>, …
FROM <explore>
LEFT JOIN <view> ON …
LEFT JOIN <view> ON …
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND …
GROUP BY <dimension>, <dimension>, <dimension>, …
HAVING <measure_filter_expression> AND <measure_filter_expression> AND …
ORDER BY <dimension> | <measure>
LIMIT <limit>
.

What are the Components of a Google Looker Query?

Looker queries comprise fundamental parameters that are applied to a LookML project to generate a complete SQL query, these parameters are the elements a user will use when building a query on the Looker Explore page and they consist of the following:

• Model Parameter: This refers to the name of the LookML model to target and it specifies the target database. The model files define Explores and show their relationships with other views.
• Explore Parameter: This is the name of the Explore to query, it populates the SQL FROM clause. It is the starting point for a query in the Looker application, it references views and each Explore can be used to join other views.
• Fields Parameter: There are several types of fields in LookML. Field can be used to indicate the dimensions, dimension groups, measures, filters, and parameters to be included in a query that would populate the SQL SELECT clause.
• Filter Parameter: This filter expression is an advanced way to filter Looker queries. It can be used to apply to zero or no more fields, that are found on the SQL WHERE and HAVING clauses.
• Sort order Parameter:  This is the field to sort by, and the order in which the sorting will be done, it uses the SQL ORDER BY clause.
• Join Parameter: Join parameter allows you to have multiple views to be added to a single Explore, thereby, enabling you to have access to and compare fields from different views at the same time. When constructing SQL queries, the join clause parameter is only included by Looker when required but when building a query in Looker, you do not have to specify how tables are joined together as this information is encoded in the model. 
• View Parameter: A view is used to represent a table of data in Looker and it does not matter if the table is native to your database or was created using Looker’s derived table. In each view, there are field definitions that correspond to a column in the reference table or a calculation in Looker.
• Dashboard Parameter: A dashboard parameter consists of groups of data visualizations known as elements that are used to impact the outlook of the entire dashboard. The dashboard can be created via looker UI or by using LookML.

What is the Google Looker API?

The looker API is a secured application programming interface that is used to manage Looker instances and retrieve data through the Looker data platform. Applications or automated scripts are written using Looker’s API to make provisions for new Looker user accounts, run queries, schedule reports, etc.  

Looker instance’s API can be accessed through its API URL where you can specify an API path by entering it in the API Host URL field on the Admin>API page using either of the following formats:

https://<instance_name>.api.looker.com
https://<instance_name>.api.<my_region>.looker.com
https://<instance_name>.looker.com

What is Google Looker Pricing?

Looker will not disclose its pricing details publicly. It provides customized plans based on the number of users and deployment scale. Looker pricing is designed to fit businesses of all sizes.

What are the Alternatives for Google Looker?

Sisense

Sisense is a Business-Intelligence, AI-driven software company founded in 2004 by Elad Israeli, Eldad Farkash, Aviad Harell, Guy Boyangu, and Adi Azaria. Its headquarters are located in New York City, New York. The main goal of Sisense is to provide APIs for customizations and building personalized analytic experiences for any application.

Tableau

Tableau is a modern Data Analytics and Business Intelligence platform. It is an easy-to-use tool, hence, it offers a smooth experience to its users. Some of Its amazing features include real-time analytics, quick responsiveness, and interactive dashboards. It also offers simple yet appealing graphics/visualizations that you can use to present your data pictorially. It comes with all the features needed for data extraction, data processing, and generating reports and dashboards. 

Power BI

Power BI is a business analysis tool from Microsoft that can be used as a cloud service or as a standalone on-premise installation. It lets users connect to various data sources and ask questions about the data through queries. It also lets users transform data into reports and dashboards to aid decision-making. Being an offering from Microsoft, Power BI offers tight integration to all the Microsoft ecosystem components like SQL Server, Microsoft Azure databases, etc.

Conclusion

This blog post explored Google Looker by explaining what Google Looker does and how it queries data using its programming language called LookML. It went further to explain the components found in a query in Google Looker and mentioned how to connect to Looker using APIs to optimize its visualization capabilities.