Question

SQL UNION

  • 4 March 2021
  • 5 replies
  • 445 views

Userlevel 1

Is there likely to be some way in the future to do this in Looker without writing a derived table?


This topic has been closed for comments

5 replies

Userlevel 3

Hi Kuopaz,

 

Unfortunately there is no alternative to a derived table for your use case.

 

The best practice advice we stick to is to create all the required integrations within the ETL process, so minimal transformation has to happen in your analytics layer.

 

It would be good to understand your use case better though, what are you trying to achieve?

 

Best,

Userlevel 1

Thanks, that’s what I thought - can only be done in a SQL DT, or better an ETL as you say.

 

The general case I am thinking about is joining fact tables that are only ‘associated’ by a common dimension, and not keys. Such as Purchases and Sales that could share a Product ID or Calendar Date. One way to do this is to ‘union the keys’ from both tables.

Can do it with joins combined with symmetric measures, but will get row explosion.

Userlevel 6
Badge +1

We do have similar cases as yours where different types of sales could be unioned but 99% of the time I would do something like that in the data modelling layer. It gives you more flexibility to deal with your data, rather than rely on Looker and its features to do some heavy work.

Userlevel 1

Agree, it’s really a data modelling issue in this case - that is going to be implemented in the database. I ask, because you can do so much in Looker without prior transformation, largely due to symmetric measures (one of the selling points).

Userlevel 6
Badge +1

That’s true, the derived tables, aggregation, and symetric measures give us some interesting possibilities but I prefer to think about them as add-ons for things that you don’t need/want in your data model. Sometimes these could be proof of concepts or, in my case, these are admin-related data.

For example, I have a admin model in Looker that uses information schema tables from BigQuery to get some stats. To me, there’s no need for it to be in our data model as it’s on a side. Here having a derived table is very useful