Migrating Looker Measures to Holistics

Overview

Looker measures and Holistics measures serve similar purposes but have some key differences in syntax and capabilities:

1. Definition Approaches

   • Looker uses only SQL definitions
   • Holistics supports both SQL and AQL definitions

2. Measure Types

   • Looker has three measure type categories:
     • Aggregate: average, count, count_distinct, list, percentile, percentile_distinct, sum
     • Non-aggregate: string, number, date, yesno
     • Post-SQL: percent_of_total, running_total, percent_of_previous
   • Holistics uses a unified measure type with flexible definitions (text, number, date, datetime, truefalse, json)

Step-by-Step Migration

Step 1: Set Up Your Environment

1. Open your Looker view file containing the measures
2. Create a new Holistics model file
3. Have the type mapping reference ready

Step 2: Migrate Measures

Basic Aggregations

// Looker
view: view_name {
  dimension: sale_amount {}

  measure: total_sales {
    type: sum
    sql: ${sale_amount} ;;
  }

}

// Holistics - SQL Definition
Model model_name {  
  dimension sale_amount {}

  measure total_sales {
    type: 'number'
    definition: @sql sum({{ sale_amount }});;
  }

}

// Holistics - AQL Definition
Model model_name {
  dimension sale_amount {}

  measure total_sales {
    type: 'number'
    definition: @aql sum(model_name.sale_amount);;
  }
}

Distinct Counts

// Looker
view: view_name {
  dimension: customer_id {}

  measure: unique_customers {
    type: count_distinct
    sql: ${customer_id} ;;
  }
}

// Holistics
Model model_name {
  dimension customer_id {}

  measure unique_customers {
    type: 'number'
    definition: @aql count_distinct(model_name.customer_id);;
  }
}

Conditional Aggregations

// Looker
view: view_name {
  dimension: state {}

  measure: customers_by_state {
    type: count
    filters: [state: "California, Nevada, Washington, Oregon"]
  }
}

// Holistics
Model model_name {
  dimension state {}

  measure customers_by_state {
    type: 'number'
    definition: @aql 
      count(model_name.customer_id) 
      | where(model_name.state in ["California", "Nevada", "Washington", "Oregon"]);;
  }
}

Running Totals

// Looker (post-query calculation)
view: view_name {
  measure: sale_amount {}

  measure: running_total {
    type: running_total
    sql: ${sale_amount} ;;
  }
}

// Holistics (in-query calculation)
Model model_name {
  measure sale_amount {}

  measure running_total {
    type: 'number'
    definition: @aql running_total(model_name.sale_amount) ;;
  }
}

Percent of Previous

// Looker (post-query calculation)
view: view_name {
  measure: mrr {}

  measure: count_growth {
    type: percent_of_previous
    sql: ${mrr} ;; 
  }
}

// Holistics (in-query calculation)
Model model_name {
  dimension date {}
  measure mrr {}

  measure count_growth {
    type: 'number'
    definition: @aql 
      ( model_name.mrr - previous(model_name.mrr, order: model_name.date) ) 
      / 
      previous(model_name.mrr, order: model_name.date)
      ;;
  }
}

Percent of Total

// Looker (post-query calculation)
view: view_name {
  measure: total_gross_margin {}

  measure: percent_of_total_gross_margin {
    type: percent_of_total
    sql: ${total_gross_margin} ;;
  }
}

// Holistics (in-query calculation)
Model model_name {
  measure total_gross_margin {}

  measure percent_of_total_gross_margin {
    type: 'number'
    definition: @aql 
      (model_name.total_gross_margin) * 1.0 
      / 
      (model_name.total_gross_margin | of_all(model_name)) 
      ;;
  }
}

Step 3: Test and Validate

1. Check all measure types are correctly mapped
2. Verify aggregations produce expected results
3. Test measures in simple reports
4. Compare results with original Looker measures

Reference Manual

Type Mapping

Looker Type	Holistics Definition	Notes
sum	sum()	Direct mapping
average	average()	Direct mapping
count	count()	Direct mapping
count_distinct	count(distinct: true)	Use AQL syntax
list	unique()	Returns array of values
percent_of_total	AQL expression	Use percent_of_total
running_total	AQL expression	Use running_total

Detailed Comparison

LookML Parameter	Purpose	Support	Holistics Equivalent & Implementation
Structural Parameters
measure	Creates a measure field	✅	Holistics measure and metric
Action and Linking Parameters
action	Creates clickable actions on measure values	✔️ (partial)	Holistics Actions Note: Holistics Actions can only be defined in Dashboard layer
drill_fields	Specifies fields to show when drilling into measure	🛠️ (will support soon)	Holistics Drill Down
tags	Adds text that can be passed to other applications	❌	No direct equivalent
link	Creates links to other Looker content or external URLs	✅	Can be solved with Holistics Actions
Display Parameters
alias	Creates alternate names for backward compatibility	❌	No direct equivalent
description	Adds explanatory text for the measure	✅	Uses description parameter
group_label	Groups measures together under a heading	❌	No direct equivalent
group_item_label	Specifies label for field under its group label	❌	No direct equivalent
hidden	Controls visibility in the UI	✅	Uses hidden parameter with true/false
label	Changes how the measure appears in the UI	✅	Uses label parameter
order_by_field	Sorts measure by values of another field	❌	No direct equivalent
view_label	Changes how measure's view appears in field picker	❌	No direct equivalent
Filter Parameters
can_filter	Lets you prohibit a measure from being used as a filter	❌	No direct equivalent
filters (for measure)	Restricts a measure's calculation based on dimension limitations. Only with the following measure types that perform aggregation: - count - sum - average - count_distinct	✅	Using AQL where function. Unlike Looker, this function can work with all type of aggregate functions
Filter Suggestion Parameters
suggest_dimension	Bases suggestions on values of different dimension	❌	No direct equivalent
suggest_explore	Bases suggestions on values of different explore	❌	No direct equivalent
suggestable	Enables or disables suggestions for a field	❌	No direct equivalent
Query Parameters
convert_tz	Controls timezone conversion	❌	No direct equivalent
datatype	Specifies the type of time data you are providing to a field
required_access_grants	Controls access based on user attributes	❌	No direct equivalent in basic AMQL, but Holistics handles access control differently
required_fields	Requires additional fields when this field is chosen	❌	No direct equivalent
sql_distinct_key	Defines the unique entities over which a measure of type: sum_distinct or type: average_distinct will be calculated	✅	Check how Holistics handles Fanout issue
Value and Formatting Parameters
direction	Determines the direction that a measure of type: percent_of_total or type: running_total is calculated when pivots are used	✅	Holistics supports native Percent of Total calculation
html	Modifies HTML output using Liquid templating	✅	Holistics supports HTML Format
list_field	Declares the dimension from which a measure of type: list will be calculated	✅	Using AQL unique function
percentile	Specifies the fractional value (the Nth percentile) for a measure of type: percentile or type: percentile_distinct	❌	Not supported yet.
type (for measure)	Specifies the type of measure Measure type categories: - Aggregate measures (average, count, sum, etc.) - Non-aggregate measures (date, number, string, etc.) - Post-SQL measures (Percent of Total, Running Total, etc.)	✅	In Holistics, type for: - Aggregate Meaasure is supported via SQL definition or AQL definition - Non-aggregate measures are supported via type in measure/metric - Post-SQL Measures are supported via AQL Expression (e.g., Percent of Total), but we will perform the calculation in the query, instead of after generating the query like Looker
value_format	Formats the output of a field using Excel style options	✅	Holistics supports Data Format
value_format_name	Formats the output of a field using a built-in or custom format	❌	Not supported custom format yet.