[Retired] Using Native Derived Tables

This is honestly one of the best features Looker has ever launched! Big time saver. One weird thing I found so far is when using date as a dimension. The timezone conversion doesn’t work correctly so I had to create a new time dimension in my original view and use CONVERT_TIMEZONE before referencing it in the NDT. This seemed to work well.

Does anyone know if you can join one of these NDTs back to another table?

Yeah no problem. I’ll send an email to support.

You can definitely join one of these NDTs back to another table. Once defined, they really just behave exactly like the current sql-defined derived tables.

@mplooker as for joining it back to the original explore_source (great question!) you should indeed be able to do so, so long as you do not require fields from the explore in the list of columns that would cause an infinite dependency loop (which Looker should detect in validation and complain about). For example, this dependency loop would occur if two different views using explore_source were joined back into their original explore and each used a column which depended on a column of the other.

Will there be (or is there already) support for joins, unions, and templated filters in the Native Derived Table definition?

@lloydtabb is there any chance this relates to templated filters being applied at certain points of the SQL generation but not others?

i was recently experiencing a weird situation when I tried to define a PDT (PDT1) that contained a templated_filter (TF1), defined another PDT (PDT2pdt1) that referenced the initial PDT, defined an NDT that performed some agg on the second PDT (NDT1pdt2), and then finally joined the NDT (NDT1pdt2) back to the second PDT (PDT2). the resulting SQL would properly evaluate the templated_filter (TF1) within the initial PDT (PDT1) but not within the NDT (NDT1pdt2) … and would look something like this:

with PDT1 as 

(

    select  accountname

    from SOMESQLTABLE1

    where [****PROPERLY EVALUATED TF1****]

    group by accountname

),

PDT2 as

(

    select 

    date, 

    accountname, 

    sum(value) as sumvalue

    from SOMESQLTABLE2

    inner join PDT1

    on SOMESQLTABLE2.accountname = PDT1.accountname

    group by date, accountname

)



select 

PDT2.date, 

PDT2.accountname, 

PDT2.sumvalue,

NTD1pdt2.sumvalue_avg 

from PDT2

left outer join 

( 

    select 

	date, 

	avg(sumvalue) over (partition by date) as sumvalue_avg 

	from SOMESQLTABLE2

    inner join 

    (

    	select  accountname

    	from SOMESQLTABLE1

    	where [****IMPROPERLY EVALUATED TF1: 1=1 ****] 

    	group by accountname

    ) as PDT1hashcode

	on SOMESQLTABLE2.accountname = PDT1hashcode.accountname

) as NDT1pdt2

on PDT2.date = NDT1pdt2.date

@lloydtabb great, thanks! here’s a quick and dirty illustration of what i’m talking about… not 1:1 the exact situation i tried to describe above but the same issue seems to arise (in the resulting SQL, i’ve annotated the templated_filter clauses that are generated with asterisks) :

LOOKML:

explore: PDT2 {

  join: PDT1 {

    sql_on:  ${PDT1.accountname}=${PDT2.accountname} ;;

    type:  inner

    relationship: one_to_one

  }

  join: NDT1pdt2 {

    sql_on: ${PDT2.date} = ${NDT1pdt2.date} ;;

    type:  left_outer

    relationship: one_to_one

  }

}





view: PDT1 {

  derived_table: {

    sql:

          select uid, accountname from

          (

            select 1 uid, 'x' accountname UNION ALL

            select 2 uid, 'y' accountname

          ) a1

          where a1.accountname = {% parameter accountname %}

              ;;

  }



  dimension: PDT1uid {

    hidden: yes

    type: number

    value_format_name: id

    sql: ${TABLE}.uid ;;

    primary_key: yes

  }



  dimension: accountname {

    hidden: no

    type: string

    sql: ${TABLE}.accountname ;;

  }

}





view: PDT2 {

  derived_table: {

    sql:

        select a2.uid,  a2.date, a2.accountname,  a2.value from

        (

          select 1 uid, 100 value, 'x' accountname, '20161230' date UNION ALL

          select 2 uid, 200 value, 'x' accountname, '20161230' date UNION ALL

          select 3 uid, 500 value, 'x' accountname, '20161230' date UNION ALL

          select 4 uid, 800 value, 'y' accountname, '20161230' date UNION ALL

          select 5 uid, 250 value, 'y' accountname, '20161230' date UNION ALL

          select 6 uid, 100 value, 'x' accountname, '20161231' date UNION ALL

          select 7 uid, 400 value, 'x' accountname, '20161231' date UNION ALL

          select 8 uid, 600 value, 'x' accountname, '20161231' date UNION ALL

          select 9 uid, 100 value, 'y' accountname, '20161231' date

        ) a2

        inner join ${PDT1.SQL_TABLE_NAME} pdt1

        on pdt1.accountname = a2.accountname;;

  }



  dimension: PDT2uid {

    hidden: yes

    type: number

    value_format_name: id

    sql: ${TABLE}.uid ;;

    primary_key: yes

  }





  dimension: date {

    type: date

    sql: ${TABLE}.date ;;

  }



  dimension: accountname {

    hidden: yes

    type: string

    sql: ${TABLE}.accountname ;;

  }

  

  measure: sumvalue {

    type: sum

    sql: ${TABLE}.value ;;

    value_format_name: decimal_2

  }

}





view: NDT1pdt2 {

  derived_table: {

    explore_source: PDT2 {

      column: date {}

      column: sumvalue {}

      derived_column: sumvalue_avg {

        sql: AVG(sumvalue) OVER (PARTITION BY date) ;;

      }

    }

  }



  dimension: date {

    hidden: yes

    type: date

  }

  

  measure: sumvalue_avg {

    type: average

    value_format_name: decimal_2

  }

}

RESULTING SQL:

WITH PDT1 AS (select uid, accountname from

          (

            select 1 uid, 'x' accountname UNION ALL

            select 2 uid, 'y' accountname

          ) a1

        *********  where a1.accountname = 'x' *********

              )

  ,  PDT2 AS (select a2.uid,  a2.date, a2.accountname,  a2.value from

        (

          select 1 uid, 100 value, 'x' accountname, '20161230' date UNION ALL

          select 2 uid, 200 value, 'x' accountname, '20161230' date UNION ALL

          select 3 uid, 500 value, 'x' accountname, '20161230' date UNION ALL

          select 4 uid, 800 value, 'y' accountname, '20161230' date UNION ALL

          select 5 uid, 250 value, 'y' accountname, '20161230' date UNION ALL

          select 6 uid, 100 value, 'x' accountname, '20161231' date UNION ALL

          select 7 uid, 400 value, 'x' accountname, '20161231' date UNION ALL

          select 8 uid, 600 value, 'x' accountname, '20161231' date UNION ALL

          select 9 uid, 100 value, 'y' accountname, '20161231' date

        ) a2

        inner join PDT1 pdt1

        on pdt1.accountname = a2.accountname)

  ,  NDT1pdt2 AS (

SELECT as53f08f27e18.*,

       AVG(sumvalue) OVER (PARTITION BY date)  AS sumvalue_avg

FROM (SELECT 

	CONVERT(VARCHAR(10),PDT2.date ,120) AS date,

	COALESCE(SUM(PDT2.value ), 0) AS sumvalue

FROM (select a2.uid,  a2.date, a2.accountname,  a2.value from

        (

          select 1 uid, 100 value, 'x' accountname, '20161230' date UNION ALL

          select 2 uid, 200 value, 'x' accountname, '20161230' date UNION ALL

          select 3 uid, 500 value, 'x' accountname, '20161230' date UNION ALL

          select 4 uid, 800 value, 'y' accountname, '20161230' date UNION ALL

          select 5 uid, 250 value, 'y' accountname, '20161230' date UNION ALL

          select 6 uid, 100 value, 'x' accountname, '20161231' date UNION ALL

          select 7 uid, 400 value, 'x' accountname, '20161231' date UNION ALL

          select 8 uid, 600 value, 'x' accountname, '20161231' date UNION ALL

          select 9 uid, 100 value, 'y' accountname, '20161231' date

        ) a2

        inner join (select uid, accountname from

          (

            select 1 uid, 'x' accountname UNION ALL

            select 2 uid, 'y' accountname

          ) a1

          ****** where a1.accountname = '' ******

              ) pdt1

        on pdt1.accountname = a2.accountname) AS PDT2



GROUP BY CONVERT(VARCHAR(10),PDT2.date ,120)) as53f08f27e18

)

SELECT 

TOP 500 

	CONVERT(VARCHAR(10),PDT2.date ,120) AS "pdt2.date_1",

	PDT1.accountname  AS "pdt1.accountname_1",

	AVG(NDT1pdt2.sumvalue_avg) AS "ndt1pdt2.sumvalue_avg_1",

	COALESCE(SUM(PDT2.value ), 0) AS "pdt2.sumvalue_1"

FROM PDT2

INNER JOIN PDT1 ON PDT1.accountname=PDT2.accountname 

LEFT JOIN NDT1pdt2 ON (CONVERT(VARCHAR(10),PDT2.date ,120)) = (CONVERT(VARCHAR(10),NDT1pdt2.date,120)) 



WHERE 

	(PDT1.accountname = 'x')

GROUP BY CONVERT(VARCHAR(10),PDT2.date ,120),PDT1.accountname 

ORDER BY 3 DESC

Hi Lloyd, sorry, maybe I wasn’t clear enough. I meant if I currently have a sql-defined PDT which joins two or more tables together, can I write a corresponding LookML-defined PDT which does this join, or is it currently not supported?

This a great feature!! We’re trying it out already!

Just one question, is it necessary to declare all columns inside the ‘explore_source’, or is there a way to retrieve all columns available by the explore?

For example, if we create a NDT that is retrieving columns from an explore that uses the view ‘orders’, when we create a new measure inside ‘orders’ view, do we also have to declare this column inside the ‘explore_source’ of the NDT?

this is pretty awesome. what an interesting way to preserve lookml definitions.

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

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.

@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: [

    time,

    date,

    week,

    month,

    raw

  ]

  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.