How do I remove the GROUP BY clause for an explore/view with non-aggregate measures?

[Steve_Guidi]

I have a pre-computed metric table that has a small set of dimensions and non-aggregate measures.  How do I model this table in Looker so that a GROUP BY clause isn’t appended to the report query?

To give a contrived example of the use case, consider a schema that tracks the unique number of application users per application module.

view: module_unique_users {

  dimension: module_id {
    type: number
  }

  measure: unique_users {
    type: number
  }
}

Note that we can’t logically aggregate unique_users in any way, since the same user may use several different modules.

When I try to use these fields in a report, Looker generates a SELECT statement that doesn’t include an aggregation function for unique_users (which is expected).  However, a GROUP BY clause is included even though unique_users is non-aggregable by its type (number), resulting in a SQL syntax error. 

SELECT module_id, unique_users
FROM module_unique_users
GROUP BY 1
ORDER BY 2 DESC

Use of cancel_grouping_fields: [module_unique_users.unique_users] in the explore won’t work since the directive is ignored for measures.

I’m basically trying to list the contents of this view without any aggregation, though still allow joins and other compatible aspects of the explore.  Is this possible?  Do I need to make unique_users a dimension even though it really isn’t?  It would be nice to keep the colorized distinction in the UI for this view.

[IanT · Accepted solution]

Summing (or max/min/avg etc) a single value is the same as just displaying that single value and grouping on a value which only appears once (module id) is no problem either.

Looker will always group by dimensions and want to aggregate measures.

If you are worried people will put UU into an analysis without moduleid (summing all the UU across modules) then you can make moduleid a required field for the UU measure.

You could just make UU a dimension if you so wish but it wont behave like a measure in charting which will cause you issues as well as the colouring of the field as you say.

[Steve_Guidi]

If you are worried people will put UU into an analysis without moduleid (summing all the UU across modules) then you can make moduleid a required field for the UU measure.

This is a great suggestion -- thank you!  For more complex unique-user calculations, I can use required fields for the entire dimension set that unique-users is calculated with.

[IanT]

Have a little play with it, I have a feeling it puts it into the underlying query however doesnt force it into the analysis...meaning the you will get a list of UU with no corresponding module IDs alongside it.

[Steve_Guidi]

Yes, that’s true.  The required-field is not displayed, but that should be o.k. for my use case.  I’m trying to ensure that someone doesn’t try to aggregate unique-user measures, and this will do it.  The rendered report loses meaning without the module-id dimension, so it will generally be included by the user.