LinkedIn
Twitter
Hi everyone,
I'm seeking guidance on creating a LookML model that empowers end users to construct dashboards with dynamic filtering and visualization freedom. Here's the scenario:
I want to create a model that initially joins tables table1 to table2 to table3 ... using a left outer join based on the relationship one_to_many between table1.id and table2.id, and a left outer join between table2.id and table3.id....
explore: table1 {
join: table2 {
type: left_outer
relationship: one_to_many
sql_on: ${table1.id} = ${table2.id} ;;
}
join: table3 {
type: left_outer
relationship: one_to_many
sql_on: ${table2.id} = ${table3.id} ;;
}
join: table4 {
type: left_outer
relationship: one_to_many
sql_on: ${table3.id} = ${table4.id} ;;
}
}....
My goal is to provide users with the flexibility to create visualizations that may or may not include fields from all tables.
Challenge and Desired Behavior:
However, I've encountered an issue when attempting to achieve this dynamic behavior. In my current approach, I've defined the joins as described above. But when I try to display a field from table3 in an exploration, Looker generates a query that joins all three tables by default (e.g., select field from table1 left join table2 left join table3...).
Desired Outcome:
I'd like to achieve behavior similar to Power BI or SAP BO, where joins are performed only when necessary based on the fields selected in the visualization. This would result in more efficient queries and a more intuitive user experience.
Questions:
Is there a specific approach in LookML that facilitates this type of model behavior?
Can I achieve this by utilizing LookML ?
Any insights or recommendations from the Looker community would be greatly appreciated!
thank you
