Dynamic schemas with the same data format using parameter

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

The Looker parameter field lets you inject a value directly into the SQL.

Various Looker pages describe how to use this feature:

  • The Templated filters and parameters documentation page, which discusses creating the field and applying the user input
  • The parameter reference page for this field type and its allowed_value subparameter
  • The Field parameters page, which includes the parameter field type and shows which LookML parameters can be subparameters of the parameter field
  • The Liquid variable reference documentation page, which mentions the {% parameter %} Liquid variable
  • The Dimension, filter, and parameter types documentation page, which indicates the LookML type parameter values that can be used with parameter fields

In this article, we discuss how to use parameter to dynamically select from multiple schemas with the same data format

The problem


Some schemas that are identical in structure. This is often encountered when working with GA Premium data, where one can see tables such as myproject:00001.ga_sessions, myproject:00002.ga_sessions, or myproject:00003.ga_sessions. The prefixes 00001, 00002, 00003 could represent data for different countries or for Web versus iOS, and so on.

The solution


Using a parameter, you can design a dashboard with a filter that lets your users select the country — with a user-friendly name!

view: t2 {
sql_table_name: {% parameter tablename %};;

parameter: tablename {
type: unquoted
allowed_value: {
label: "UK"
value: "00001.ga_sessions"
}
allowed_value: {
label: "Germany"
value: "00002.ga_sessions"
}
}
}

If your company has multiple clients, you might store data from each of your clients in its own schema: clienta.users, clientb.users, and so on. Using a similar approach to the country example above, you can let an account manager easily choose which client's data to use.

Version history
Last update:
‎07-18-2022 11:09 AM
Updated by: