[Retired] Using Native Derived Tables

(rufus) #21

@ajhong91, as per Lloyd’s comment above we can create an explore file in the same way we would create a new view/model file we just need to add explore.lkml as an extension in the filename.

To add an explore file, we recommend choosing + and Create View, then specifying the explore’s name using an “explore.lkml” extension. Looker displays the file in the Other section of the LookML IDE.



@devwiredau - can you share your workaround here? I’m experiencing the same problem


(Graham Stanton) #23

Has anyone gotten this to work by including the model file rather than by creating a separate explore file?

I get a duplicate explore definition error, which I assume is created by circular dependencies, i.e. model M includes all views, including view V, which includes model M in order to use one of its explores as an explore source.

Is there a way to fix this without moving the explore into its own file and being careful about the view includes in that one?

Adding onto this question because it’s related. I just noticed that this whole NDT approach forces every model (defined in the default way) to include every explore file. That’s because the models include all the views, and the NDT view includes the explore it’s based on.

Should we stop importing *.view and start listing them explicitly? I’d still like to be able to use an NDT-defined view in a model without exposing its underlying explore, but that seems like a minor point.

I also want to add on that I am loving this new feature overall. It’s easily the most exciting advancement in LookML in the three years that we’ve been working with you guys. Making every query potentially reusable as a view is big deal. It’s a step toward LookML becoming a full replacement for SQL.


(Graham Stanton) #24

Probably a more important question than my other one. Is it possible to use a custom filter in an NDT? It doesn’t appear that the syntax supports it, yet.


(Graham Stanton) #25

@rufus, I’m not sure if @devwiredau ever wrote into support, but I’m running into the exact same issue with timezones. Here’s an overview of the syntax:

In view v1:

dimension_group: event_time {
  type: time
  convert_tz: yes
  timeframes: [
  sql: ${TABLE}.event_time ;;

Then explore based on event_time_date, resulting SQL for the dimension is:

DATE(CONVERT_TIMEZONE('UTC', 'America/New_York', v1.event_time))

All good so far.

But if I create view v2 based on this explore with column event_time_date (even using the Looker provided native derived table syntax based on the explore), the resulting SQL for view v2 is missing the CONVERT_TIMEZONE piece. @devwiredau’s workaround is the same one I used, and it works great, but it should be unnecessary, and I also don’t love hardcoding the reporting time zone in the LOOKML.


(lloyd tabb) #26

Yes, look at the bind_fitlers: syntax. Here is an example:



(lloyd tabb) #27

We’re working on this. There are a couple of problems here. When you convert a time to a date, there is some implied to be some timezone. The problem is that on a ephemeral derived table, you likely want the reporting timezone and on a persistent one, you actually need to make a decision. This is also compounded that Looker treats dates as string. We’re likely to make some change here, but we don’t want to do it twice so we are going a little slowly and carefully here.


(Graham Stanton) #28

Thanks, @lloydtabb. I definitely get the potential gotchas here, so it’s good to hear you guys are being careful.

Going by the principle of least surprise, I’d say I’d expect an explore of a view based on an NDT to match exactly the results in the explore it was based on, especially if created that view by copying generated LookML.

If it’s a timestamp field, then that works just fine if the timezone conversion is done in the final query (but it certainly can’t be done at both the derived table building and the query steps). But if it’s a date field, then it has to be done at the derived table building step for the aggregates to be done with the right time boundaries.


(Graham Stanton) #29

I was thinking about the “Custom Filter” query syntax (Looker Expression) in an explore, which is necessary when, e.g. comparing one dimension to another. Is that possible?

It’s not a big deal to define a derived dimension in a view file somewhere, but it’s sometimes nice to keep cross-view calculations out of view files completely.


(rufus) #30

Custom filter expression syntax is specific to custom filters. Native Derived Tables perform the same function as writing a SQL query, but are expressed natively in the LookML language, so you should be able to compare one field to another. Do you have an example of what you’re trying to do here?


(Graham Stanton) #31

Hi @rufus. I generated the NDT LookML and noted that all the custom filter syntax disappeared.

I think in this case it was confirming a lead capture date was prior to a purchase date. This is possible via custom filter syntax, but I couldn’t figure out how I to do it in NDT syntax.


(rufus) #32

Hey @graham, could you send an example to help.looker.com? We’d be happy to take a look and figure out how to make it work.


(quinn.morrison) #33

Hi @graham,

I previously saw these duplicate explore errors when trying to include a model file in a NDT, and this issue has been reported. However, the errors that I experienced have since disappeared, and I’ve been able to include model files in my NDTs (with the *.view import in the model). Have you tried including a model in an NDT recently, and are you still experiencing these errors?


(Diego Perez) #34

rufus, can you help me too? I have some questions and I wanted to ask you if I could get some answers as well? i’m currently doing nothing at home but searching for drug reviews because of my recent health issues. in the meantime wanted to learn and this site seems an amazing place. thanks in advance. and by the way thanks everyone as i found some help through the posts here.


(rufus) #35

@Morguitaine of course!

Discourse is a great place to ask general questions and to check whether other users have experience that can help with what you’re working on (or to just share something cool you’ve done in Looker). However, sometimes it can be beneficial to send questions directly to help.looker.com, especially if they are related to your particular data model.

Send over an email and we’ll be happy to help.


(Mark Goodwin) #36

Hi @quinn.morrison

I recently tried including the model file just as @graham, and I also got the duplicate view error. My work around was to create a new “explore.lkml” file that includes the explore I need, but with a different name. I had to use the “from:” definition to allow it to happen, like this:

explore: actions_ndt {
from: actions
persist_for: “2 hours”
group_label: “Actions”
label: “Analyze Communications Hub Activity”

Is this the right way to approach this? Or at least what’s the best practice? I already have the actions explore in another model file and I don’t want to take it out.



(quinn.morrison) #37

Hi @Mark_Goodwin,

Thanks for sharing this! Best practices for utilizing includes in NDTs varies from project to project. Often, when there are multiple models in the project, you will see these duplicate explore errors when including a model file in the NDT view. In these scenarios, its best to take your approach, involving creating another file in which you define the explore that will be used in the NDT, and including this in the NDT view.

Another option is to create your NDT view file with no includes defined. Without specifying any additional includes, you will still be able to create and explore your NDT normally. The only draw back is that the validator will throw and error for not including a file with the explore_source definition in the NDT view file. However, if you do wish to go this route, you have the option to turn off requiring LookML validation to commit in your instance admin panel, and simply live with this Lookml error.

Hope this helps!



(Ayrton Barros) #38

Hello guys, I’m trying to use the bind_filter feature with a string dimension, and I’m not having any luck :frowning:
Anyone around here had success with it? Tks!


(Morgan Imel) #39

Hi @Ayrton_Barros,
If you’d like to come on chat or send us an email at help.looker.com, we’d be happy to take a deeper look!


(Devin) #40

Hey @graham did your convert_timezone hack break recently with one of the recent releases? Mine did and I’m trying to figure out how to fix.

@lloydtabb Did you guys push a solution for this? Do you have any explanation on how to deal with this now because the convert_timezone doesn’t work anymore.

This was resolved. No big issue with the new timezone functionality.