Looker Technical Basics The Alpha And O Of Data Provision 8211 Part 2
Management Summary
Experience the versatile possibilities of Looker! After we get inPart one an overview of Looker’s architecturethis article will walk you through the semantic layer, API integration, and innovative embedding techniques. Leverage Looker’s latest technical foundations for streamlined data analysis.
Access rights – data, feature and content access
Access in Looker is based on three fundamental pillars: data access, feature access, and content access. These pillars form the basis for fine-grained access control that ensures users can only access the data and functionality relevant to their role. Data access controls which data sources and fields the consumers of the data can see. Feature access controls what actions users can perform within Looker, such as creating reports or exporting data. Content access defines which dashboards and reports are accessible to users.
User attributes
User attributes are key parameters in Looker that allow you to customize data access. You can define specific attributes for each user, such as department, location, or role, and use these attributes to control access to data and functionality. An example would be that a sales representative is only allowed to access the sales data for their region. User attributes enable dynamic and flexible access control that can easily adapt to changing organizational structures and business needs and are applied in combination with access filters.Example: Defining a user attribute in LookML
| user_attribute: region { label: “Region” type: string } |
Access filters – Row level control
Access filters provide a dynamic way to restrict data access based on user attributes. These filters can be applied automatically to ensure that users only see the data that is relevant and acceptable to them. A typical scenario is filtering sales data by region or customer segment, depending on user attributes. For example, a sales representative from the North region might only see sales figures for that region, while another representative from the South region could only see the corresponding data for their region.Example: Applying an access filter in LookML
| explore: sales { access_filter: { field: region user_attribute: region } } |
Field access – column level control
Field Access in Looker allows administrators to control access to specific data fields. This is particularly useful when certain fields contain sensitive information that should only be visible to selected users. For example, salary information in HR dashboards can only be made accessible to users with appropriate permissions. By defining field access rules, companies can ensure that sensitive data remains protected and only seen by those who actually need to access it.Example: Restricting field access in LookML
| view: employee_data { field: salary { access_grant: can_view_salary type: number } } |
Derived Tables
Derived tables are virtual tables that are created based on SQL queries or existing data models. They offer a flexible way to transform and enrich data before using it for analysis. An example might be a derived table that calculates aggregate sales data for weekly reports. Derived tables can also be used to perform complex calculations and data manipulation without changing the underlying data sources. This enables a high level of flexibility and adaptability in data analysis.Example: Creating a derived table in LookML
| derived_table: { sql:SELECT order_id, customer_id, SUM(total_amount) as total_sales FROM orders GROUP BY order_id, customer_id ;; } |
Ephemeral vs. persistent derived tables
An important concept about derived tables in Looker is the difference between ephemeral and persistent derived tables.Ephemeral derived tablesare created at runtime and only exist as long as the query is running. They are useful for ad hoc analysis and testing because they do not require persistent storage and can be updated quickly. However, these tables reload the database with every query because they need to be recalculated with every query.Example: Ephemeral derived table
| view: ephemeral_sales { derived_table: { sql:SELECT order_id, customer_id, SUM(total_amount) as total_sales FROM orders GROUP BY order_id, customer_id ;; datagroup_trigger: sales_datagroup } } |
Persistent derived tableshowever, are calculated once and stored in the database. They offer the advantage that they can be reused without reloading the database with each query. This significantly improves query performance, especially for large datasets and complex calculations. However, persistent tables need to be updated regularly to ensure that the data remains current.Example: Persistent derived table
| view: persistent_sales { derived_table: { sql_trigger_value: SELECT MAX(updated_at) FROM orders sql:SELECT order_id, customer_id, SUM(total_amount) as total_sales FROM orders GROUP BY order_id, customer_id ;; } } |
The difference between ephemeral and persistent derived tables is critical to the performance and scalability of Looker implementations. While ephemeral tables are useful for fast and flexible analysis, persistent tables offer a more efficient solution for regular and large-scale data queries. The choice between these two approaches depends on the company’s specific needs and data infrastructure.
Native vs. SQL Derived Tables
Native Derived Tables (NDTs) use LookML to define the table structure and leverage Looker’s native functions and abstractions, while SQL Derived Tables (SDTs) use raw SQL for their definitions. NDTs provide simplicity and easier maintenance within the LookML ecosystem, while SDTs provide flexibility for complex SQL logic.Example: Native Derived Table
| view: native_sales { derived_table: { explore_source: sales { column: order_id { field: order_id } column: customer_id { field: customer_id } column: total_sales { measure: total_amount } } } } |
Aggregate table literacy
Understanding and using aggregated tables is critical to optimizing analytical performance. Aggregate tables summarize data to speed up complex calculations and reduce query time. By strategically implementing such tables, companies can significantly increase the efficiency of their data analysis. An example would be creating an aggregate table that summarizes monthly sales figures by product category to speed up performance analysis. Proper understanding and application of this technique can reduce the load on the database and improve response times to user queries.Example: Creating an aggregated table in LookML
| aggregate_table: monthly_sales { query: { dimensions: [month, product_category] measures: [total_sales] } sql_table_name: my_agg_table connection: my_connection } |
API and Embedding
The Looker API
The Looker API provides a versatile interface for automating processes and integrating Looker with other systems. The API can be used to retrieve data, create and manage dashboards, and perform custom analytics. This expands Looker’s functionality and enables seamless integration into existing IT landscapes. Developers can use the API to build custom applications that directly access Looker data or to automate recurring tasks such as updating reports. An example would be using the API to automatically import and analyze sales data from a CRM system into Looker.
Looker offers complete coverage via API and therefore every function can be controlled and accessed via it.
Application embedding
Embedding Looker dashboards into other applications is a powerful feature that allows data analytics to be integrated directly into the workflow. Application Embedding allows users to incorporate Looker data visualizations into their daily tools and processes, significantly improving data accessibility and usability. An example would be embedding a sales dashboard into a CRM system to provide real-time insights to sales teams. This promotes data-driven decision making and makes it easier to access relevant information without users having to switch between different applications.Example: Embedding a dashboard
| <iframe src=”https://my.looker.instance.com/embed/dashboards/1?embed_domain=https://my.website.com” width=”100%” height=”600px” frameborder=”0″> </iframe> |
Embed design options
Embedding Looker content offers a variety of design options that enable customized and interactive data visualizations to be directly integrated into applications. These embeds can be customized to seamlessly fit the existing design of the application and ensure a consistent user experience.
Looker dashboards and reports can be embedded to appear native to the application. The layout, colors and fonts can be adapted to the design of the application. Developers have the freedom to design the user interface and add interactive elements that allow users to gain deeper insights into the data.
Looker as an interface between application and raw data – the semantic layer
The “semantic layer” in Looker bridges the gap between raw data and user-friendly analytics. It makes it possible to build complex data models that reflect specific business requirements while ensuring a unified and consistent view of the data. In addition, complete API coverage and the option for embedding clearly sets Looker apart from a pure analysis and BI tool and qualifies it as an excellent data management tool.
Conclusion
This article showed you how to leverage the semantic layer, API integration, and advanced embedding techniques to optimize your data analysis and representations.
