Nested aggregate and aggregate awareness: how to make it work?

symmetric
liquid
bigquery

(Rogier Werschkull) #1

Use case:

Coming from raw event data, we have created an aggregated table nested table in BigQuery where the grain of the aggregate is:
Day, Site, Device, Game, Country
On this level we have stored various aggregated measures that, like TimeOnpage_agg.

Now, on the nested details level we store the individual pageviews, that have a lot of additional dimensions, like:
Device name, Browser name, Visitor, Session etc… and of course also the detailed TimeOnpage measure

Now, I want to have all of this to be in one LookML model where:

I create a measure called TimeOnpage_optimized
…where I want :TimeOnpage_agg to selected (the aggregate level) when NOT using ANY dimension from the nested level.
Then, when ANY nested dimension is used, I want to (and should) use the nested TimeOnpage measure.

Now, the de-nesting on the fly seems to work, using an example like: Nested Data in BigQuery (Repeated Records)
With this I have created a LookML view that unnests dimensions / measures as runtime (called pageviews_optimized__SessionVisitorPageDetails)

However, making the optimized measure aggregate aware using:


…does not seem to work: Looker basically will always force the unnest & does its symmetric aggregate magic when I just want to switch the used column / calculation, depending on query columns being present:
So, this does not work:

measure: time_on_page_optimized {
group_label: “Time On Site/PageViews”
label: “Time on Site aggregate aware”
description: “Take this measure from the aggregated or nested level, depending on context”
type: sum
sql:
{% if pageviews_optimized__SessionVisitorPageDetails._in_query %}
${pageviews_optimized__SessionVisitorPageDetails.time_on_page}
{% else %}
${time_on_page_agg}
{% endif %}
;;
}

How can I make this happen?