Question

UNION of explores

  • 9 December 2019
  • 5 replies
  • 1453 views

I have created two explores, A and B, that I want to combine into a new explore, C.

A and B should be combined by a UNION, therefore I cannot do this directly in the new explore so I have created a new view with a derived table.

In the views for A and B I have a lot of measures and dimensions that are derived outside of their derived tables, of course also the rules for presentation are set in the measures and dimensions. To be able to reuse this in explore C I have created Native Derived Tables from explore A and B.

In the view for explore C (where I create a UNION of the two NDT’s) I tried to use “extends” to get all information regarding presentation from A and B, but then I get LookML warnings. According to Looker Team I should not use “extends” when I have Native Derived Tables.

If I remove function “extends” from the view for explore C, then I have to define all rules for presentation of measures and dimensions again. Since I want to repeat this and create several explores in the same way, I will have to add the same rules over and over again.

Is there any best practice for how to combine existing explores into new explores when I want to use a UNION to combine them? Thankful if anyone could help me with this.


5 replies

I have a similiar use-case that I am working on implementing. Unfortunately, I can’t answer your main question but I am curious how you are performing the union in explore C. As far as I know, the only way do this is by using SQL UNION statements in sql derived tables. But what tables are you querying for views A & B? Their PDTs that are created by looker?

Userlevel 3

Hello Clayton,


We did something that looks similar on our side using union of NDT. This allows you to avoid rebuilding the logic in pure SQL DT. Of course, each NDT needs to pull the exact same necessary fields in the exact same order to make the UNION work!


image


Antho

Awesome, that’s exactly what I’m looking for. So, to be clear, in the underlying views, the order the dimensions are defined will dictate the order that they are selected in the resulting SQL queries? I wasn’t sure if that was guaranteed or not. That’s good to know.


Also, I assume only dimensions (not measures) come through from the underlying views, correct?

This was really helpful @antho 

It is worth clarifying that the `googleads_crosschannel_ndt` etc are the names of Explores that are accessible through `includes` in the view.

Both dimensions and measures from the NDT / Explore are available in the view.

 

 

Userlevel 3

Hello @vpipkt,

Indeed, I could have been more specific here: 

As you said, I defined multiple NDT from different explore sources but using a strict similarity of fields and order in order to combine multiple advertising sources.

Quick highlight below (I had around 30 fields for each NDT and added persistence to increase the final “UNIONED” explore performance ;)

 

 

Reply