Lets assume I have views like
customer_dim,
product_dim,
store_dim
sales_fact
All the dim views are joined to fact view (Star schema).
In this case can we create dynamic dimension which enables the user to select a dimension from the different dim tables so that the user can calculate Total sales by customer , sales by product or sales by store?
Hi
Thanks for reaching out. To achieve this type of dynamic querying within Looker, we can use liquid statements. From your description, it sounds like this is a good use case for a feature called parameters. This would allow you to create a dynamic measure, that would calculate the total of whichever dimension is selected in the parameter field (which will appear as a filter-only field in the explore). These dimensions can come from different views, as long as they are joined in the given explore and properly scoped with view_name.field_name reference.
Another option here is to use the liquid variable _in__query to create a measure that evaluates to different conditions depending on whether certain fields are included in the query. You can use this variable in the context of any LookML sql parameter. For example:
measure: in_query_total {
type: sum
sql:{% if customer_dim.field_name._in__query %}
${customer_dim.field_name}
{% elsif product_dim.field_name._in__query %}
${product_dim.field_name}
{% elsif store_dim.field_name._in__query %}
${store_dim.field_name}
{% else %}
null
{% endif %} ;;
}
If you’d like to discuss these options in more depth, please visit us at help.looker.com, and we can dive deeper into your use case.
Thanks!
Quinn
Thank you Quin for your solution. But my use case is different. The use case is like that.
View: customer {
dimension: customer_name
dimension:customer_id(PK)
}
view: store{
dimension:store_name
dimension:store_id(PK)
}
view: product {
dimension: product_name
dimension: product_id (PK)
}
view: sales {
dimension:id (PK)
dimension:product_id(FK)
dimension:store_id (FK)
dimension.customer_id (FK)
measure: sales
type:sum
}
Provided this type data model, There should be a parameter which accepts the dimension names from different views i.e. The user input can be any dimension name such as product_name,customer_name, store_name.
If the user inputs product_name (Input is the field name but not the field value), then the looker should generate the query as below:
select product.product_name, sum(sales) as sales
from sales
left join product on product.product_id=sales.product_id
group by product.product_name
If the user inputs store_name, then the looker should be able to generate the query as below:
select store.store_name, sum(sales) as sales
from sales
left join store on sales.store_id=store.store_id
group by store.store_name
If the user inputs customer_name , then the looker should be able to generate the query as below:
select customer.customer_name, sum(sales) as sales
from sales
left join customer on sales.store_id=customer.customer_id
group by customer.customer_name
The dimension in SELECT and GROUP BY should be dynamic. There should not be any unnecessary joins. I am able to achieve this but it is creating unnecessary joins. Is there anyway to achieve this dynamic behavior?
Thanks for your help in advance.
Hi
Thank you for clarifying your use case. Which explore are you working in that is producing these unnecessary joins in the SQL? If you are exploring within the sales view, and you have product, store, and customer joined to this explore in your model, Looker should generate the sql that you have specified above. The only reason we would see extra joins is if your were working in the customer explore, for example, and had joined product/store to this explore through sales, i.e.:
explore: customer {
join: sales {
sql_on: ${customer.id} = ${sales.customer_id} ;;
}
join: product {
sql_on: ${product.id} = ${sales.product_id} ;;
}
join: store {
sql_on: ${store.id} = ${sales.store_id} ;;
}
If you were to select product_name and sum(sales) in this context, it would still include customer in the FROM clause, because that is the base view of this explore. If you want to avoid the inclusion of views not in the query, you can do this by working within the sales explore, in which all other views (product, store, and customer) are directly joined.
Hope that helps!
Quinn
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
