Multi-tenant embedded analytics: A step-by-step guide to managing dynamic fields in Looker

[Francesco_V]

Looker’s embedded analytics capabilities help companies drive growth and create new revenue streams by creating data-centric apps for their users and customers.

But in scenarios where each client needs to track unique attributes, like "Customer Acquisition Channel" or "Contract Renewal Date" one challenge that often arises is effectively managing dynamic fields – custom attributes defined by each client that enrich their data analysis.

Fear not! This blog post will guide you through a practical, step-by-step approach to implementing those dynamic fields, ensuring a smooth, scalable, and secure experience for every user. By leveraging Looker's powerful features like groups, user attributes, and access grants, we can create a single, dynamic model that adapts to each client's specific needs – eliminating the need for cumbersome, client-specific models.

The multi-tenant challenge

Let's face it, key-value pairs alone don't cut it for multi-tenant embedded analytics. You need a structured way to manage:

• Attribute visibility: Show users only the custom attributes relevant to their business, not a jumbled list of everything.
• Attribute naming: Ensure the names of these data fields match the definitions established within the application.
• Rock-solid data security: Guarantee that clients can only see and analyze their own data, maintaining privacy and trust.

Working example: bringing the challenge to life

Let's illustrate this with a simplified example. Consider an application with two customers who have each configured custom attributes for their "User" entity, which are represented with the following column definitions:

• entity_id: the entity the attribute belongs to; our application defines multiple entities (users, orders, etc.) and customers that define new attributes linked to any of them. 
• customer_id: the application customer who configured the attribute.
• attribute_type: the type of the attribute.
• attribute_id: the identifier of the attribute within a given attribute_type and customer_id. A key assumption is that attribute_id's are finite and reused across multiple customers.
• attribute_name: the name assigned by the customer to the attribute.

 As you can see, each client has defined a different number of attributes with varying names and data types. Our goal is to design a Looker model that seamlessly accommodates these variations while ensuring each client sees only relevant data presented with consistent labels.

Lets now assume that each customer assigned values to their custom attributes as shown in the following table:entity_row_id represents the identifier of each entity (e.g. the user_id, order_id, etc.). In our example, user 101 is Italian, has passport 1234567890, became a customer in 2022 and is going to renew in 2026.

The solution: the power of groups, user attributes, and access grants

Our approach hinges on effective data model design and utilizes Looker's user management and access control features. Let's break down the steps:

1. Create groups

First, create a separate Looker group for each application customer. This enables us to assign custom values to control both column configurations (labels, visibility) and row level security. Groups can be created and managed through APIs.

2. Define user attributes

User attributes provide a customized experience for each Looker user. Attributes will be used to control two LookML constructs:

• Access grants (visibility)
• Dimension labels

In our example we define two sets of user attributes for each entity:

Access User Attributes control if a user or group of users can see a specific dimension.

Custom user attributesWe can then define group visibility as follows to reflect how each customer defined their first field of type string. An access user attribute is required for every entity_id, attribute_type and attribute_id defined by our customers.

Group valuesLabel User Attributes control the label seen by a user or group of users. For example: 

Defining label user attributesWe can then define group labels as follows to reflect how each customer defined their first field of type string:

Defining group labelsNotice that a label user attribute is required for every entity_id, attribute_type and attribute_id defined by our customers.

We also want to define a user attribute named customer_id. Defining a customer_id user attributeThis user attribute will be used to model row level security for each application customer.Adding group valueUser attributes and group values can be defined using Looker APIs, so when a customer updates their definitions in the application, the changes can be propagated to Looker by updating user attribute values.

3. Define access grants: enforce data security and segregation

An access grant is a LookML structure defined in a model file that controls access to other LookML structures. This ensures that users within a specific client group can only see the dimensions allowed for their profile.

For instance, we can specify that users having the value “Yes” in their user attribute access_user_date1 will satisfy the requirement and will be able to access any LookML object (dimension, explore, etc.) associated to the access grant. 

access_grant: access_user_date1 {
	user_attribute: acces_user_date1
	allowed_values: ["Yes"]
}

We need to define an access grant for every entity_id, attribute_type and attribute_id defined in the application.

4. Define a Looker View

For each entity, define a Looker view that uses a SQL based Persistent Derived Table (PDT) to pivot your dynamic field data. This transforms the data into a columnar structure where each dynamic field is represented by a dedicated column, offering greater flexibility for analysis and reporting.

In our example we first filter the User entity and then we pivot by attribute (identified byattribute_id and id). Notice that in this transformation we do not consider the attribute name defined by each customer. 

view: user_attributes_pivoted {

  derived_table: {
    sql: SELECT
      entity_row_id as user_id,
      customer_id,
      `STRING-1`,
      `STRING-2`,
      cast(`DATE-1` as DATE ) as `DATE-1`,
      cast(`DATE-2` as DATE ) as `DATE-2`,
       FROM (
        select entity_row_id, customer_id, concat(attribute_type,'-',attribute_id) as attribute , atribute_value
        from `lookerprivateipfvi.flexible_attributes.attribute_value`
        WHERE entity_id = 'User'
        )
      PIVOT (MAX(atribute_value) FOR attribute IN ('STRING-1','STRING-2','DATE-1', 'DATE-2')) ;;
      cluster_keys: ["entity_row_id"]

      datagroup_trigger: dynamicattributes_default_datagroup
  }

The resulting pivoted data looks like this:

Pivoted user attributes

5. Define dimensions with dynamic labels and access

Once we have defined the base view, we’ll create two types of dimensions:

1. Customer independent dimensions: These are fixed dimensions like user_id and , customer_id shared across all clients, used for joining data and implementing row-level security. 

     # user_id is the primary key to join with User table
     dimension: user_id {
       type: number
       primary_key: yes
       hidden: yes
       sql: ${TABLE}.user_id ;;
     }
   
    # customer_id allows us to define row level security on attributes
     dimension: customer_id {
       type: number
       hidden: yes
       sql: ${TABLE}.customer_id ;;
     }​

2. Customer dependent dimensions: These dimensions represent the dynamic fields defined by each client. Use LookML's label and required required_access_grants parameters to dynamically control the dimension's label and visibility based on the user attributes and access grants defined earlier.

In our example we use a string dimension: 

# field corresponding to Attribute_type=STRING, Attribute_Id=2
  dimension: string2 {
   label: "{{ _user_attributes['label_user_string2'] }}"
   required_access_grants: [access_user_string2]
    type: string
    sql: ${TABLE}.`STRING-2` ;;
  }

This dimension will be labeled as per user attribute label_user_string2 and will be visible only to users meeting the access_user_string2 access grant requirements.

If instead we used a date field, the dimension label will be read from user attribute label_user_date1 and will be visible only to users meeting the access_user_date1 access grant requirements. Notice that we can still use advanced LookML features (e.g. timeframes, etc.)! 

# field corresponding to Attribute_type=DATE, Attribute_Id=1
  dimension_group: date1 {
    label: "{{ _user_attributes['label_user_date1'] }}"
    required_access_grants: [access_user_date1]
    type: time
    timeframes: [raw, date, week, month, quarter, year]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.`DATE-1` ;;
  }

6. Define a test explore: validating your setup

Next let’s create a Looker Explore to test our dynamic model. By switching between different client group profiles, you can verify that users see only the relevant dimensions with the appropriate labels and can apply filters based on their custom fields.

In our example we define an explore to ensure that we have achieved our objectives: 

explore: user_attributes_pivoted {
  # row level security  
  access_filter: {
    field: customer_id
    user_attribute: customer_id
  }
}

If we try to explore our model with a “customer 1” profile we see the following dimensions and labels:

Fields as viewed by Customer1With a “customer 2” profile we see:

Fields as viewed by customer2Notice that this time we see only two dimensions and they have been renamed as described in the “customer 2” group. Therefore we were able to meet our first set of requirements, since we verified that we are able to show different dimension names and hide unused dimensions depending on the customer profile.

We also want to be able to create complex filters using custom fields: for instance a user with “customer 1” profile, so we can define the following selection criteria:Filters viewed by customer1

7. Define a unified explore

We can now construct a Looker explore that joins our base view (e.g., "Users") with the pivoted dynamic field view. We apply row-level security based on the customer_id user attribute to ensure data segregation. 

explore: users {
 # row level security
  access_filter: {
    field: user_attributes_pivoted.customer_id
    user_attribute: customer_id
  }
 # join custom fields view 
  join: user_attributes_pivoted {
    sql_on: ${users.id}=${user_attributes_pivoted.user_id} ;;
    type: inner
    relationship: one_to_one
  }
}

8. Explore and conquer!

With this setup, users from different clients will see only the dynamic fields relevant to them, with consistent and meaningful labels, all while maintaining strict data security. As a user with “customer 1” profile, we see these dimensions:

Dimensions viewed by customer1and we can then combine them to define complex filters.

Defining complex filters as customer1

Conclusion

By embracing the power of Looker's groups, user attributes, and access grants, you can create a flexible and scalable solution that adapts to each client's unique needs. This approach streamlines development, enhances user experience, and ensures your application remains secure and efficient.

Ready to level up your Looker game with dynamic fields?

Dive into Looker's documentation, explore the API, and join the community forum to unlock the full power of LookML for multi-tenant applications!

Resources:

• Looker API Documentation: - Essential for creating and managing user attributes, groups, and access grants through code.
• LookML Language Guide: - Covers the LookML constructs used for defining dimensions, access grants, and labels.
• Looker Persistent Derived Tables (PDTs): - Provides detailed information about creating and using PDTs for data transformation.

[sunita2004pnwar]

this is great

[Pavithra_I]

Great

[marout]

This is awesome! Thank you! 

[AaryanCodes]

Great Article, made it easy to follow along with images!