Why Looker Warns When a Dimension References a Measure and How to Fix It


(Zachary Michel) #1

The 411

You may have noticed at times that you’ve defined a dimension but it has turned up as a measure when you explore. The reason for this is because Looker is preventing you from generating reports with broken SQL. Prior to 3.54 Looker did this automagically and did not let you know why.

As of 3.54+ Looker will begin to warn when your dimensions should actually be measures.

Why does this happen?

In Looker there are two main field types, dimensions and measures. The main difference between the two fields is how they affect Looker’s generated SQL: a measure is an aggregation and in SQL aggregates are not allowed in the GROUP BY.

A dimension will ALWAYS be added to the GROUP BY. If I select the following dimension in my Explore/Look,

Old LookML ``` - dimension: name type: string sql: ${TABLE}.name
</details>

<details>
<summary>New LookML</summary>

dimension: name {
type: string
sql: ${TABLE}.name ;;
}

</details>


Then the SQL will add two parts: the `SELECT` for the dimension and the relevant `GROUP BY`:

SELECT
some_table.name as “some_view.name” – This is the name dimension
FROM some_table
GROUP BY 1 – In this case a single dimension is in the Group By, because we’ve only selected one


If a `dimension` is added to the `GROUP BY`, then the only option for a measure to be different is for it to **not be added to the `GROUP BY`**. If I use the same query from above and add the following measure,
<details open>
<summary>Old LookML</summary>
  • measure: total_value
    type: sum
    sql: ${TABLE}.value
</details>

<details>
<summary>New LookML</summary>

measure: total_value {
type: sum
sql: ${TABLE}.value ;;
}

</details>


the SQL produced is altered by adding the aggregation, but not adjusting the `GROUP BY`:

SELECT
some_table.name as “some_view.name”, – This is the name dimension
SUM(some_table.value) as “some_table.total_value” – This is my total_value measure
FROM some_table
GROUP BY 1 – In this case a single dimension is in the Group By, because we’ve only selected one


Notice how the only change is the additional statement in the `SELECT` clause. Okay, so what if I have an aggregate, but I want it to group? I should just make it a dimension, right?
<details open>
<summary>Old LookML</summary>
  • dimension: my_sum_dim
    type: number
    sql: ${total_value}
</details>

<details>
<summary>New LookML</summary>

dimension: my_sum_dim {
type: number
sql: ${total_value} ;;
}

</details>


**WRONG**. *If* Looker allowed this, then this would produce broken SQL.

SELECT
SUM(some_table.value) as “some_table.my_sum_dim”
FROM some_table
GROUP BY 1


Thus, when Looker notices there is an aggregate referenced in a dimension it **automagically forces it to be a measure**.

### What if I want my measure to perform a GROUP BY?

>It is a rule of SQL that you may never have an aggregate in the GROUP BY clause.

The only way to get your `measure` (in SQL, the term is an `aggregate`) to be allowed in a `GROUP BY` statement is by pre-aggregating them. This is either done in a [derived_table](), which you create in LookML and is composed of custom SQL written by you, or in a sub-select. 

Best practice is to use a `derived_table` because in most cases the operation of pre-creating a table and joining in the results is more optimal than performing a sub-select, which computes on each row.

Let's use my example above of my measure `total_sum`. Right now we have the SQL from the Looker generated query which we will use to help us write the `derived_table` SQL:

SELECT
SUM(some_table.value) as “some_table.total_value”
FROM some_table


Before we write any SQL in a `derived_table` we want to think about what we'll need in this table.

1. We'll need all the aggregates we want to pre-calculate
2. We'll need a key that will allow us to join the values back in to our explore

In this case I'm going to assume that `some_table` has a column named `some_table.company_name`, which I'm going to use as my link back to the explore. Now I want to pre-aggregate my `total_value` so I can group by it. My `derived_table` view file would then look like

<details open>
<summary>Old LookML</summary>
  • view: some_table_facts
    derived_table:
    sql: |
    SELECT
    some_table.company_name as company_name
    , SUM(some_table.value) as total_value
    FROM
    some_table
    GROUP BY 1

    fields:

    • dimension: company_name
      type: string
      sql: ${TABLE}.company_name

    • dimension: total_value
      type: number
      sql: ${TABLE}.total_value

</details>

<details>
<summary>New LookML</summary>

view: some_table_facts {
derived_table: {
sql: SELECT
some_table.company_name as company_name
, SUM(some_table.value) as total_value
FROM
some_table
GROUP BY 1
;;
}
dimension: company_name {
type: string
sql: ${TABLE}.company_name ;;
}

dimension: total_value {
type: number
sql: ${TABLE}.total_value ;;
}
}

</details>


Now that I have this facts table I'm going to join it to my explore to expose the dimensional representation of `total_value` in my explore.
<details open>
<summary>Old LookML</summary>
  • explore: some_table
    joins:
    • join: some_table_facts
      type: left_join
      sql_on: ${some_table_facts.company_name} = ${some_table.company_name}
</details>

<details>
<summary>New LookML</summary>

explore: some_table {
join: some_table_facts {
type: left_join
sql_on: ${some_table_facts.company_name} = ${some_table.company_name} ;;
}
}

</details>


### Some quick notes

- Writing the `derived_table` is always a thought process. Don't just copy in the Looker generated SQL in to a `derived_table` and think you've won. You need to think about your data and what makes the most sense when pre-aggregating. 

- If you have problems deciphering the best way to write your `derived_table`, or simply don't have the most experience with SQL that is not a problem. We have plenty of services at Looker to help you out! Send an email to support@looker.com with a detailed description of what you're trying to do and our awesome Department of Customer Love will get you moving in the right direction.

- Our online chat team is also great to speak to when you're lost on whether or not this is necessary. The team is amazing at speaking about SQL and how it works. Keep in mind that they are not able to write your SQL for you, as writing correct SQL requires a deep understanding of the data behind it.

Looker 5.8 Extended Support Release Notes
Looker 5.4 Release Notes
Looker 3.54 Release Notes
Legacy Features End-of-life Schedule
(Levi Davis) #2

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.