Looker allows you to create robust, flexible data models that empower users to explore data in a self-service manner. The bedrock of Looker's capabilities is LookML, Looker's easy-to-learn data modeling language. However, knowing LookML syntax is only the first step. Equally important is designing your LookML models in a way that optimizes for performance, maintainability, and user-friendliness. This article will discuss some best practices and guidelines for designing data models in Looker.
1. Define Clear Objectives for Your Model
Before you start writing LookML code, it's crucial to define clear objectives for your model. What are the key metrics or KPIs you want to expose? Who will be using the model, and what questions will they want to answer? This will help guide your data modeling decisions and ensure your model delivers value to your users.
2. Choose the Right Grain
The grain of your model (the level at which your data is summarized) plays a critical role in its utility and performance. The grain you choose should align with the primary use case for your model. For example, if your users primarily need to analyze daily sales, it might make sense to aggregate your data at the day level.
3. Keep It Simple and Intuitive
An intuitive data model encourages user adoption. Stick to simple, recognizable names for your views, dimensions, and measures. Use descriptive labels and descriptions. Organize related fields into view groups. Try to anticipate the user's perspective and design your model accordingly.
view: order_items {
dimension_group: created {
type: time
timeframes: [date, week, month, quarter, year]
sql: ${TABLE}.created_at ;;
}
dimension: product_id {
type: number
sql: ${TABLE}.product_id ;;
}
measure: total_sales {
type: sum
sql: ${TABLE}.amount ;;
drill_fields: [detail*]
}
}
4. Leverage Looker Functions for Calculations
Looker offers a variety of functions that can simplify your LookML code and enhance the performance of your models. Whenever possible, use Looker functions like yesno, percent_of_total, running_total, rank, etc.
measure: percentage_of_total_sales {
type: percent_of_total
sql: ${total_sales} ;;
}
5. Optimize for Performance
Efficiency is crucial in a data model. Some common strategies include limiting the use of derived tables, indexing fields used in joins and filters, and using datagroup triggers to manage caching. Furthermore, consider using persistent derived tables for complex computations that don't need real-time data.
6. Make Good Use of Explores
Explores are the user-facing portion of your LookML model. Carefully plan which views to expose in each Explore to avoid overwhelming users with too many fields. Use hidden: yes and access_grant to manage field visibility.
explore: orders {
join: customers {
sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
relationship: many_to_one
}
join: products {
sql_on: ${orders.product_id} = ${products.product_id} ;;
relationship: many_to_one
}
}
7. Ensure Flexibility and Scalability
Your LookML model should be flexible enough to evolve with your business and scalable enough to handle increasing volumes of data. Keep your code modular and maintainable. Consider using extends to share common fields across views, and include to split your model into multiple files.
8. Use Version Control
Lastly, version control is critical for collaboration and preventing mistakes. Looker integrates with Git, allowing you to track changes, create branches, and revert to previous versions of your LookML code.
By following these best practices, you can design LookML models that provide maximum value to your users while ensuring maintainability and performance. Remember that data modeling is an iterative process, and your LookML models should continually evolve to meet your users' changing needs and questions.