[RETIRED] Experimental Feature: Setting Up Custom Fields

(hilary) #1

The content of this article has been moved to Looker’s technical documentation here.

YTD LY comparison
Looker 5.14 Release Notes
Looker 5.14 Release Notes
Looker Next: Experimental Custom Fields
Looker 5.14 Extended Support Release Notes
(Timothy Burke) #2

Great work team! Thanks so much for this feature, super useful, and it’s great to get these Beta / Experimental features as they’re developed.

Some feedback about using groups:
IMO altering the naming convention might be useful. It’s nice to have granular control about which users see which features, but I’m going to try most of them, and from an admin perspective I imagine myself 6 months from now having 5 or 6 different named expirimental/beta groups. Instead of “Merged Results Beta Users” and “Custom Fields Beta Users” if the naming convention was flipped to “Beta Users - Merged Results” and “Beta Users - Custom Fields”, then I could sort my groups alphabetically and those would all get sorted together.

Feedback on this specific Custom Fields feature:
I would love if we could write sql more directly, I think that would be (1) both easier to author and also (2) more powerful (edited to describe in more detail).

On (1), I’ll just say that at least for me, switching back and forth between these different languages causes me some pause, I have to switch gears in my head -

"SQL Syntax" case when boolean1 then 'a' when boolean2 then 'b' else null end
"Looker Expressions" if(boolean1,'a',if(boolean2,'b',null))

On (2), I believe this feature as-is allows me to create a filtered sum, such as:

sum(case when attribute1='a' then field1 else 0 end) as filteredSum1

but if I wanted to represent something more arbitrary / complicated, like:

 when attribute1='a' then field1
 when attribute1='b' then field2
 else 0 end) as customField

I can’t do that using this feature as-is, right?

Understood that the SQL syntax is database specific, which has some downsides, but for prototyping and answering adhoc questions, this would be super useful, similar to how SQL can be defined directly when defining measures in LookML.

(Desiree) #3

Hi Timothy,

Thanks so much for the feedback! I am passing your praise and feedback along to our product team. As for your question in (2) it’s not currently possible to do that, but I will pass this along to the team as well.


(Kyle) #4

Absolutely love this feature!

Once thing I have noticed is that if you build a custom measure and then hide it it will go away.

I have some use cases where I want to hide a custom measure and use it for a table calculation.


I’m with @TimothyBurke on this! Love this idea, but would love it even more if it was SQL syntax. In my experience, the people that would want to use this feature are likely to know basic SQL anyway, so would prefer that over learning how to use Looker Expressions.

At the moment, I’m a bottleneck because any new dimensions/measures our Looker Users want require editing the underlying LookML, and I’m hesitant to give them access since they can do a lot more than create new fields once they get that access.

Our solution so far has been to allow them to make pull requests but not merge, but it means that I (or a teammate) still have to review the pull request, which still adds to our workload, and slows down how quickly our Looker Users can work. Custom Fields could change this.

I’d love to start using this feature, but not using SQL syntax is a bit of a dealbreaker right now. I find the Looker Expressions syntax hard to grok (I’m almost always having to refer to the documentation), and agree that there are some things you just can’t express using Looker Expressions!

(Desiree) #6

Thanks so much for the feedback, Kyle! We are glad to hear that you love this feature. I will let the product team know about your use case request.

(Desiree) #7

Thanks for the feedback, Crlclr! I have informed the product team about your concerns— we are always happy to help with specific questions or workflows. Please feel free to email support@looker.com or hop on chat if you have any more!


Thank you for this feature! When implemented would like to have Custom Fields as a separate permission from Table Calculations.

(Emma Ware) #9

Hey @praab,

I’ll make sure the Product team knows you’d like to have separate permissions for Custom Fields and Table Calculations.

All the best,

(Robert Ellman) #10

Just wanted to add another piece of feedback that may contradict some of the “Use straight SQL”.
A couple of things I’d like to see after using this feature for a couple of weeks now:

  • An administrators view of Custom Fields that have been created, regardless of which explore they are tied to. I’m thinking something like the Content Validator. Although right now the custom fields do show up as “Errors” in the Content Validator, they’re not really errors, and you can’t see the definition of the custom field.
  • A promotion mechanism to take the custom field and apply it to the appropriate LookML, one thing that we’ve used the feature for is to experiment with potential new fields without having to go through our process of modifying LookML in our staging instance, code reviews, merging to production, etc. Even if this process just provided the LookML snippet to add, it would be more valuable than having
    to recreate the same logic again as LookML in order to make the “Custom” field an “Official” field.

(molly.lippsett) #11

Hi @Robert_Ellman1

Thank you for testing out this new feature and providing this excellent feedback! I’ve passed it along to our product team.


(Wil Gieseler) #12

@TimothyBurke @clrcrl Thanks for the feedback! I’d love to dig in a little more about where you find the differences between Looker Expressions and SQL are getting in your way. Is it primarily the lack of a CASE statement like SQL?

Our current point of view on the expression syntax is to make it similar to SQL for familiarity, but simplify it wherever possible to make it easier for people who don’t know SQL to learn. We also want something that works the same way across all SQL dialects, so users don’t have to understand specific quirks of a given SQL dialect to get stuff done.

But we’d love to know what’s tripping you up so we can make it even better!

(Timothy Burke) #13

My bigger argument here isn’t just that straight SQL is easier to write than Looker Expressions (I do believe that is the case, simply for familiarity reasons with the syntax, but I see the pros and cons of abstracting it) – my bigger argument is that as this feature exists now, you are limited to only:

  1. Filter an existing measure defined in LookML model
    i.e. sum(case when Dimension1=1 then Measure1 else null end)
  2. Create a measure from a dimension defined in LookML model
    i.e. sum(Dimension1)
    But you can’t do this:
  3. Create a new arbitrary measure, leveraging existing dimensions and measures from your LookML model
    i.e. sum(case when Dimension1=1 then Measure1 when Dimension2=2 then Measure2 else null end)
    or even simpler, sum(Measure1 + Measure2)

A custom measure like (3.) is often preferable to a table calculation with the same definition, because the table calculations won’t populate in row totals, column totals, etc. with a single definition.

That type of measure isn’t worth defining in the model if you are just trying to do quick throwaway math.

The same logic could possibly be done with Looker Expressions, but defining straight SQL would be one way to allow that type of custom field definition.

(Wil Gieseler) #14

Ah, interesting, thanks for the reply!

Regarding this example you mention:

 when attribute1='a' then field1
 when attribute1='b' then field2
 else 0 end)

This is possible right now, but a bit more indirectly. You can make a custom dimension for the “inside” logic (though you’ll have to use our if function rather than the case syntax, which I agree would be better). You can then make a custom measure based on that custom dimension to perform the sum aggregation. Two steps, but doable.

I’m not quite sure what you’re asking for in your more recent #3 example above because of the references to measures. I’m under the impression that in SQL aggregate functions (i.e. measures) cannot be nested like sum(Measure1 + Measure2).

In SQL you can of course do math between aggregates as long as they’re not inside an aggregate like measure1 + measure2. Custom Fields do not currently support this but it’s a natural next step that we are investigating.

Also, I realized that above I didn’t mention the most important reason we don’t use SQL directly: security. It’s simply not safe to offer direct SQL writing without some kind of security model, which having our own expression language gives us nearly “for free” (along with the other benefits already mentioned).

(fabio) #15

Also, I realized that above I didn’t mention the most important reason we don’t use SQL directly: security. It’s simply not safe to offer direct SQL writing without some kind of security model

A million times this^

I was reading down the thread and growing increasingly anxious that this was yet to be brought up :slight_smile:

(Timothy Burke) #16

I guess I was taking liberties in my examples, kind of mixing up “Measures” which are Looker-defined, and raw database object fields accessible directly via SQL, I was rushing for an example but shouldn’t mix those up.

Interesting that you can make a custom measure on top of a custom dimension! Thanks for mentioning that, it’s not something I would have tried, and that actually does unlock some potential use cases. In my testing, along the lines of your suggestion, I found that you can create a custom dimension that depends on fields from multiple views, but then you can’t create a custom measure that depends on that dimension, so the two-step approach you described will work in some situations, but there are limits.

I hear the security concern. At this point since it’s in Beta with explicitly granted usage, we have it configured so the same people who can use this feature are also Looker model developers and our database is fully accessible to those users, although I’m sure many different Looker clients have different setups and needs. Because the custom fields can still be viewed by other Looker users even if they can’t create their own custom fields, it can be used almost as an extension of model development, but much faster (and disposable, per explore), so they’re great for adhoc analysis.

(Wil Gieseler) #17

Yep, a major part of the idea is making custom fields easy to use for ad-hoc analysis which can potentially reduce the pressure to put every single derived field in the model.

I should clarify for the benefit of others that custom fields have a robust security approach which makes them totally safe to use for anyone, not just developers or those with model access. Because you can only create custom fields from existing fields you already have access to, there’s no way to access data outside of your existing permissions. That means custom fields are a safe, flexible analytical tool appropriate for every Looker user.

(Timothy Burke) #18

I’m still finding myself using this feature quite a bit, but another limitation I’ve run into is that date fields aren’t available.

It’s common to have existing dimensions / dimension groups for dates i.e. the sql field purchase can be referenced elsewhere in LookML as purchase_raw, purchase_year, purchase_month, purchase date. If you have some arbitrary date XYZ where something changed (a feature was rolled out, a competitor moved in, etc.) and you want to group purchases into two groups before XYZ and on/after XYZ this would be really powerful to create those groupings on the fly. It seems like none of the associated fields (not purchase_raw, not purchase_date) are available to use when defining a custom dimension.

(Jesus Cruz) #19

@TimothyBurke thank you for providing the use cases where you’d apply the date field in a custom dimension. I’m sharing that feedback directly with our Product Team.

Jesus Cruz
Looker Support

(Kyle) #20

Hi @TimothyBurke,

I have been able to use the date functions even though they don’t pop up and it shows an error I think you can use it.

You just have to follow the syntax that would show up in table calcs, ${view_name.date_field}. I should note that I am using Redshift and it may not work on other query engines.