Question

Always_filter challenge

  • 8 February 2016
  • 7 replies
  • 299 views

Userlevel 1

Hi Looker,


We are a C2B marketplace for Home Improvement jobs. We have consumers that post their job (“Service Request”) and we have Service Professionals that can react on such a request (they write a “Proposal”).


Logically, we have a Consumer-explore, with consumer-data and Service Request-data.

And we have a ServiceProfessionals-explore, with SP-data and Proposal-data.


Moreover, we also have a generic ‘Date’-explore, that we use for Management purposes.

For example, if we want to know how many SRs we got ánd how many new SPs signed up in month X we can find out really easy with this explore and visualize it.


Also: currently we are active in 1 country, but this will be more countries soon.

Therefore, we have modelled all our views in a way that 1 view always consists of all the data for all countries.


We can use an always_filter so that we by default only look at country X, but end-users can update this filter if they want to look at country X ánd country Y. This works like a charm for our Consumer-explore and our ServiceProfessionals-explore, but if we also want to do this for our Date-explore, things become tricky.


For example, I can add this to the model



  • explore: dates

    always_filter:

    sp_country: ‘Italy’

    sr_country: ‘Italy’


And this to the date-view:




  • dimension: sp_country

    label: ‘SP Country’

    sql: ${servicepro.country}




  • dimension: sr_country

    label: ‘SR Country’

    sql: ${servicerequest.country}




But then we also have to do it for all the other views that directly linked to date, for example Proposal and Review.


Then we will end up with this:


always_filter:

sp_country: ‘Italy’

sr_country: ‘Italy’

proposal_country: ‘Italy’

review_country: ‘Italy’


However: the big, big disadvantage of the above implementation, is that for every Look you will create in this Explore, an SQL-query with 4 Left Joins will be executed, even if you don’t “need” all of those Joins (for example, because you only want to check out SPs and Reviews; not SRs and Proposals). Therefore, the overall performance of the Date-Explore will be bad, even when you have simple questions.


Any thoughts/ideas on how to do this?

Or maybe, in general, a “date”-explore is not a good idea at all (maybe we can better just combine several metrics by creating tiles from different Explores and combine them into 1 dashboard instead of trying to create this “master” explore?)?


Thanks, Willemijn.


7 replies

Userlevel 1

When I have an access filter that will be applied to more than 1 explore, I generally create a view just for that access filter and join it in to each explore, so even if the filter is applied to four different explores at least you only need to set one.


Not sure if this helps with your left join issue.

Userlevel 1

Hi Phil,

Thanks for your answer. I tried to sent you a DM, but apparently this forum doesn’t have such a feature?..

Two questions about your post:



  • Do you mean “always_filter” or “access_filter”? My question was about always_filter.

  • Do you mean “4 different explores” or “4 different views”? My question was about 1 explore, in which we join multiple views.

    Thanks, Willemijn.

Userlevel 1

I was actually talking about access_filters - but the same method may work for always_filters:-


Create a table containing a list of distinct filter values (in your case countries). Use this as the base table for the explore:-


explore: dates
always_filter: country
view: countries
joins:
-join: dates
type:..
relationship: ...
sql_on: ${countries.country} = ${servicepro.country}

As the filter will only effect the base table in the explore, the left-joins will only be added if dimensions and measures are selected for those joins.

Userlevel 6
Badge

@wdijkhuizen maybe this isn’t how you are implementing it, but a pattern that works in the small, but will eventually fail is:


   - explore: date
join:
- join: proposals
sql_on: ...
- join: service_requests
sql_on: ...
- join: reviews
sql_on: ...

The problem here is that you get a cross product of all transactions for the day.


I think you are looking for a transactional funnel where you can filter on country of the transaction. If you build a derived tables that is a union of all your transactions with a type (and in this case, a country), you can achieve much better performance, your singular country (move the country into the funnel table).


Here is an article that describes out to build a funnel out of multiple transactions:




Please let me know if I misunderstood your explore’s structure.

Userlevel 6
Badge

BTW: there is a working example of the funnel model on learn.looker.com that you can play with:


https://learn.looker.com/explore/255_funnel/funnel

@wdijkhuizen Sounds like a similar problem to what I am constantly facing. We use the words “KPI Style” vs “Cohort Style” internally to describe the 2 different ways.


BTW we are also a marketplace, B2B.


Here are some references:




We solved this by with our “generic date” is a HUGE union that occurs for all the different events. Its a PDT and its slow to generate but fairly fast when you filter on only the event types you are interested int.


I have also sent a few emails to looker folks asking to get their opinions on this use case. Happy to connect offline.


CC @Erin_Breen

Userlevel 1

Thanks for informing us; since we are writing LookML-code for “only” a few weeks now 😉

We will read the article you’ve sent and refactor our code to the the pattern as described in the article a.s.a.p.

Maybe it is nice to explain this pattern to new clients during the onboarding-phase? We didn’t get this feedback from our Looker analists in the “Looker Model Review”-document.

Cheers and thanks for now!

Reply