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

  • 14 November 2015
  • 3 replies

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


| sale_id | date | item_id | amount |


| 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 BETWEEN ? AND ? AND i.group_name IN (?)

and being able to inject the value for the placeholders ?

3 replies

Userlevel 6

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


- join: item_groups

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

relationship: one_to_many

- view: sales_facts


- 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


- 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:


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




You can play with this example here:

Userlevel 6

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,,  {% 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.