Allow Viewers to select GCP Project in Dashboard

[uwe]

Hi everyone,

we have a multi-stage ETL pipeline with the resulting tables living in different GCP projects in BigQuery as in the following example.

Stages: unstable, staging, sandbox, prod

GCP/BQ Setup:

• unstable-project.etl.my_event
• staging-project.etl.my_event
• sandbox-project.etl.my_event
• prod-project.etl.my_event

Table Schema (for all four tables):

• timestamp ts
• string user_id
• string status

Access to these projects, datasets, and tables is fully managed in GCP and in Looker we grant access to our users and viewers via OAuth. Some accounts will have access to staging only, while others will have access to all stages.

My goal is to develop ONE “My Event Dashboard” for all four stages.

I want to avoid duplicating Looker Projects, Models, and Dashboards, if possible.

As a user I would expect to have a filter control in a dashboard that allows selecting the GCP Project (or stage), such that I can see the data from that particular stage only. If I do not have access to some stages these should not be visible in the filter values (if possible). Also not having access to some stages should not break the Dashboard for me.

The access rights granted in GCP (i.e., obtained via OAuth) should define which values to filter on. If possible, the filter control (or underlying Looker model) should enforce that the user selects exactly ONE project/stage in the dashboard and this should then translate to which tables are queried (or views included in the LookML).

I tried using user attributes and liquid variables, manually adding the four project IDs as Advanced String values in a “project” attribute, with no success. Also this would be just a hack. Having a native OAuth way of accessing data would be much preferred.

What is the best approach here?

Thanks,
Uwe

[uwe]

I found a solution using view parameters.

First, I define a parameter stage and then use this to conditionally prefill the value of all views’ sql_table_name.

The code could be out-sourced for reuse to a separate “abstract” view (i.e., with extension: required) so that I can reuse this in several views.

Here is the view-independent extension base:

# file: /extensions/stages.view.lkml

# provides stage parameter that is used to setup the sql_table_name
view: with_stage_parameter {
  extension: required

  parameter: stage {
    type: unquoted
    allowed_value: { value: "unstable"  }
    allowed_value: { value: "staging" }
    allowed_value: { value: "sandbox" }
    allowed_value: { value: "live" }
    default_value: "stage_not_set"
  }

  dimension: stage_name {
    label: "Stage Name"
    sql: "{% parameter stage %}";;
  }

  dimension: table_name {
    label: "Table Name"
    sql: "${TABLE}";;
  }
}

# maps the selected stage parameter to a (partial) `sql_table_name`.
# usage:
#   view: name {
#     extends: [with_sql_table_stage_project]
#     sql_table_name: `${EXTENDED}.dataset.table`
#   }
#
view: with_sql_table_stage_project {
  extension: required
  extends: [with_stage_parameter]
  # be careful to avoid spaces and newlines in project ids
  sql_table_name:
  {%   if stage._parameter_value == 'unstable' %}my-unstable-ab12{%
    elsif stage._parameter_value == 'staging'  %}my-staging-bc23{%
    elsif stage._parameter_value == 'sandbox'  %}my-sandbox-cd45{%
    elsif stage._parameter_value == 'live'     %}my-live-de56{%
    else                                       %}my-staging-cd45{%
    endif %};;
}

And here is an example view that uses the extension:

# file: /views/my_event.view.lkml

include: "/extensions/**/*.lkml"

view: my_event {
  extends: [with_sql_table_stage_project]

  sql_table_name: `${EXTENDED}.events.my_event`;;

  # ...
}

In my dashboards I can now filter by this parameter and the four allowed values are shown correctly in the UI.

I hope this helps others looking for a solution.

Also, if there is a better solution (using Looker Admin settings, etc.) I am still very interested in that. I cannot be the only data guy with that problem.

How did you solve it?

Best,
Uwe

[uwe]

Update: We are still using this solution.

If you use it with SA-based access you need a single service account to access all stages.

We gave our prod-SA access to all stages and treat all models exposed this way as production models. Works quite well so far and allows working on our dashboards without much need for copying and deploying lookml code, which would slow us down significantly we believe.

For some models (where we access sensitive data) we use OAuth-based access, where all data-access is managed in BQ. It also works there quite well so far. If a user cannot access a stage, the dashboard will be empty then.

I will keep you posted if we change the setup or run into any issues.

Also, if you have an alternative solution, we would be happy if you shared your own multi-stage setup. Thx! ?

Cheers,

Uwe