Dynamic LookML based on User and/or Group

Looker Q&A Looker
1

I am facing a scenario where different users may have different LookML requirements. Given this example:

Base View:

view: sales_data {

  dimension: id {
    type: string
    sql: ${TABLE}."ID"
  }

  dimension: sales_date {
    type: date
    sql: ${TABLE}."SALES_DATE"
  }
}

Anyone in Group “Sales” might requires the View to be modified like so:

view: sales_data {

  dimension: id {
    type: string
    sql: ${TABLE}."ID"
  }

  dimension: sales_date {
    type: date
    sql: ${TABLE}."SALES_DATE"
  }

  dimension: gross_sales {
    type: number
    sql: ${TABLE}."GROSS_SALES"
  }

  dimension: net_income {
    type: number
    sql: ${TABLE}."NET_INCOME"
  }
}

And anyone in Group “Marketing” might need the View to be modified like so:

view: sales_data {

  dimension: id {
    type: string
    sql: ${TABLE}."ID"
  }

  dimension: sales_date {
    type: date
    sql: ${TABLE}."SALES_DATE"
  }

  dimension: gross_margin{
    type: number
    sql: ${TABLE}."GROSS_MARGIN"
  }
}

Different users may also require different Joins in the Models as well. i.e. - how can I join a specific View for Users in Group ‘A’, but not for Users in Group ‘B’?

Are there possible solutions for this? Or any documentation you could point me toward?

The longer version of this:

We have embedded Looker into a web application. Of course, our application comes out-of-the-box with standard fields. But it also provides the ability for users to add ‘custom’ fields to a screen in our application. When a user creates a custom field, the field’s definition is stored in one DB table, and its values are stored in a separate DB table. Similar to the following tables:

dbo.CustomFieldDefinitions
dbo.Sales
dbo.Sales_CustomFieldValues

dbo.Sales contains our application’s standard set of fields. dbo.Sales_CustomFieldValues contains the values of any additional fields that are defined in dbo.CustomFieldDefinitions.

2

Hello @PaulM1

So here it’s the perfect use case for access_grant and required_access_grant!

Those 2 parameters will allow you to display fields, joins and/or explores based on a user attribute.

Say you have a user attribute that is called department that as various values like sales, marketing or finance depending on the user actual department. That value could be set at the user level or at a group level (so if you create a Marketing group, you can set the department user attribute value to marketing for everyone in that group)

Now let’s look at the lookML.

  • Start by creating your various access_grant at the model level:
### model file

access_grant: marketing {
  user_attribute: department
  allowed_values: [ "marketing", "executive" ]
}

access_grant: sales {
  user_attribute: department
  allowed_values: [ "sales", "executive" ]
}

}

Now we apply it them your view:

view: sales_data {

  dimension: id {
    type: string
    sql: ${TABLE}."ID"
  }

  dimension: sales_date {
    type: date
    sql: ${TABLE}."SALES_DATE"
  }

  dimension: gross_sales {
   required_access_grants: [sales]
    type: number
    sql: ${TABLE}."GROSS_SALES"
  }

  dimension: net_income {
      required_access_grants: [sales]
    type: number
    sql: ${TABLE}."NET_INCOME"
  }

  dimension: gross_margin {
      required_access_grants: [marketing]
    type: number
    sql: ${TABLE}."GROSS_MARGIN"
  }
}

You can apply the same logic to Explores and Joins!

3

Cyril_MTL_Analytics:

required_access_grant

interesting that you’re using “required_access_grant” not a “required_access_grants”. What’s the difference in terms of functionality

4

Hey Alexey,
It’s just a typo, it should indeed be required_access_grants.

Good catch, I’ll update my previous message