Dimensionalize always at one dimension and conditional on the inclusion of a second dimension in the query

Ask the Community
1

I’m wondering if there’s a way to dimensionalize an AQL field so that its:

  • Always at the granularity of dimension A
  • Only at the granularity of dimensions B or C if they are included in the final table query (similar to keep_grains)

I’ve been playing around with a few different things and can’t seem to get it to work. I know it might not even be possible so I wanted to check in before I spend a lot of time on this. Would love any ideas if there’s a solution to this, even if it’s a bit complex to make work!

2

Hi @anya.conti,

Currently, this is not possible because dimensionalize does not support inheriting visualization grouping yet. I am not aware of any workarounds, as this would require major changes in how we handle dimensions (we assume dimensions are static and precomputed before any visualization code).

However, we are considering supporting this in the future. If you have a specific use case, please let us know.

It’s also possible that you may achieve your goal using keep_grains and nested aggregation.

3

Hmmm, makes sense. I’ll see what I can play around with, thanks for the help! Is keep_grains the ONLY way currently to make a field dependent on which fields are included in the query? Or is there any other way that I’m not thinking of right now?

For explanation of our use-case, we’ve got a metric that needs to be summed at a certain granularity and then have a ceiling and floor applied, but those ceiling and floors need to be different depending on the granularity that we’re analyzing. We currently do this in dbt, and then have 3 different Datasets built around granularity where essentially the only difference is the the granularity of that field (it’s an absolutely integral metric). We then use the resulting field as a dimension in holistics, but we might be able to get away with it as a measure. I’m hoping that if I can figure out how to automatically calculate the field based on granularity with the correct floor and ceiling also at the right granularity, then we could really consolidate our dbt models and our holistics datasets, and make things a lot easier for our internal users.

4

Hi Anya,

Is keep_grains the ONLY way currently to make a field dependent on which fields are included in the query?

Metrics group on all dimensions included in the query by default. keep_grains allows you to enforce that a metric is only used with selected dimensions, ignoring grouping on other dimensions. There is also of_all, which ignores grouping on the specified dimensions instead.

I assume you have already know about this but mentioned to be clear. Based on your use case, though, they may not be the most applicable options?

have a ceiling and floor applied, but those ceiling and floors need to be different depending on the granularity that we’re analyzing.

When you say ceiling and floor, do you mean restricting the granularity itself (for example, preventing analysis at certain grains), or applying a greatest / least calculation with different values?

Assuming it is the latter (based on your other question), are the values static for each selected grain, or are they metrics calculated at another granularity?

If the values are static, you could handle this with is_at_level, for example:

case(
  when: is_at_level(orders.created_at | year()),
  then: sql_number(
          'GREATEST',
          sql_number('LEAST', revenue, 50000),
          0
        ),

  when: is_at_level(orders.created_at | month()),
  then: sql_number(
          'GREATEST',
          sql_number('LEAST', revenue, 3000),
          10
        ),

  when: is_at_level(orders.created_at | day()),
  then: sql_number(
          'GREATEST',
          sql_number('LEAST', revenue, 2330),
          20
        ),

  else: 1
)

Swapping those static numbers to other metrics should also be straightforward, but it will depend on your use cases.

5

When you say ceiling and floor, do you mean restricting the granularity itself (for example, preventing analysis at certain grains), or applying a greatest / least calculation with different values?

Assuming it is the latter (based on your other question), are the values static for each selected grain, or are they metrics calculated at another granularity?

It would be applying greatest / least calculation. They would NOT be static values, but calculated at the granularity of the fields being included.

I hadn’t seen the is_at_level function before, this is cool! I’ll take a look at it, but not sure how easy it would be to make it work for this specific use case since I’m not using static values.

I know this is probably a weird use case, and might not be possible, but really appreciate the brainstorming!

6

not sure how easy it would be to make it work for this specific use case since I’m not using static values.

I know this is probably a weird use case, and might not be possible, but really appreciate the brainstorming!

Hi Anya,

We may be able to help more effectively with a few additional details about your use case. Feel free to send us more specific information through a support ticket, and we’ll be happy to take a closer look and assist further.