Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Is there a way to perform a query that builds a dashboard by injecting parameters at runtime?

  • 14 November 2015
  • 3 replies
  • 117 views
O

Suppose I have a table called sales_fact and a table item_groups in following layout:



sales_fact:


| sale_id | date | item_id | amount |



item_groups:


| item_id | group_name |



sales_fact and item_groups are linked together in 1:m relationship i.e. a items has multiple sales groups.


I want create a dashboard to report the value of each group.



If the cardinality of the sale_fact and item_groups is small then creating a PDT is ok.



If the cardinality of the two tables is quite big, let’s 110^9 rows for sales and an average ratio item to groups is 1:10 then the PDT would end up ~ 110^10.



However, in most use cases there would be no need to materialise the join between the two table because the both sales_fact and item_group tables would be filtered reducing a lot the number of row on which the join is done.



Essentially I would like to be able to build the following query



SELECT s.*, i.* FROM sale_fact s LEFT JOIN item_groups i ON i.item_id = s.item_id WHERE s.date BETWEEN ? AND ? AND i.group_name IN (?)



and being able to inject the value for the placeholders ?

3 replies

lloydtabb
Rank
Userlevel 6
Badge

LookML can model this easily. Looker’s Symmetric Aggregates (Symmetric Aggregates) make it so you can explore this data even though there is a one to many relationship.



Assume the model:



- explore: sales


  from: sales_facts


  joins:


  - join: item_groups


    sql_on: ${sales.item_id} = ${item_groups.item_id}


    relationship: one_to_many





- view: sales_facts


  fields: 


  - dimension: sale_id


  - dimension_group: sale


    type: time


    timeframes: [date, week, month, year]


    sql: ${TABLE}.date


  - dimension: amount


    type: number


  - measure: total_amount


    type: sum


    sql: ${amount}





- view: item_groups


  fields:


  - dimension: item_id


  - dimension: group_name


Looker will correctly compute a table containing the following:















Item Groups Group Name		 SalesTotal Amount


Items that are in multiple groups will appear in totals for both groups. You can simply add filters to reduce the groups you wish to show.



Play with the video store example. Categories in movies is pretty much the same thing, each movie can be in multiple categories.



Grouping by category yeilds:





And the sql:



SELECT 


	category.name AS `category.name`,


	COALESCE( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(film.replacement_cost,0)*(1000000*1.0)) AS DECIMAL(65,0))) + (CAST(CONV(SUBSTR(MD5(inventory.inventory_id),1,16),16,10) AS DECIMAL(65)) *18446744073709551616 + CAST(CONV(SUBSTR(MD5(inventory.inventory_id), 17, 16), 16, 10) AS DECIMAL(65))) ) - SUM(DISTINCT (CAST(CONV(SUBSTR(MD5(inventory.inventory_id),1,16),16,10) AS DECIMAL(65)) *18446744073709551616 + CAST(CONV(SUBSTR(MD5(inventory.inventory_id), 17, 16), 16, 10) AS DECIMAL(65)))) )/(1000000*1.0) ,0) AS `inventory.total_replacement_cost`


FROM tmp.LR$4D6KBTNZ1RBIQEG54ZG1C_inventory AS inventory


LEFT JOIN film ON inventory.film_id = film.film_id


LEFT JOIN film_category ON film.film_id = film_category.film_id


LEFT JOIN category ON film_category.category_id = category.category_id





GROUP BY 1


ORDER BY category.name DESC


LIMIT 500


You can play with this example here: https://learn.looker.com/x/H6Gtv73

lloydtabb
Rank
Userlevel 6
Badge

It occurs to me that you might be asking something else. Looker’s templated filters allow you to insert conditions into SQL queries used in derived tables.





O

Template is basically what I am looking for.



One thing I was trying to do but it does not work is the following



SELECT s.sale_id, s.date,  {% condition %} i.group_name, {% endcondition %} s.amount FROM sale_fact s {% endcondition %} LEFT JOIN item_groups i ON i.item_id = s.item_id {% endcondition %}


Essentially resorting to the join only when it was necessary.

Reply


Terms of Service