Building a Flexible and Scalable Looker Dashboard Using LookML

Looker is a Data Analytics and Visualization company recently acquired by Google. Looker Visualization UI has a unique semantic modeling layer called LookML. This semantic layer separates the visualization layer from the database. This layer also helps in dealing with changes to the dashboard with minimal effort. 

LookML Development Best Practices

In order to build a flexible and scalable semantic layer, developers need to provide a higher level of abstraction. In this post we share the value of a higher level of abstraction.

Use Case and Implementation Model

In this example we have different types of job roles and pay codes. When developing a query to load a metric on the dashboard, filter the dataset by job codes (roles) and pay codes as shown in figure 1 below.

Building a flexible and scalable Looker Dashboard

measure: total_worked_hours {
    label: "Total Worked Hours"
    type: sum
    sql: NULLIF(${employee_hours},0) ;;
    precision: 3
    value_format: "0.00"
value_format: "0.##"
     filters: {
       field: activity_code
       value: "Transaction"
     }
     filters: {
       field: paycode_name
       value: "Regular,Straight,Overtime,Holiday Worked,Double time"
     }
     filters: {
       field: job_role_id
       value: "3607,6211"
     }
    drill_fields: [employee_id,total_worked_hours]
     filters: {
       field: activity_code
       value: "Transaction"
     }

There is a problem with this approach. If you have to define another measure with the same filters paycode_name and job_code_id, the code is duplicated as shown below in Table 2.

measure:  total_rc_hours_clocked{
    label: "Total Hours RC Clocked"
    type: sum
    sql: NULLIF(${employee_hours},0) ;;
#     value_format: "0.##"
    precision: 3
    value_format: "0.00"
     filters: {
       field: paycode_name
       value: "Regular,Straight,Overtime,Holiday Worked,Double time"
     }
filters: {
       field: job_role_id
       value: "3607,6211"
     }

    drill_fields: [paycode_name,total_rc_hours_clocked]
     filters: {
       field: activity_code
       value: "Transaction"
     }

You can see how this approach is not scalable and is difficult to maintain. If a new job code is added to the visualization layer, a developer has to add the ID manually to all the filters written over time.  

The solution to this problem is shown in Figure 2 and 3. A better approach creates dimensions for the job roles and pay code names and uses dimensions as filters in the measures for total_worked_hours and total_hours_rc_clocked. Using this approach, you don’t have to define the job codes and paycode names over and over again. In the future, when a job code or a paycode name needs to be modified in the dashboard, all you have to do is make the change to the two dimensions below and changes are applied to all measures that use the filters.

Looker Skip
Figure 2
dimension: is_cwk_rc_spec_jobrole {
    type: yesno
    sql: ${job_role_id} IN ('3607', '6211') ;;
  }

  dimension: is_standard_paycode {
    type: yesno
    sql: ${paycode_name} IN ('Regular','Straight','Overtime','Holiday Worked','Double time') ;;
  }

  dimension: is_overtime_paycode {
    type: yesno
    sql: ${paycode_name} IN ('Overtime','Holiday Worked','Double time') ;;
  }

  dimension: is_transaction_activity {
    type: yesno
    sql: lower(${activity_code}) = 'transaction' ;;
  }

  dimension: is_break_activity {
    type: yesno
    sql: lower(${activity_code}) = 'break' ;;
  }

  measure: total_worked_hours {
    label: "Total Worked Hours"
    type: sum
    sql: NULLIF(${employee_hours},0) ;;
    precision: 3
    value_format: "0.00"
    filters: {
      field: is_cwk_rc_spec_jobrole
      value: "Yes"
    }

measure:  total_rc_hours_clocked{
    label: "Total Hours RC Clocked"
    type: sum
    sql: NULLIF(${employee_hours},0) ;;
    value_format: "0.00"
    filters: {
      field: is_cwk_rc_spec_jobrole
      value: "Yes"
    }
    filters: {
      field: is_standard_paycode
      value: "Yes"
    }
    drill_fields: [paycode_name,total_rc_hours_clocked]
    filters: {
      field: is_break_activity
      value: "No"
    }
  }
Looker Skip Figure 3
Figure 3

Conclusion

While implementing already flexible and scalable LookML semantic layers, it is important to abstract the customer data as much as possible. This allows for changes to be made quickly and efficiently.  

If you have any questions about building a flexible and Scalable Looker Dashboard Using LookML, drop us a line at info@SpringML.com or tweet us @springmlinc.

About SpringML

SpringML delivers data-driven digital transformation outcomes with an experimentation and design thinking mindset. We provide Google Cloud consulting and implementation services and industry-specific analytics solutions that deliver high-impact business value from data. SpringML is a premier Google Cloud partner with capabilities to plan, assess, deploy, and manage data-driven engagements. We have been awarded Google Cloud specialization based on our expertise and customer portfolio for Data Analytics, Machine Learning, and Marketing Analytics.