Building a Flexible LookML Model

[otaku]

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 

[salehaxsid]

Looker's base is SQL, so when select a field from table 3 it shows joins of table3 with every other table same as SQL approach.
If you don't want the join to happen in front end and there's no dependency on other tables, then just create a separate explore of Table3
just by adding
explore: table3{},
you will be able to select this explore from explore pane and create visuals via that explore and not joined explore.

[salehaxsid]

Also, you can create explores based on hierarchy and keep it separate 
For example:
I have sales, customer, product -> category table.
1. Here category must be joined with product in order to retrieve its data, hence we will create one explore of product which will have product table joined with category.
2. After creating product explore you can create sales table explore and it will be joined with customer and product.

Hope this explanation helps.

[otaku]

Thank you @salehaxsid  for your reply.

To keep it simple, let's take a very basic use case:

I would like to set up a single explore that allows me to:

Create a table with the names of the customers. The generated SQL script should be: SELECT name FROM customer.

When a user creates a table with a customer column and a sales column, the generated SQL script should be: SELECT customer.name, sales.quantity FROM sales LEFT JOIN customer ON id.

Basically, I would like to have a single option to choose the customers, whether alone or with another table. The generated SQL script should adapt to the user's needs. SAP BO does this very well and I would like to reproduce this mode of operation.

I hope you understood my need

Thank you so much for your help

[salehaxsid]

I kind of understand that and you can achieve this by creating desired explores, if you want only customer table then create explore of customer without any sql join parameter, if you want only sales and customer then create another explore for customer with sql join of sales. This way different explore create different queries for the joins mentioned in explore.

[Sliam]

Hello @salehaxsid 
Do you have a suggestion regarding the unnecessary joins that looker creates?
Thank you

[Victor_P]

Hi Otaku, thanks for your post.

Looker generates SQL with a "right-deep join tree". The base view of the explore (table1 in this case) is always used as the start of the FROM clause, then joins are added to reach other fields requested by the user. In this case we must trace table1->table2->table3 in order to display the fields from table3. You can have multiple branches though. For example, if table4 was joined directly to table1, and the user only selected a field from table4, then only the table1->table4 join would be rendered, since we don't need anything from the table2/3 branch.

In order to query only from table3, you would need to define a new `explore` object with table3 as the base view. I realize this is a bit different from other tools that are oriented around abstract table relationships. In general, you want to base your explores on the most granular fact table of the star schema, where most of the aggregation logic will be running, then join out to your dimension tables. You can also use derived tables to create subqueries (CTEs) or materialized aggregates/rollups.

[otaku]

Hello @Victor_P 

Thank you very much for your response.

Indeed, I understand better now.

In my case, the PowerBI models are not analytical, meaning there is no fact table with only a few dimensions around it. I have transactional models (similar to the example model I mentioned earlier). The models are like this, and we let the end users develop their reports afterwards. This makes it complex to migrate my PowerBI models to LookML.

I cannot create multiple explorations because I don't know the final needs of the users. What do you suggest for migrating transactional PowerBI models with multiple views and multiple joins between the views to LookML?

I understand what derived tables are, but I'm having trouble seeing how they can help me. However, I will look more closely at derived tables to create subqueries (CTEs) or materialized

[otaku]

From my understanding, we need to rethink our approach with LookML.

Basically, we should define the use cases in advance and then create star schema models that will meet their needs.

I am probably not the first one to migrate standard BI tools to LookML, so I would really like to get some feedback.