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.
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:
@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?
@Stephen_Bronste 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
.
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} ;;}
}
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;
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?
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 🙂