Order_by_field can only be used with dimensions


(Paola) #1

As of 3.54, Looker will produce a warning when order_by_field is declared on a non-dimension field or references a measure.

##When can I use the order_by_field parameter?
The order_by_field parameter allows one dimension to be the sort order for another dimension. If you use the order_by_field in a measure such as,

Old LookML ``` - dimension: id type: int primary_key: true sql: ${TABLE}.id
  • measure: total_funding
    type: sum
    sql: ${raised_amount}
    order_by_field: id
</details>

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

dimension: id {
type: int
primary_key: yes
sql: ${TABLE}.id ;;
}

measure: total_funding {
type: sum
sql: ${raised_amount} ;;
order_by_field:“id”
}

</details>

Looker will give the following warning:

<img src="/uploads/default/original/2X/e/e4f1502ac562c96e8e63e63f9d7c9813d4c6ac1a.png" width="320" height="86">

Looker will also give a warning if the `order_by_field` references a measure. For example:

<details open>
<summary>Old LookML</summary>
  • measure: average_funding
    type: avg
    sql: ${raised_amount}

  • dimension: round_code
    sql: ${TABLE}.round_code
    order_by_field: average_funding

</details>

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

measure: average_funding {
type: average
sql: ${raised_amount} ;;
}

dimension: round_code {
sql: ${TABLE}.round_code ;;
order_by_field: average_funding
}

</details>

Looker will give the following warning:

<img src="/uploads/default/original/2X/c/cc7806c07525610447887f29053746f40545cb43.png" width="324" height="103">

##How do I fix this?
If you want to use the `order_by_field` parameter, make sure you are only working with dimensions. More examples can be found in this discourse article: [order_by_field (3.14+)] (https://discourse.looker.com/t/order-by-field-3-14/361)

Looker 3.54 Release Notes
(Michael Erasmus) #2

I’m curious why this warning is in place? I have a Look working with a measure as my order_by_field and it’s doing exactly what I want, except I can’t commit on our Looker instance with LookML warnings.

Let me explain a little more what I’m trying to do. I want to be able to sort a view randomly if you sort on the id dimension. I know I can add a random dimension like discussed here Random Samples of Data in the Explore

But if I use a dimension, that messes up any measures I have in my look (since it’s also groups on that dimension, so if you have say a count in there, you’ll only have count’s of 1). So I can use a measure instead:

Old LookML ``` - measure: random_sequence_number type: number sql: max(random())
</details>

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

measure: random_sequence_number {
type: number
sql: max(random()) ;;
}

</details>

This doesn't mess up my measures and I can sort randomly on that measure. However, I would prefer not to have to show a strange random number field in my Look. So I try this:

<details open>
<summary>Old LookML</summary>
  • dimension: id
    primary_key: true
    order_by_field: random_sequence_number
    sql: ${TABLE}.id
</details>

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

dimension: id {
primary_key: yes
order_by_field: random_sequence_number
sql: ${TABLE}.id ;;
}

</details>

So this works great. I can sort on the `id` dimension and get a random sample. The look runs fine with no SQL errors and does exactly what I expect.

Except then I get the warning:

https://www.dropbox.com/s/ni62ntf1q5z4bdr/Screenshot%202016-10-29%2015.21.41.png?dl=0

Anyway I could override the warning in this one place?

(lloyd tabb) #3

We put the warning in because people were making measures that were ordered_by dimensions and dimensions ordered by measures it got very confusing and hard to debug (Dimensions forced unwanted grouping)

You make an interesting case for measures ordered by other measures. We hadn’t seen a use case here, but I don’t see an obvious problem with it. Adding @conrad


(conrad) #4

There is another reason for disallowing order_by_field on a dimension referencing a measure. Dimension’s order_by_field referencing a measure is ambiguous when the query contains pivots. This would not be a problem for measure referencing another measure in order_by_field, and we should look into allowing that. However, it looks like your use case actually has the problematic combination of dimension and measure. I’ll socialize this with the product team and see if we can come up with a sensible solution.


(Michael Erasmus) #5

Ah ok, that makes sense @conrad. I can live with having it work for measures referencing other measures as well and can see that I could make something like that work in my situation as well (I also have a measure in my look that I can sort on)


(conrad) #6

I think that measure ordered by other measure is completely doable. I’ll file a feature request and hopefully can implement in this or next release.


(Levi Davis) #7

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


(conrad) #8

@Michael_Erasmus we’ve implemented order_by_field being allowed on a measure when it references another measure. This functionality will be available in the next release, 4.4.


(Michael Erasmus) #9

That’s awesome, thanks for the update @conrad. Eager to give it a try.