Understanding and Creating Explores

Explores in Looker are central to creating a powerful and flexible self-service analytics experience. They define the starting point for an analysis and enable users to dive into the data. Explores provide a way to define the scope of data to examine, and they establish the fields available for selection in a query. This article will dive into the concept of Explores in Looker, their structure, and how to create them.

Understanding Explores

Explores are a LookML concept. In simple terms, an Explore can be seen as a prepared SQL query, where the data has been modeled using LookML, Looker's proprietary modeling language. Explores enable Looker users to navigate through data without writing SQL queries, but instead through point-and-click interactions in the Looker UI.

Creating Explores

To create an Explore, you define it within a model file in Looker. This is done using LookML code in a .model.lkml file. The following is an example of an Explore created within a model:

model: ecommerce {
  explore: orders {
    view_name: orders
    label: "Online Orders"
    description: "Exploration of all online orders"
  }
}

This simple example demonstrates the basic elements of an Explore:

• explore: orders - This line initiates an Explore based on the orders view. The name of the Explore is orders.
• view_name: orders - This line indicates the primary view (table) that the Explore will query.
• label: "Online Orders" - This label is the display name that Looker users see in the Explore UI.
• description: "Exploration of all online orders" - This is a longer description that appears in tooltips and other descriptive areas in the Looker UI.

Adding Joins to Explores

In the real world, data models are rarely as simple as one table. Often, you need to join multiple tables together to get the complete picture. Looker allows you to define joins within an Explore.

Here's an example of an Explore with a join:

model: ecommerce {
  explore: orders {
    view_name: orders
    label: "Online Orders"
    description: "Exploration of all online orders"
    
    join: customers {
      sql_on: ${orders.customer_id} = ${customers.id}
      relationship: many_to_one
    }
  }
}

This LookML code joins the orders table with the customers table on the customer_id field from orders and id field from customers. The relationship is defined as many_to_one, which means that for each record in orders, there is one or zero corresponding records in customers.

Creating Derived Tables

In some cases, you may want to create an Explore on a derived table rather than a base table in your database. A derived table is a table that is created by applying transformations to the base data. In Looker, you can define a derived table in LookML and then create an Explore on top of it.

Here's an example of an Explore with a derived table:

model: ecommerce {
  explore: monthly_orders {
    view_name: monthly_orders
    label: "Monthly Orders"
    description: "Exploration of orders aggregated by month"
    
    from: derived_table {
      sql: SELECT DATE_TRUNC('month', order_date) as month, COUNT(*) as order_count
            FROM orders
            GROUP BY month ;;
    }
  }
}

In this example, the derived table performs a SQL query that groups orders by month and counts the number of orders for each month. The Explore monthly_orders is then created on this derived table.

Conclusion

Explores in Looker provide the foundation for data exploration, visualization, and dashboarding. By understanding and properly defining Explores, you can build a robust self-service analytics environment that caters to diverse analytical needs. This article has only scratched the surface of what's possible with Explores. In the subsequent articles, we will delve deeper into advanced features such as parameterizing Explores, using liquid variables, and much more.