Introduction

As businesses increasingly rely on data for decision-making, the efficiency of business intelligence tools like Looker has become a priority. A slow-performing dashboard can frustrate users, hinder decision-making, and negatively impact productivity. Optimizing Looker dashboards for performance ensures users get actionable insights faster, leading to better outcomes.

This guide dives into actionable tips and best practices to boost the performance of Looker dashboards, covering everything from query optimization to leveraging backend improvements.

Understanding Dashboard Performance

Before diving into optimization, it's essential to understand the factors that affect Looker dashboard performance. Here are the primary metrics and factors to evaluate:

Key Metrics

• Dashboard Load Time: The time it takes for a dashboard to render and display data.
• Query Execution Time: The duration taken by the database to process and return data for a query.

Factors Affecting Performance

1. Data Volume: Large datasets with millions of rows can slow down performance.
2. Dashboard Design: Overly complex dashboards with too many visualizations and filters can hinder responsiveness.
3. LookML and Queries: Inefficient LookML models and poorly written queries are common culprits.
4. Database Performance: Suboptimal database configurations or limitations can slow down queries.

Best Practices for Optimizing Looker Dashboards

1. Efficient Query Design

Optimizing queries can significantly reduce dashboard load times. Here’s how:

Use Aggregate Tables

Instead of querying raw data repeatedly, pre-aggregate it in the database.
SQL Example:


CREATE TABLE aggregated_sales AS
SELECT 
    date,
    SUM(total_sales) AS total_sales,
    COUNT(order_id) AS total_orders
FROM sales
GROUP BY date;

**Avoid SELECT ***

Always select only the required fields to reduce data volume.
Inefficient Query:

SELECT * FROM sales;

Optimized Query:

SELECT order_id, total_sales, customer_id FROM sales;

Leverage Indexing

Ensure commonly filtered fields, like order_date or customer_id, are indexed in the database.
Index Example:

CREATE INDEX idx_order_date ON sales(order_date);

Automate Query Diagnostics
Set up scripts to identify slow queries automatically.
Python Example Using Looker SDK:

import looker_sdk

sdk = looker_sdk.init31()  # Initialize SDK
slow_queries = sdk.run_inline_query(
    result_format="json",
    body={
        "model": "system__activity",
        "view": "history",
        "fields": ["query.id", "query.runtime"],
        "filters": {"query.runtime": ">5000"}
    }
)
print(slow_queries)

2. Streamline LookML Models

LookML serves as the foundation of Looker dashboards. Keep it clean and efficient:

Avoid Redundant Joins

Excessive joins slow down query execution. Only include necessary joins in the Explore. If possible add these joins into your source data tables.
LookML Example:

explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.id} ;;
  }
  join: products {
    sql_on: ${orders.product_id} = ${products.id} ;; # Only add if necessary. If possible add the desired products fields to your orders table within your database.
  }
}

Use Persistent Derived Tables (PDTs)

PDTs store results for complex calculations, reducing query execution time.
LookML Example:

derived_table: {
  sql: SELECT customer_id, SUM(order_total) AS total_spent
       FROM orders
       GROUP BY customer_id ;;
  persist_for: "24 hours"
}

Enable Caching

Turn on caching in Looker to store query results and reduce redundant processing.
Go to Admin > Caching and configure cache settings based on your use case.

3. Simplify Dashboard Design

Overloading dashboards with complex visualizations or filters can overwhelm the system. Simplify with these tips:

Limit Visualizations

Reduce the number of tiles on your dashboard. For instance:

• Combine multiple charts into a single multi-series chart where possible.

Use Pre-Filters

Instead of allowing users to filter on every possible field, create pre-filtered dashboards tailored for specific user roles.
Example Pre-Filter in LookML:

explore: sales {
  sql_always_where: ${sales.region} = "North America" ;;
}

Pre-Sort Data

Sort data at the database or LookML level to avoid on-the-fly sorting in the visualization layer.
LookML Example:

dimension: total_sales {
  sql: SUM(${TABLE}.sales) ;;
  sort: desc
}

4. Optimize Backend and Database Performance

Choose High-Performance Databases

Use databases like BigQuery, Snowflake, or Redshift for faster query execution. These databases are designed for large-scale analytics.

Partition Large Tables

Partition tables to speed up queries on specific ranges of data.
Partitioning Example:

CREATE TABLE partitioned_sales (
    order_id INT,
    order_date DATE,
    total_sales DECIMAL
) PARTITION BY RANGE (order_date) (
    PARTITION p1 VALUES LESS THAN ('2023-01-01'),
    PARTITION p2 VALUES LESS THAN ('2024-01-01')
);

Scale Databases for Concurrency

Ensure your database is configured to handle multiple simultaneous Looker queries by scaling up or adding compute resources.

5. Utilize Looker Performance Tools

System Activity Dashboards

Monitor performance with Looker’s built-in System Activity dashboards. For example:

• Query History Dashboard: Identifies long-running queries.
• Performance Dashboard: Shows cache usage and dashboard load times.

Query Logs

Export and analyze query logs to identify and troubleshoot bottlenecks.
Query Logs SQL Example:

SELECT 
    query_id, 
    execution_time, 
    database 
FROM looker_query_logs
WHERE execution_time > 5000; -- Queries taking longer than 5 seconds

Set Alerts for Anomalies

Create alerts for key metrics like load times or execution times exceeding thresholds.

Real-World Examples of Optimization

Example 1: Reducing Load Time with Optimized Joins

A retail company noticed that its sales dashboard took over 30 seconds to load. By auditing LookML, redundant joins were removed, and persistent derived tables were implemented, reducing load time to under 10 seconds.

Example 2: Improving Performance with Aggregate Tables

An e-commerce company struggled with performance on a dashboard showing daily sales trends. By creating an aggregate table in BigQuery for daily sales summaries, the dashboard load time improved by 50%.

Example 3: Leveraging Caching for Repeated Queries

A financial firm’s users frequently accessed similar dashboards throughout the day. By enabling Looker’s caching system, they reduced repetitive query execution, cutting load times in half.

Conclusion

Optimizing Looker dashboards not only improves user satisfaction but also enhances productivity by delivering insights faster. By applying the best practices outlined in this guide—ranging from query and LookML optimization to backend enhancements—you can ensure your dashboards perform at their peak.

For more expert tips or personalized assistance with Looker, feel free to reach out! Contact Us for Looker BI Optimization Services.