Looker Technical Basics The Alpha And O Of Data Provision 8211 Part 1
Management Summary
Looker is a powerful business intelligence (BI) tool that helps companies gain actionable insights from their data.
Looker’s architecture – an overview
Looker’s architecture consists of multiple layers that together form a flexible and scalable platform. The three main components are the data sources, the Looker data model (LookML) and the user interface. This architecture makes it possible to keep raw data in its original source while still producing comprehensive analysis and reporting. LookML itself will be examined in more detail in the next chapter.
Data sources
The data sources are the basis of every BI analysis and form the first layer of the Looker architecture. Looker supports a wide range of data sources, including traditional relational databases such as MySQL, PostgreSQL and SQL Server, as well as modern cloud-based platforms such as Google BigQuery, Amazon Redshift and Snowflake. This versatility allows organizations to seamlessly integrate their existing data infrastructures with Looker without having to physically move or transform the data.
By connecting directly to data sources, data integrity is maintained and data analyzes are always up to date.
Looker data model
The Looker data model, defined by LookML, is the heart of the Looker architecture. LookML is a powerful and flexible modeling language that makes it possible to transform complex data structures into logical and reusable models. By defining dimensions, metrics, aggregations, and relationships in LookML, consistent and scalable data models can be created that provide a foundation for accurate and comprehensive data analysis.
The Looker data model abstracts the complexity of SQL queries and allows users to perform data analysis without having deep SQL knowledge. This abstraction facilitates collaboration between technical and non-technical users and promotes consistent data logic across the organization. In addition, LookML supports the modularization of data models, which significantly improves the reusability and consistency of data analysis.
User interface – Viewer, Explore, and Developer layers
Looker’s user interface is divided into different layers: the viewer layer, the explore layer and the developer layer. The viewer layer provides users with an intuitive interface for interacting with reports and dashboards. The Explorlayer allows advanced users to perform interactive queries and explore data. The developer layer is intended for developers who create and manage complex data models with LookML. This structure ensures efficient use of Looker for users at different levels of data analysis, providing a seamless experience from visualization to data modeling.
LookML – Views, Explores, Models and Git
LookML, Looker’s modeling language, is an essential part of the Looker architecture and enables precise and efficient data modeling. In LookML the structure follows a clear hierarchy: Models, Views, Explores, Dimensions and Key Figures. Models contain views and define the relationships between them. Views represent data sources and define dimensions and metrics. Explores are based on views and enable interactive data analysis. Dimensions are attributes used to group data, while measures represent aggregated numerical values. This hierarchy ensures organized, consistent, and scalable data modeling.
A hierarchical representation of LookML objects
Views
Views are the basic building blocks in LookML. They represent a table or a logical view of the data source and define the underlying dimensions and metrics. Each view contains a description of the data structure, including the fields, their types, and the relationships between them.
Dimensions in views are attributes by which data can be grouped, such as date, product name, or customer segment. They form the basis for categorizing and aggregating the data. Key figures, on the other hand, are numerical values that can be analyzed and aggregated, such as sales, profit or number of orders. By defining calculations and aggregations within the views, complex analytical requirements can be met.
Another advantage of views is their reusability. Once defined, views can be used in different models and explores, significantly improving the consistency and efficiency of data modeling. In addition, the clear and structured definition of views enables easy maintenance and adjustment of the data models.Example
| view: orders { sql_table_name: public.orders ;; dimension: order_date { type: date sql: ${TABLE}.order_date ;; } measure: total_sales { |
Here defines the viewordersthe tablepublic.ordersand contains the dimensionorder_dateas well as the key figuretotal_sales.
Dimensions
Dimensions are fundamental building blocks in LookML that allow data to be grouped and analyzed according to specific attributes. For example, a dimension can be a date, a product name, or a customer segment.Example
| dimension: order_date { type: date sql: ${TABLE}.order_date ;; } |
Here defines the dimensionorder_datea date field that groups orders by their date.
Key figures
Metrics are numerical values that can be aggregated and analyzed. They make it possible to calculate important metrics such as sales, profit or number of sales.Example
| measure: total_sales { type: sum sql: ${TABLE}.sales ;; } |
Explores
Explores are the interface between the data models and the end users. They provide an interface through which users can conduct interactive queries and analysis. Explores are based on views and make it possible to combine and analyze data from different tables and sources.
An Explore represents a specific view of the data that is available to consumers of the data. Different dimensions and metrics can be combined and filtered to gain detailed and specific insights. By using joins and relationships between views, complex data structures can be mapped and analyzed.
The flexibility of Explores allows you to create customized analyzes for different user groups. Technical users can perform in-depth queries and analysis, while non-technical users can easily and intuitively access the information they need. This adaptability makes Explores a powerful tool for data-driven decision making.Example
| explore: orders { join: customers { sql_on: ${orders.customer_id} = ${customers.id} ;; relationship: many_to_one } join: products { |
The Exploreordersconnects the orders to both the customer database and the product database. This enables comprehensive analysis of orders in terms of customer and product information.
Models
Models are the highest structural units in LookML and serve as containers for Views and Explores. A model defines the logical structure of data analysis and contains the configurations and relationships necessary for consistent and efficient data analysis.
Within a model, the different views are linked together and the dependencies are defined. This makes it possible to model complex data relationships and hierarchies and ensure that data is represented correctly and consistently. Models provide a central location for managing and organizing data models and make it easier to maintain and scale the data infrastructure.
Another advantage of the models is the possibility of modularization and reusability. By defining models, specific data structures and logic can be reused and deployed in different places. This promotes consistency and efficiency of data modeling and enables easy customization and extension of data models.Example
| model: ecommerce { connection: “ecommerce_db” include: “/views/*.view.lkml” explore: orders { explore: products { |
In this example that definesecommerce-Modelthe connection to the databaseecommerce_db, closes all views from the directory/views/and creates two Explores:ordersandproducts. These Explores link orders to customers and products to categories.
Interaction of Views, Explores and Models
The interaction of Views, Explores and Models in LookML creates a robust and flexible data modeling architecture. Views provide the basic data structure, Explores enable user-friendly and interactive analysis, and Models organize and manage the entire data structure.
This structure makes it possible to perform complex data analysis and create consistent and reusable data models. The separation of the different levels makes it easier to maintain and customize the data models and promotes collaboration between technical and non-technical users.
By using LookML, companies can effectively model and analyze their data, make informed decisions, and optimize their business processes.
Example of full integration
To further illustrate how models, views and explores work in LookML, let’s look at a detailed example of integrating various components for analyzing e-commerce data.Model: ecommerce
| model: ecommerce { connection: “ecommerce_db” include: “/views/*.view.lkml” explore: orders { join: customers { sql_on: ${orders.customer_id} = ${customers.id} ;; relationship: many_to_one } join: products { sql_on: ${orders.product_id} = ${products.id} ;; relationship: many_to_one } } } |
This model defines a connection to the databaseecommerce_dband closes all views from the directory/views/a. It includes the Exploreorders, which contains two joins: one with thecustomers-View and one with theproducts-View. This allows orders to be linked to customer data and product information.View: orders:
| view: orders { sql_table_name: public.orders ;; dimension: id { dimension: order_date { dimension: customer_id { dimension: product_id { measure: total_sales { |
Theorders-View defines the tablepublic.ordersand contains different dimensions likeid, order_date, customer_idandproduct_id. It also calculates the key figuretotal_salesby aggregating sales. This structure makes it possible to analyze orders according to various criteria and calculate total sales.View: customers:
| view: customers { sql_table_name: public.customers ;; dimension: id { dimension: name { dimension: email { |
Thecustomers-View defines the tablepublic.customersand contains dimensions likeid, nameande-mail. These dimensions enable detailed analysis of customer information, including their names and email addresses, which can be useful for personalizing marketing campaigns and customer relationships.View: products:
| view: products { sql_table_name: public.products ;; dimension: id { dimension: name { dimension: category_id { |
Theproducts-View defines the tablepublic.productsand contains dimensions likeid, nameandcategory_id. These dimensions enable detailed analysis of product information, including product names and categories relevant to inventory management and sales analysis.
By combining these components, LookML enables comprehensive data modeling and analysis of e-commerce data. The modelecommerceserves as a framework for the data structure, while the viewsorders, customersandproductsdefine the specific tables and dimensions. The Exploreordersconnects these views together to enable detailed analysis of orders, customers and products. This integration supports comprehensive business analytics and helps optimize business processes and decision-making.
Git and Looker
Looker integrates Git for version control and collaboration. This integration makes it possible to manage LookML projects and track changes. Teams can create branches to develop new features or fix bugs without affecting the major release. Changes can be reviewed and merged, promoting controlled and collaborative development. By using Git in Looker, the history of all changes remains traceable and previous versions can be restored if necessary. This seamless integration significantly improves the efficiency and reliability of data modeling processes.
Conclusion:
Detailed exploration of the Looker architecture and its components provides an in-depth understanding of how to create and manage advanced data analytics solutions. Use this knowledge to improve your…Data strategyto optimize and make informed business decisions.In the second part of this article I will discuss the scalability of the data, use of APIs and data usage control.
