home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
allow_approximate_optimization

Usage

view: view_name {
  measure: field_name {
    allow_approximate_optimization: yes
  }
}

Hierarchy

allow_approximate_optimization

Possible Field Types

Measure

Default Value

no

Accepts

A Boolean (yes or no)

Definition

For dialects that support HyperLogLog sketches, Looker can leverage the HyperLogLog algorithm to approximate distinct counts for aggregate tables.

The allow_approximate_optimization: yes statement enables Looker to store HyperLogLog sketches in aggregate tables, which means that Looker can use approximations for distinct counts for aggregate awareness.

See the Dialect Support for Distinct Counts with Aggregate Awareness section below for the list of dialects that support distinct counts for aggregate tables using HyperLogLog sketches.

In general, distinct counts can’t be supported with aggregate awareness because you can’t get accurate data if you try to aggregate distinct counts. For example, if you are counting the distinct users on a website, there may be a user who came to the website twice, three weeks apart. If you tried to apply a weekly aggregate table to get a monthly count of distinct users on your website, that user would be counted twice in your monthly distinct count query, and the data would be incorrect.

One workaround for this is to create an aggregate table that exactly matches an Explore query, as described on the Aggregate Awareness documentation page. When the Explore query and an aggregate table query are the same, distinct count measures do provide accurate data, so they can be used for aggregate awareness.

The other option is to use approximations for distinct counts. The HyperLogLog algorithm is known to have about a 2% potential error. The allow_approximate_optimization parameter requires your Looker developers to acknowledge that it’s okay to use approximate data for the measure so that the measure may be calculated approximately from aggregate tables.

With aggregate awareness, there are two cases where distinct counts come into play:

In both of these cases, if your dialect supports HyperLogLog sketches, you can add the allow_approximate_optimization: yes statement to measures to enable approximate values. You can then include these measures in aggregate tables.

Even for measures defined with allow_approximate_optimization: yes, Looker will return exact data when possible. For example, if the dimensions in an Explore query are a perfect match of the dimensions in an aggregate table, Looker can provide exact data for distinct counts, without having to approximate. In this case, you will see in the Explore’s SQL tab that distinct count measures are being used for aggregate awareness without employing the HyperLogLog algorithm.

Example

The apx_unique_count measure shown below is set for allow_approximate_optimization: yes, which means that the measure can be used in an aggregate_table.

measure: apx_unique_count { type: count_distinct allow_approximate_optimization: yes # default value is no sql: ${id} ;; }

Dialect Support for Distinct Counts with Aggregate Awareness

Looker can use distinct counts for aggregate awareness with database dialects that support HyperLogLog sketches. In the current Looker release, the following SQL dialects are supported for distinct counts with aggregate awareness:

Please check your SQL dialect’s documentation to understand the speed and accuracy tradeoffs of this method.

Top