Layer, Refinement, Extend or: what is the best solution?

[moebe]

Layer, Refinement, Extend or: what is the best solution?

We are currently working on the largest of our LookML models.
This model is actually not that complicated, but it gets a lot more complex due to looker behavior and business requirements.

The parameters:

• Only 7 tables.

• 1 fact with 21.5 billion rows and 50 columns

• 1 basic dimension with 31.5 million rows and 183 columns.

• 5 dimensional tables with “small stock”.

• The fact contains several “delivery dates” which have different statements. (e.g. in which month we receive money for a product, in which month the product must be delivered and in which month we have currency risks (exchange rates) for it.

• The fact contains ONLY the raw data key figures. From these, ALL business key figures are derived in LookML.

• The dimension contains the most different master data which we have named thematically bundled.Nevertheless, there are fields whose content occurs multiple times. (For example the country. This can be a border “flow”, i.e. from which country to which country is delivered. But it can also be the country of production, or the country where the product is sold etc.) This is also solved by a naming convention.

So far so good.

In order to bring structure into the whole thing, we have made use of Refinements by:

• raw_lyer (Idea copied from @fabio1 from the article LookML layering), which contains all views as 1:1 views.
• standard_layer where we assign labels and descriptions etc. and hide the not needed elements (PK keys, control fields, internal data quality fields etc.) and do the “for all customer the same” key figures.
• specific_layer where we make all adjustments to the dimensions or facts that do not apply to every single explore, or that contain so much additional logic (liquid) that we wanted to encapsulate them.

So far actually, so good… sounds quite structured and well thought out in my opinion…

But now we come to the complexity mentioned above.

All key figures that we calculate need information from other dimensions.
Therefore, many of our key figures look like this:

  sql: case when ${pdt_dim_time_delta.bki_delta_type}= 'BASE' 
	and ${dim_vb_txn_profile.delivery_status}= 'UNREALIZED' 
   then ${pre_valuation_value_udsc}
       when ${pdt_dim_time_delta.bki_delta_type}= 'YtD' 
	and ${dim_vb_txn_profile.delivery_status}='UNREALIZED'.
      	and ${dim_time_delivery.bki_date_id_year} <= ${pdt_dim_time_delta.bki_date_id_prev_year} 
   then ${pre_valuation_value_udsc}*-1
       end ;;

So here I need fields from 4 tables for the evaluation.
When I look at the guideline of @fabio1 Rule F1 , says
“Fields in a view should not reference other views…”

view: users {...}
view: orders {
  ...
  measure: orders_per_user {
    sql: ${count} / NULLIF(${users.count},0)
  }
}
explore: orders {} # Errors :(


view: users {...}
view: orders {...}
view: users_orders {
  # No need for a sql_table_name or derived_table
  measure: orders_per_user {
    sql: ${orders.count} / NULLIF(${users.count},0)
  }
}
explore: orders {} #Doesn't break like before!
explore: users {
  join: orders {...}
  join: users_orders {
    sql: ;;
    # Use `sql` instead of `sql_on` and put some whitespace in it
    relationship: one_to_one
    view_label: "Orders" #For cleaner explore UI
  }
}

but I think that would still be acceptable, because he also writes:
… unless this is (A) a field-only view, or (B) a tightly coupled view (provide a base explore too!) and B would apply…

But now add complexity two.

For example, different metrics are only available in different currencies and some even only in the “original currency” (whatever that may be).
At the same time, our goal is to have all these metrics in one Explore, otherwise cross-filtering won’t work, which is a basic requirement for many of our planned dashboards.
So the simplest solution, making multiple explores, is not applicable.

Now I thought I’m smart and use Refinements to turn the _Raw View into many theme-oriented views. But of course this doesn’t work either, because a Refinement overwrites the refinements made before. So I can’t give the different key figures their own view label - without doing it manually for each field - .
So this is not such a great idea either.
Extends: Here I could go further. I could probably divide the big base tables into many small ones using extends. But how do I get all of them back together in one Explore.

How do you solve such topics, or do you not have such topics?
What would you try to do?

[fabio1]

Hi @moebe

For rule F1, it’s just a suggestion, rather than a hard requirement, so it’s best to understand the problem that it is trying to address and come up with a considered solution, even if it does not exactly match the rule.

On customizing “view_label”, this is something you can do at the join level, so in many cases you can use the same view multiple times in an explore:

explore: orders {
 join: shipping_address {
  from:address
 }
join: billing_address {
  from: address
 }
}

Beyond this recommendation, I think I would need some more examples of your data structure to fully grasp what you’d like to do.

[Dawid]

I think your example is very complex and you would probably need to break it into smaller chunks to get valuable help from the Looker Community. One would have to spend considerable amount of time, in order to fully grasp how to model your situation