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

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

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

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.





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