Question re: "A Cure For the one_to_many Blues" Article

[DeclanD]

Hi All,

I've implemented the "Join Paths" model design described in Fabio's  "A Cure For the one_to_many Blues" article in some of my explores and it's working brilliantly for the most part! There is one aspect that's causing me some confusion, which is the "Measuring Users" section of the article: 

If we also needed to measure on the user table, we could join it a second time, on its own join path. The first user join, then, would be useful for dimensions for each of the user, order, and payment records; and the second (unfanned) user join could be used for measuring users. This is actually quite easy to do in LookML, by referencing the same view twice and using the fields parameter to exclude measures from the Explore, and then to only include measures from the path-restricted view.

If I'm understanding this correctly, this would involve adding a join called say users_measures to the Explore to host the measures. If I include the user measures in a set called measures, the Explore might look like this:

explore: users {
 fields: [ALL_FIELDS*, -users.measures*] # measures should come from unfanned join

 join: user_join_paths {...]

 join: users_measures {
  from: users
  fields: [measures*] # include measures only, dimensions come from base 'users' table
  relationship: one_to_one
  sql_on: ${user_join_paths.path} = 'users'
  AND ${users_measures.id} = ${users.id} ;;
 }
}

I'm running into an issue where I have user measures that require a filter on a user dimension. Say I wanted to count the # of internal users - my users view might look like this:

view: users {
 dimension: id {}
 dimension: type {}
 measure: total_internal_users {
  type: count
  filters: [type: "Internal"]
 }
 set: measures {fields: [total_internal_users]}
}

Unfortunately, the total_internal_users measure returns a LookML error in this explore: "Unknown field 'users_measures.type' referenced in 'filters' parameter" since the type dimension exists in the base users view but is excluded from the users_measures join. One possible workaround would be to duplicate each dimension so that one field would be a user-facing dimension that would be surfaced in the Explore menu and the duplicated dimension would be a hidden field that's used in measures' filter parameters. This does not seem like the cleanest, most DRY solution though. Is there a better approach I could be using?

[DeclanD]

Hi @fabio1, would you be able to help me with this? Thanks!

[fabio1 · Accepted solution]

Hey @DeclanD - there is no perfect approach here, but I'll share my preferred approach and its caveats:

My ideal solution is to avoid repetition by using a fully qualified field reference here, such as "users.type". Unfortunately, this only feasible when you can reliably know the name of the view as joined into the explore, and would cause problems when the view might need to be aliased in a join (e.g. if you were to explore `messages` and had users joined on as `senders` and `recipients`), in which case some duplication would usually be necessary.

[DeclanD]

Thank you for the quick response! That makes sense and I figured that was probably the case, I just wanted to make sure I wasn't missing out on a potentially cleaner solution.