what is lookml explain with simple program

LookML is Looker's modeling language. It's not a general-purpose programming language like Python or Java; instead, it's a declarative language specifically designed to describe the dimensions, measures, and relationships within your data. Think of it as a way to create a semantic layer on top of your database, making it easier for users to explore and analyze data without needing to write complex SQL.

Here's a breakdown with a simple example:

Key Concepts in LookML

• Projects: A LookML project represents a collection of LookML files that define the data model for a specific dataset.
• Models: A model defines which database connections and sets of data (Explores) are available to users.
• Explores: An Explore is the starting point for user queries. It defines a set of joined tables that users can explore.
• Views: A view represents a single table or a derived table. It defines the dimensions and measures available within that table.
• Dimensions: Dimensions are attributes or categories of your data (e.g., date, product name, customer ID).
• Measures: Measures are quantitative values that can be aggregated (e.g., sum of sales, average order value, count of customers).

Simple Program Example

Let's imagine we have a simple database table called orders with the following columns:

• order_id (integer)
• customer_id (integer)
• order_date (date)
• total_amount (decimal)

Here's how we might define this in LookML:

LookML

# In a file named orders.view.lkml (inside a LookML project)

view: orders {

  sql_table_name: public.orders ;; # Replace with your actual table name

  dimension: order_id {

    primary_key: yes

    type: number

    sql: ${TABLE}.order_id ;;

  }

  dimension: customer_id {

    type: number

    sql: ${TABLE}.customer_id ;;

  }

  dimension_group: order_date {

    type: time

    timeframes: [raw, date, week, month, year]

    sql: ${TABLE}.order_date ;;

  }

  measure: total_sales {

    type: sum

    sql: ${TABLE}.total_amount ;;

    value_format_name: usd

  }

  measure: average_order_value {

    type: average

    sql: ${TABLE}.total_amount ;;

    value_format_name: usd

  }

  measure: order_count {

    type: count

    drill_fields: [order_id] # Allows users to drill down to individual orders

  }

}

# In a file named my_model.model.lkml (inside a LookML project)

connection: "my_database_connection" { # Replace with your connection name

  dialect: "postgres" # Replace with your database dialect

  # ... other connection details

}

model: my_model {

  connection: "my_database_connection"

  explore: orders {

    from: orders

  }

}

Explanation:

• view: orders: This defines a view named "orders" that corresponds to our database table.
• sql_table_name: Specifies the actual name of the table in the database.
• dimension: order_id: Defines a dimension for the order ID. primary_key: yes indicates that this is the primary key.
• dimension: customer_id: Defines a dimension for the customer ID.
• dimension_group: order_date: Defines a dimension group for the order date, automatically creating dimensions for different timeframes (date, week, month, year).
• measure: total_sales: Defines a measure that calculates the sum of the total_amount column. value_format_name: usd formats the output as US dollars.
• measure: average_order_value: Calculates the average order value.
• measure: order_count: Counts the number of orders. drill_fields allows users to click on the count and see the individual order IDs.
• connection (in the model file): Defines the database connection.
• model: my_model: Defines the model and specifies the connection and available Explores.
• explore: orders: Defines an Explore named "orders" based on the "orders" view.

How it Works in Looker

When a user uses Looker to explore data using this LookML, Looker translates the user's selections into SQL queries based on the definitions in the LookML. For example, if a user wants to see the total sales by month, Looker would generate a SQL query similar to this (simplified):

SQL

SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(total_amount) AS total_sales FROM public.orders GROUP BY 1 ORDER BY 1

Key Takeaways

• LookML is declarative, not procedural. You define what data is available and how it's related, not how to retrieve it.
• LookML creates a consistent and reusable data model.
• LookML abstracts away the complexity of writing SQL for end-users.

This simple example provides a basic understanding of LookML. In real-world scenarios, LookML models can become much more complex, involving multiple views, joins, derived tables, and more advanced features. However, the core concepts of dimensions, measures, views, and explores remain the same.