Question

[Retired] Using Native Derived Tables


Userlevel 6
Badge

This article has been retired as the information is now in documentation on this page.



If you reached here from the product, our apologies – we’re working on getting that link redirected to this LookML reference page.


45 replies

Userlevel 6
Badge

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.

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.

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.

Userlevel 3

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?

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.

Userlevel 3

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.

Userlevel 2

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?

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.

Userlevel 3

@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.

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.


Mark

Userlevel 2

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!


Quinn

Hello guys, I’m trying to use the bind_filter feature with a string dimension, and I’m not having any luck 😦

Anyone around here had success with it? Tks!

Userlevel 3
Badge

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!

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.

Userlevel 2

Hi @devwiredau,


I sent you an email, but it will be beneficial to post a response here as well.

As of release 5.0, Looker now officially supports timezone conversion for NDTs (you can check this out in our release notes.


There is now a timezone parameter supported in the explore_source to convert to a consistent timezone or, for ephemeral derived tables, to the timezone your query is in. Not specifying a timezone will default to no timezone conversion (AKA the database timezone).


Thanks!


Quinn

IMO this is a really big limitation to this feature which I think 1) should be noted at the top of the page, and 2) will hopefully be addressed by Looker soon.


Our standard workflow for creating a new DT is:



  1. can we calculate the column we need using the existing explore & underlying views?

  2. if no, create DT to aggregate as needed - and if NDT, using that same explore

  3. join DT back to the original explore


@quinn.morrison I think you are saying that the only way to commit the code created in this process is to turn off LookML validation? Does that mean we are turning off LookML validation forever for all commits?

Userlevel 6
Badge

@Stephen_Bronstein the best practice in complex models with NDTs is to put your explores in separate files. You can do this by creating a new file in a project and simply giving it the name foo.explore.lkml. Once you do this, include all the views that the explore needs.


In the views that are NDTs, include the explore files. Everything will validate properly. Here area couple of examples (and sorry for the compressed format. The example I’m drawing from is illustrating a way to write more dense LookML)


You will notice the view user_order_sequence is a native derived table based on the explore order_items and also joined in to order_items.


order_items.explore.lkml



include: "users.view"
include: "inventory_items.view"
include: "products.view"
include: "distribution_centers.view"
include: "user_order_sequence.view"
include: "order_items.view"
include: "user_joins.explore"


explore: order_items {
extends: [user_joins]
join: users {relationship:many_to_one sql_on: ${order_items.user_id} = ${users.id} ;;}
join: inventory_items {relationship:many_to_one sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;}
join: products {relationship:many_to_one sql_on: ${inventory_items.product_id} = ${products.id} ;;}
join: distribution_centers {relationship:many_to_one sql_on: ${products.distribution_center_id} = ${distribution_centers.id} ;;}
join: user_order_sequence {relationship: many_to_one
sql_on: ${user_order_sequence.order_id} = ${order_items.order_id} ;;}
}

order_items.view.lkml


include: "order_items.explore"

explore: user_order_sequence {}
view: user_order_sequence {
derived_table: {
#persist_for: "2 hours"
explore_source: order_items {
column: user_id { field: order_items.user_id}
column: order_id {field: order_items.order_id}
column: created_time {field: order_items.created_time}
derived_column: user_sequence { sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;}
}
}
dimension: order_id {hidden:yes}
dimension: user_sequence {type:number}
}

The full repository is here;


https://github.com/looker/bq_thelook

Thanks @lloydtabb for the detailed reply! I am now up and running with the NDT after I created a separate explore file as per your instructions. And apologies for misreading @quinn.morrison’s comment where she does indeed say to create a new explore, not a new model as I had (mis)read.


Note that, in order to create a file that ended with .lkml, I ultimately selected ‘Create View’ and then changed the name to replace .view with .explore. I first tried ‘Create Document’ but that ended up with an invisible .md at the end.

I need to UNION two tables. Is it possible/good practice to union two NDTs?

Userlevel 7
Badge +1

I don’t believe you can do a UNION within an NDT, but you could union two NDTs together in a non-native derived table, just like:


view: derived_table {
derived_table: {
sql: select * from ${an_ndt.SQL_TABLE_NAME}
UNION ALL
select * from ${another_ndt.SQL_TABLE_NAME};;
}

Takes you out of the nicely defined NDT ecosystem, but it should work 🙂

Reply