Understanding Google Looker: 4 Critical Aspects

on BI Tool, Data Driven, Data Driven Strategies, Looker • May 31st, 2021 • Write for Hevo

Google Looker | Hevo Data

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.

Table of Contents

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.

Google Looker is a sophisticated platform for business intelligence (BI), data applications, and analytics. Looker helps you to explore your data through user-friendly workflows, share data by using collaborative dashboards, and develop insightful visualizations of your data to increase the productivity of your company by gaining important business decisions from them. Since its acquisition by Google, it has become an integral part of the Google Cloud Platform.

Google Looker is a completely browser-based platform thereby eliminating the need for desktop software and allowing for the scheduling and automation of report distributions, setting of custom parameters to receive alerts, and so on.

Google Looker uses its lightweight modeling language known as LookML which describes to Looker how to query data and will be discussed in the next section. It also works with transactional databases like Oracle, MySQL, and data warehouses like BigQuery, Snowflake, Redshift, etc., thereby, making it easy for members across your organization to gain access to the data to create reports and dashboards.

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'
.

Simplify your Data Analysis with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo helps you integrate data from 100+ data sources (including 30+ Free Data Sources) to Google Looker in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds, in a smooth fashion without having to code a single line. 

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources, that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.

You can try Hevo for free by signing up for a 14-day free trial.

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. For more on the Model parameter, visit here.
  • 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. For more on the Explore parameter, visit here.
  • 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. For more on the Field parameter, visit here.
  • 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. For more on the filter expression parameter, visit here.
  • 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. For more on the Join parameter, visit here.
  • 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. For more about the View parameter, visit here.
  • 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. For more about the Dashboard parameter, visit here.

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

Looker API can be used to connect to Clients Software Developments Kits (SDKs), as it can be used with any programming language or application environment that can make HTTPS requests and have JSON responses. This can be done by constructing a Looker API HTTPS request by using Looker Ruby SDK or by generating a client SDK library.  Whenever this is done, Looker authenticates your API request using the OAuth 2.0 bearer access token in the HTTP authorization header of your request. For more about Looker API, visit here.

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.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be a challenging task and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications into your Data Warehouse to be visualized in a BI tool such as Google Looker. Hevo is fully automated and hence does not require you to code. You can try Hevo for free by signing up for a 14-day free trial. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

No-code Data Pipeline For Looker