Question

How to create cyclic dimensions in LookML

  • 19 December 2017
  • 3 replies
  • 149 views

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?


3 replies

Userlevel 2

Hi @rnukala,


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.

Userlevel 2

Hi @rnukala,


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

Reply